UtterAccess Discussion Forums > Passing parameters to Access Query

Full Version: Passing parameters to Access Query

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

bflemi3

Aug 27 2007, 11:39 AM

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

Aug 27 2007, 12:22 PM

What is the syntax you are using right now?

bflemi3

Aug 27 2007, 12:25 PM

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

Aug 27 2007, 04:45 PM

Hello,

the parameters are already defined?

Maybe this would help? link

Martin

bflemi3

Aug 28 2007, 07:34 AM

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

Aug 28 2007, 07:46 AM

I found an example that works for one parameter, but I can't make it work for two.

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

Aug 29 2007, 01:53 AM

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.