Run saved query with VBA

Let’s say there is a query (Just example) by name “QryPreviousApps_CC_fromdump” in database
While running this example query it would ask you for couple of parameters (Last authenticated date - [prmLastAuthDate] and Cost Number - [prmCstcntr]).

To run the query in VBA, use the following code.

Dim Cn As New ADODB.Connection, Rs As New ADODB.Recordset, Cmd As New ADODB.Command

Public Const dBase1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\vishal\Desktop\\DB1.mdb;Persist security info=false;Jet OLEDB:Database Password=pswd"

dim dtmLastAuthDate as date, IntCstCntr as integer

 dtmLastAuthDate = #1/1/2012#

IntCstCntr = 1234

Cn.Open dBase1

With Cmd

   .ActiveConnection = dBase1

   .CommandType = adCmdStoredProc

   .CommandText = "QryPreviousApps_CC_fromdump"

   .Parameters.Append .CreateParameter("prmLastAuthDate", adDate, adParamInput, , dtmLastAuthDate)

   .Parameters.Append .CreateParameter("prmCstCntr", adInteger, adParamInput, 9, IntCstCntr)

End With

Rs.Open Cmd, , adOpenKeyset, adLockOptimistic

'Now you can refer the field names using rs!fieldname