Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Execute Access parameter query using connection object

Posted on 2002-07-12
11
Medium Priority
?
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 5

Expert Comment

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

Expert Comment

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

0
 
LVL 4

Accepted Solution

by:
zuijdhoek earned 80 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

610 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