Run Access Query From Excel With Parameter?

  • Thread starter Thread starter EricG
  • Start date Start date
E

EricG

I'm using code like the following to run a stored procedure from Excel. Is
there a similar method to run a stored procedure that requires a parameter?
I can always hard code the SQL, but I would rather just be able to call the
procedure and provide the parameter it needs.

Thanks,

Eric

Sub RunAccessQueries_ADO()

Dim cn As ADODB.Connection
Dim cm As ADODB.Command

dbPath = "d:\data\mypath\"
dbName = "mydb.mdb"

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.Open
End With

With cm
.CommandText = "qryMakeTable" ' Something like "qryMakeTable " &
myParam?
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
End With
'
cn.Close
'
End Sub
 
Assuming the database has a saved parameter query, query13 as example:


SELECT MAX(Ds.d)
FROM Ds
WHERE (((Ds.d)<=[limite]));



You can then EXEC it like this:


? CurrentProject.Connection.Execute("exec query13 7").Fields(0).Value


or use an open connection to the database. Note that (here the single)
argument is simply passed after the stored procedure... ooops, the saved
parameter query, name. Also, Execute produce a read only forward only
recordset, so here, I chose to read the value from the first field of the
first record it produced.



Vanderghast, Access MVP
 
It's that simple? Wow! I added the parameter value in-line with the
procedure name, and it worked perfectly. Thanks for the help.

Eric

vanderghast said:
Assuming the database has a saved parameter query, query13 as example:


SELECT MAX(Ds.d)
FROM Ds
WHERE (((Ds.d)<=[limite]));



You can then EXEC it like this:


? CurrentProject.Connection.Execute("exec query13 7").Fields(0).Value


or use an open connection to the database. Note that (here the single)
argument is simply passed after the stored procedure... ooops, the saved
parameter query, name. Also, Execute produce a read only forward only
recordset, so here, I chose to read the value from the first field of the
first record it produced.



Vanderghast, Access MVP


EricG said:
I'm using code like the following to run a stored procedure from Excel.
Is
there a similar method to run a stored procedure that requires a
parameter?
I can always hard code the SQL, but I would rather just be able to call
the
procedure and provide the parameter it needs.

Thanks,

Eric

Sub RunAccessQueries_ADO()

Dim cn As ADODB.Connection
Dim cm As ADODB.Command

dbPath = "d:\data\mypath\"
dbName = "mydb.mdb"

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.Open
End With

With cm
.CommandText = "qryMakeTable" ' Something like "qryMakeTable " &
myParam?
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
End With
'
cn.Close
'
End Sub
Click to expand...
Click to expand...
 
Indeed, the word exec (or execute, at long) is not required either, so just:

? CurrentProject.Connection.Execute("query13 7").Fields(0).Value

works too, with ADO, and it is not limited to Jet as targeted db. Note that
if you have more than one parameter, you may have to be sure of the right
order of the parameters, using an explicit PARAMETERS clause in your Jet
query (something we have to do for crosstabs).


Building, explicitly, an ADO parameters collection is required when your
stored proc return a value in one of the parameters, but that is not
something Jet does with its stored procedures... or queries with parameters,
if you prefer.



Vanderghast, Access MVP



EricG said:
It's that simple? Wow! I added the parameter value in-line with the
procedure name, and it worked perfectly. Thanks for the help.

Eric

vanderghast said:
Assuming the database has a saved parameter query, query13 as example:


SELECT MAX(Ds.d)
FROM Ds
WHERE (((Ds.d)<=[limite]));



You can then EXEC it like this:


? CurrentProject.Connection.Execute("exec query13 7").Fields(0).Value


or use an open connection to the database. Note that (here the single)
argument is simply passed after the stored procedure... ooops, the saved
parameter query, name. Also, Execute produce a read only forward only
recordset, so here, I chose to read the value from the first field of the
first record it produced.



Vanderghast, Access MVP


EricG said:
I'm using code like the following to run a stored procedure from Excel.
Is
there a similar method to run a stored procedure that requires a
parameter?
I can always hard code the SQL, but I would rather just be able to call
the
procedure and provide the parameter it needs.

Thanks,

Eric

Sub RunAccessQueries_ADO()

Dim cn As ADODB.Connection
Dim cm As ADODB.Command

dbPath = "d:\data\mypath\"
dbName = "mydb.mdb"

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.Open
End With

With cm
.CommandText = "qryMakeTable" ' Something like "qryMakeTable " &
myParam?
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
End With
'
cn.Close
'
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top