Solved

Execute Access parameter query using connection object

Posted on 2002-07-12
11
205 Views
Last Modified: 2010-05-02
Trying to execute an Access parameter query using the connection object (I know how to do it with a rs object I think).

tried:

adoCon.Execute QryName & " ('" & CStr(sClub) & "')", , adCmdStoredProc

and

adoCon.Execute QryName & " (" & Chr(34) & sClub & Chr(34) & ")", , adCmdStoredProc

but they give errors. Where am I going wrong?



0
Comment
Question by:nickwoolley
11 Comments
 
LVL 5

Expert Comment

by:rpai
ID: 7149390
What is the error message? Please post your code.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7149398
What is the error?

0
 
LVL 4

Accepted Solution

by:
zuijdhoek earned 20 total points
ID: 7149465
You just can't do it that way. You better use a Command-object for this. This code-snippet might give you an idea.

Sub Main()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim strTable As String
    Dim strConnect As String
       
    Set cnn = New ADODB.Connection
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Common Files\Database\Database.mdb;Persist Security Info=False"
   
    cnn.Open strConnect
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "qryMultipleArtists" 'query
    cmd.Parameters.Append cmd.CreateParameter _
        ("Name", adChar, adParamInput, 255, "Graceland ")
       
    Set rst = cmd.Execute()
    strTable = rst.GetString
           
    Debug.Print strTable
   
ExitHere:
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
   
End Sub

Mark
0
 

Author Comment

by:nickwoolley
ID: 7149472
Code extract:

    Set cQueries = New Collection
   
    cQueries.Add "QMapCodes"
    With Me
        If .chkMembersImport Then cQueries.Add "QUpExcCustomers"
        If .chkBanksImport Then cQueries.Add "QUpExcBanks"
        If .chkBarredImport Then cQueries.Add "QUpExcBarred"
        If .chkCommentsImport Then cQueries.Add "QUpExcComments"
        If .chkCashDeskTransImport Then cQueries.Add "QUpExcCash"
        If .chkSessionsImport Then cQueries.Add "QUpExcVisits"
    End With
       
    For i = 1 To cQueries.Count
        On Error Resume Next
        If i = 1 Then
            conImportDB.Execute cQueries.Item(i), , adCmdStoredProc
        Else
            'Parameter queries
            'conImportDB.Execute cQueries.Item(i) & " (" & Chr(34) & sClub & Chr(34) & ")", , adCmdStoredProc
            'conImportDB.Execute cQueries.Item(i) & " ('" & CStr(sClub) & "')", , adCmdStoredProc
        End If
        If Err Then
            AddMessage "Errors running query " & cQueries.Item(i), , True
            Err.Clear
        ElseIf conImportDB.Errors.Count > 0 Then
            AddMessage "Errors running query " & cQueries.Item(i), , True
        Else
            AddMessage "query " & cQueries.Item(i) & " run", , True
        End If
    Next i
   
   
    Set cQueries = Nothing


Error Message is: Syntax error in parameters clause. Make sure the parameter exists and that you typed its value correctly.
0
 
LVL 5

Expert Comment

by:rpai
ID: 7149531
What is sClub declared? Where is the value set?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Expert Comment

by:rpai
ID: 7149544
Also, you do not need Chr(34) in this statement
'conImportDB.Execute cQueries.Item(i) & " (" & Chr(34) & sClub & Chr(34) & ")", , adCmdStoredProc

I see an additional space here:-
'conImportDB.Execute cQueries.Item(i) & " ('" & CStr(sClub) & "')", , adCmdStoredProc

Try this:-
conImportDB.Execute cQueries.Item(i) & "(" & sClub & ")", , adCmdStoredProc

If sClub is declared as String, you do not need CStr again!
0
 

Author Comment

by:nickwoolley
ID: 7149594
rpai: The following line gives the same error message:
conImportDB.Execute cQueries.Item(i) & "('" & sClub & "')", , adCmdStoredProc

sclub is declared as a string and is taken from the value in a dropdown combo. The preceding code is    

    With frmClubNames
        With .cmbClubs
            For i = 0 To rsExcludes.RecordCount - 1
                .AddItem rsExcludes.Fields("Club").Value
                rsExcludes.MoveNext
            Next i
        End With
        .Show vbModal
        sClub = .cmbClubs.Text
    End With
    Unload frmClubNames
   
    If Len(Trim(sClub)) = 0 Then
        Exit Function
    End If
0
 
LVL 5

Expert Comment

by:rpai
ID: 7149887
Try this:-
conImportDB.Execute cQueries.Item(i) & "('" & sClub & "')", , adCmdStoredProc
0
 
LVL 5

Expert Comment

by:rpai
ID: 7154725
Any progress on this?
0
 

Author Comment

by:nickwoolley
ID: 7157302
Tested it but it gives the same error, but the same query will run fine manually in Access
0
 

Author Comment

by:nickwoolley
ID: 7157343
That worked, thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now