Stored Procedure pass through query fails

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
    

Answer 1

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

Answer 2

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

Answer 3

Thanks Rich P123

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

Answer 4

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.