Executing A Stored Procedure From Access Form

[DAPOOLE]

How can I run a stored procedure on SQL Server from an Access form? Whenever I execute the below code it errors with "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSql As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("qryMultiSelect")


   strSql = "exec mail_count TEST_TABLE"

            
   qdf.SQL = strSql

   Set db = Nothing
   Set qdf = Nothing

Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
    
End Sub

Note: the stored procedure exists in the database and executes fine from SQL Query Analyzer.

Thanks in advance.

 

[namliam]

What are the properties for the query qryMultiSelect??

 

[boblarson]

I believe you want this:

http://support.microsoft.com/kb/185125

 

[DAPOOLE]

What are the properties for the query qryMultiSelect??

qryMultiSelect is just a basic query. I thought one was needed to run any queries in Access? I don't know if this is right or not as I am new to Access.
Basically I just want a coded button that executes the stored procedure and outputs the results to the screen.

 

[namliam]

just a basic query runs just a basic query in access... period...

If you want to run a query/SP in SQL Server you need to send the command/query to SQL Server using a "Pass through" query, which is a 'different but same' type of query except your telling access to send the command it contains to SQL Server.
With a query open in design view, in the menu: Query > SQL Specific > Pass-through
This will change it and allow you to select where to pass it to if you look at the query properties (Right click the title bar, properties)

Good luck !

 

[DAPOOLE]

just a basic query runs just a basic query in access... period...

If you want to run a query/SP in SQL Server you need to send the command/query to SQL Server using a "Pass through" query, which is a 'different but same' type of query except your telling access to send the command it contains to SQL Server.
With a query open in design view, in the menu: Query > SQL Specific > Pass-through
This will change it and allow you to select where to pass it to if you look at the query properties (Right click the title bar, properties)

Good luck !

Thanks. Okay if I understand you I went into Query > SQL Specific > Pass-through and changed the qryMultiSelect to read exec mail_count TEST_TABLE however this time it doesn't error but neither does it output the data. Did I understand you correrectly?

 

[namliam]

you undertand me correctly, if your sp returns records then this should return them too.