Dynamic use of Access query

[Guest]

Hello,

i got this piece of code which returns a recordset from an Access Query.
But i need this function to pass dynamicaly the name as a parameter of the
function, and then get a recordset from that.

But this code needs to add parameters from the query :

Dim adoCmd As ADODB.Command
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset

' Database connection
'sAccessDB path of the Access DB
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
sAccessDB & "; USER ID=Admin; PASSWORD=;"

Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
'Name of the query
.CommandText = "QueryName"
.CommandType = adCmdStoredProc
'Parameters defined in the query
.Parameters.Append .CreateParameter("pCycle", adVarChar,
adParamInput, 16, sCompSel)
.Parameters.Append .CreateParameter("pWorkDate", adDate,
adParamInput, , UpdateDate)
'Execute the query
Set adoRS = adoCmd.Execute
End With

'Close connection
Set adoRS = Nothing
Set adoCmd = Nothing
Set adoConn = Nothing

Do you know how could i get rid of lines with .Parameters or how could i
dynamicaly get those from the name of the query?

I'm stuck

Please help, thanks

 

[Jamie Collins]

i got this piece of code which returns a recordset from an Access Query.
But i need this function to pass dynamicaly the name as a parameter of the
function, and then get a recordset from that.

But this code needs to add parameters from the query :

Dim adoCmd As ADODB.Command
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset

' Database connection
'sAccessDB path of the Access DB
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
sAccessDB & "; USER ID=Admin; PASSWORD=;"

Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
'Name of the query
.CommandText = "QueryName"
.CommandType = adCmdStoredProc
'Parameters defined in the query
.Parameters.Append .CreateParameter("pCycle", adVarChar,
adParamInput, 16, sCompSel)
.Parameters.Append .CreateParameter("pWorkDate", adDate,
adParamInput, , UpdateDate)
'Execute the query
Set adoRS = adoCmd.Execute
End With

'Close connection
Set adoRS = Nothing
Set adoCmd = Nothing
Set adoConn = Nothing

Do you know how could i get rid of lines with .Parameters or how could i
dynamicaly get those from the name of the query?

I'm not sure what you are asking.

Note you are calling a stored procedure. The MS Access community may
call it a parameterized Stored Query object (or whatever), but you are
using the Jet 4.0 OLEDB provider which, in common with everyone else,
calls it a PROCEDURE. A stored proc may be called using the SQL method
EXECUTE (this is what the ADO Command object is using under the hood
anyhow).

If you are saying your stored proc doesn't have any parameters, the
answer is to not use any in the call e.g.

Set adoRS = adoConn.Execute( _
"EXECUTE MyStoredProc;")

If you are saying your stored proc does have parameters but you don't
know their names, the answer is you don't need to know their names. You
just need to be able to pass valid values e.g. if MyStoredProc has two
parameters start_date and end_date of type DATETIME:

Set adoRS = adoConn.Execute( _
"EXECUTE MyStoredProc '2001-01-01', '2004-01-01';")

If you are saying you only have the stored proc's name and you want to
find details e.g. number of parameters, their data type etc, then you
can use ADOX to create an appropriate ADO Command object for you and
use this to call the proc e.g. add something like this to your code

Dim adoxCat as Object
Set adoxCat = CreateObject("ADOX.catalog")
Set adoxCat.ActiveConnection = adoConn
Set adoCmd = adoxCat.Procedures("MyStoredProc").Command

You can then get the details from the newly-created command e.g.

adoCmd.Parameters(0).Name
CBool(adoCmd.Parameters(0).Type = adDate)

FWIW ADODB's GetSchema method has an enum for
adSchemaProcedureParameters to return an OLEDB PROCEDURE_PARAMETERS
rowset i.e. parameter details for procedure. However, when I try this
with the Jet 4.0 provider I get an error, 'Object or provider is not
capable of performing requested operation' (same for SQL Server). I
assume this is because the provider must query system tables not
accessible with ADODB.

Jamie.

--