Solved

Execute Access parameter query using connection object

Posted on 2002-07-12
11
209 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

786 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