locked
Stored Procedure pass through query fails RRS feed

  • Question

  • Hi

    I'm using MS Access 2007 and SQL Server 2008 sp2.

    I can run the pass through query in Access's Query Designer no problems. When I run it, the "select data Source" dialog pops up and I find the .dsn file that I use to link to the sql db back end. The query runs perfect.

    When I try to run the same query from VBA, I get a error pop-up

    Run-time error '3129'"
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SElECT', or 'UPDATE'.

    One thing to note is that when I run a pass through query that does not call a stored procedure, it works fine. THe commented out line "set strSQL = "select...."" works fine. It is just the stored procedure call that fails.

    Can anyone see what might be the problem?

    Thanks in Advance

    Here is the code:

        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strVar As String
        
        Set db = CurrentDb
            
        On Error Resume Next
            db.QueryDefs.Delete ("qryMyQuery")
        On Error GoTo 0
        
        'strSQL = "select UnitCost from tblOrderLines where IDOrderLines = 22794;"
        strSQL = "exec  sp_mystoredProc"
        
        Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
        Set rst = qdf.OpenRecordset(dbOpenDynaset)
                
        strVar = rst(0)
        Me!TextBoxExpenseTotal = strVar
        

    Monday, April 22, 2013 3:27 PM

Answers

  • If you DO have a valid working connection to your sql server (test with an ODBC DSN) you can just use ADO instead -- for running your stored procedure on the sql server.  Here is a sample:

    '--requires a Tools/References check on Microsoft ActiveX Data Objects 2.x Library  (2.5 or higher)

    Sub runStoredProcfromAccess()
       Dim cmd As New ADODB.Command, RS As New ADODB.Recordset, strSql As String
       Dim i As Integer, j As Integer

       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=YourServer;Database=yourDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCmdStoredProc
       cmd.CommandText = "stp_CountTest1"  '--name of stored proc -- not passing params for this sample
       Set RS = cmd.Execute
       Debug.Print RS(0)
       RS.Close
       cmd.ActiveConnection.Close
    End Sub


    Rich P

    • Marked as answer by kurgaaan Monday, April 22, 2013 7:22 PM
    Monday, April 22, 2013 4:25 PM
  • Hi

    I'm using MS Access 2007 and SQL Server 2008 sp2.

    I can run the pass through query in Access's Query Designer no problems. When I run it, the "select data Source" dialog pops up and I find the .dsn file that I use to link to the sql db back end. The query runs perfect.

    When I try to run the same query from VBA, I get a error pop-up

    Run-time error '3129'"
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SElECT', or 'UPDATE'.

    One thing to note is that when I run a pass through query that does not call a stored procedure, it works fine. THe commented out line "set strSQL = "select...."" works fine. It is just the stored procedure call that fails.

    Can anyone see what might be the problem?

    Thanks in Advance

    Here is the code:

        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strVar As String
        
        Set db = CurrentDb
            
        On Error Resume Next
            db.QueryDefs.Delete ("qryMyQuery")
        On Error GoTo 0
        
        'strSQL = "select UnitCost from tblOrderLines where IDOrderLines = 22794;"
        strSQL = "exec  sp_mystoredProc"
        
        Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
        Set rst = qdf.OpenRecordset(dbOpenDynaset)
                
        strVar = rst(0)
        Me!TextBoxExpenseTotal = strVar
        

    Your code doesn't execute the SQL statement as a pass-through query.  After you create the querydef, you need to set its .Connect property to an appropriate connection string:

    qdf.Connect = <your connection string>


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Andrey Artemyev Monday, April 22, 2013 5:07 PM
    • Marked as answer by kurgaaan Monday, April 22, 2013 7:22 PM
    Monday, April 22, 2013 4:50 PM

All replies

  • If you DO have a valid working connection to your sql server (test with an ODBC DSN) you can just use ADO instead -- for running your stored procedure on the sql server.  Here is a sample:

    '--requires a Tools/References check on Microsoft ActiveX Data Objects 2.x Library  (2.5 or higher)

    Sub runStoredProcfromAccess()
       Dim cmd As New ADODB.Command, RS As New ADODB.Recordset, strSql As String
       Dim i As Integer, j As Integer

       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=YourServer;Database=yourDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCmdStoredProc
       cmd.CommandText = "stp_CountTest1"  '--name of stored proc -- not passing params for this sample
       Set RS = cmd.Execute
       Debug.Print RS(0)
       RS.Close
       cmd.ActiveConnection.Close
    End Sub


    Rich P

    • Marked as answer by kurgaaan Monday, April 22, 2013 7:22 PM
    Monday, April 22, 2013 4:25 PM
  • Hi

    I'm using MS Access 2007 and SQL Server 2008 sp2.

    I can run the pass through query in Access's Query Designer no problems. When I run it, the "select data Source" dialog pops up and I find the .dsn file that I use to link to the sql db back end. The query runs perfect.

    When I try to run the same query from VBA, I get a error pop-up

    Run-time error '3129'"
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SElECT', or 'UPDATE'.

    One thing to note is that when I run a pass through query that does not call a stored procedure, it works fine. THe commented out line "set strSQL = "select...."" works fine. It is just the stored procedure call that fails.

    Can anyone see what might be the problem?

    Thanks in Advance

    Here is the code:

        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strVar As String
        
        Set db = CurrentDb
            
        On Error Resume Next
            db.QueryDefs.Delete ("qryMyQuery")
        On Error GoTo 0
        
        'strSQL = "select UnitCost from tblOrderLines where IDOrderLines = 22794;"
        strSQL = "exec  sp_mystoredProc"
        
        Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
        Set rst = qdf.OpenRecordset(dbOpenDynaset)
                
        strVar = rst(0)
        Me!TextBoxExpenseTotal = strVar
        

    Your code doesn't execute the SQL statement as a pass-through query.  After you create the querydef, you need to set its .Connect property to an appropriate connection string:

    qdf.Connect = <your connection string>


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Andrey Artemyev Monday, April 22, 2013 5:07 PM
    • Marked as answer by kurgaaan Monday, April 22, 2013 7:22 PM
    Monday, April 22, 2013 4:50 PM
  • Thanks Rich P123

    I did get it working both with ADO and ODBC. From your answers I realized that I missed the connection string.

    Monday, April 22, 2013 7:25 PM
  • Thank you Dirk Goldgar

    I did get it working both with ADO and ODBC. From your answers I realized that I missed the connection string.

    Monday, April 22, 2013 7:25 PM