Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic use of Access query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic use of Access query
Laurent M wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Access query vs. Excel query | Excel Programming |