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.

What are the properties for the query qryMultiSelect??

I believe you want this:

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

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.

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 !

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?

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