Full Version: Passing parameters to Access Query
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
bflemi3
I am trying to pass 2 parameters (adDate) to a parameterized query in MS Access. I have tried a range of different things but can't get the syntax down. could someone point me in the right direction?
Thanks in advance,
-B
dashiellx2000
What is the syntax you are using right now?
bflemi3
CODE
Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rec As New ADODB.Recordset
    Dim strStartDate As String
    Dim strEndDate As String
    Dim i As Integer
    Dim rng As Range
    
    strStartDate = InputBox("Enter Start Date:")
    strEndDate = InputBox("Enter End Date:")
    
    cnn.Open "Provider=" & acProvider & "Data Source=" & acDB_Path
    
    With cmd
        .ActiveConnection = cnn
        .CommandText = "qry_SubVendor_QOP_Starts"
        .CommandType = adCmdStoredProc
    End With
    cmd.Parameters.Append (cmd.CreateParameter("[Start Date:]", _
        adDate, adParamInput, , strStartDate))
    cmd.Parameters.Append (cmd.CreateParameter("[End Date:]", _
        adDate, adParamInput, , strEndDate))
    Set rec = cmd.Execute
    Set rng = Worksheets("Starts").Range("a2")
    CopyRecordset rec, rng
    rec.Close
KingMartin
Hello,
The parameters are already defined?
Maybe this would help? link
Martin
bflemi3
Hi Martin
Thanks for the response. The connection was made (it seemed to go slower with the ADOX catalog than an ADO connection), but no data was returned. I don't understand why this is so difficult with access but so easy with SQL Server, you'd think it would be the other way around. Anyway, here is what I've got so far:
CODE
    Dim cmd As New ADODB.Command
    Dim rec As New ADODB.Recordset
    Dim cat As New ADOX.Catalog
    Dim strStartDate As String
    Dim strEndDate As String
    Dim i As Integer
    Dim rng As Range
    
    strStartDate = InputBox("Enter Start Date:")
    strEndDate = InputBox("Enter End Date:")
    
    'cnn.Open "Provider=" & acProvider & "Data Source=" & acDB_Path
    cat.ActiveConnection = "Provider=" & acProvider & "Data Source=" & acDB_Path
    Set cmd = cat.Procedures("qry_SubVendor_QOP_Starts").Command
    With cmd
        '.ActiveConnection = cnn
        '.CommandText = "qry_SubVendor_QOP_Starts"
        '.CommandType = adCmdStoredProc
        .Parameters("[Start Date:]") = strStartDate
        .Parameters("[End Date:]") = strEndDate
        MsgBox .Parameters("[Start Date:]")
        '.Parameters.Append .CreateParameter("[Start Date:]", _
            adDate, adParamInput, , strStartDate)
        '.Parameters.Append .CreateParameter("[End Date:]", _
            adDate, adParamInput, , strEndDate)
        Set rec = .Execute
    End With
    Set rng = Worksheets("Starts").Range("a2")
    CopyRecordset rec, rng
    rec.Close

Thanks for your help,
-B
bflemi3
I found an example that works for one parameter, but I can't make it work for two.
!--c1-->
CODE
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;  Data Source=" & DBFile & _
";Persist Security Info=False"
Set cmd.ActiveConnection = cnn
cmd.CommandText = "My Query"
Set rst = cmd.Execute(, "31/12/2006", adCmdStoredProc)
rst.MoveFirst
Debug.Print rst!Field1, rst!Field2
rst.Close
End Sub

Have you ever used this method before?
KingMartin
Hello again,
Unfortunately I don't have any code handy using multiple parameters...
See if this helps:
http://www.sqlservercentral.com/columnists...mmandobject.asp
Martin
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.