UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Passing parameters to Access Query    
 
   
bflemi3
post Aug 27 2007, 11:39 AM
Post #1

UtterAccess Guru
Posts: 836
From: MD
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
Go to the top of the page
 
+
dashiellx2000
post Aug 27 2007, 12:22 PM
Post #2

UtterAccess VIP
Posts: 9,209
From: Maryland
What is the syntax you are using right now?
Go to the top of the page
 
+
bflemi3
post Aug 27 2007, 12:25 PM
Post #3

UtterAccess Guru
Posts: 836
From: MD
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
Go to the top of the page
 
+
KingMartin
post Aug 27 2007, 04:45 PM
Post #4

Retired Moderator
Posts: 10,959
From: Prague,CZ / Kiev,UA
Hello,

the parameters are already defined?

Maybe this would help? link

Martin
Go to the top of the page
 
+
bflemi3
post Aug 28 2007, 07:34 AM
Post #5

UtterAccess Guru
Posts: 836
From: MD
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
Go to the top of the page
 
+
bflemi3
post Aug 28 2007, 07:46 AM
Post #6

UtterAccess Guru
Posts: 836
From: MD
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?
Go to the top of the page
 
+
KingMartin
post Aug 29 2007, 01:53 AM
Post #7

Retired Moderator
Posts: 10,959
From: Prague,CZ / Kiev,UA
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
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 19th June 2013 - 11:46 PM