1

I'm operating on a virtual machine where my SQL server and MS Access sit. I set up an ODBC connection from Access to SQL and linked a few tables. I can perform the usual operations on these tables (select/update etc.).

BUT, I'm unable to run a stored procedure for some weird reason! The procedure runs perfectly on SSMS but not when I call it from Access VBA. Following is the code I'm using to execute the proc (I need to pass 3 parameters as well, but I've excluded that from the code below for simplicity):

With CurrentDb.QueryDefs("qPass")
  .SQL = "exec [HS].[spGetXMLExtract]"
  .Execute
End With

The error returned by MS Access is

Invalid SQL statement: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Don't know if this is happening because: 1. I've got these applications on a VM; and/or 2. I need to somehow link the stored procedure to MS Access just like I did for the tables (perhaps this can't be done).

I guess there are convoluted methods I 'might' be able to adopt but I don't want to. Example:

1. Create a table in SQL with columns that store the parameters; Write an AFTER UPDATE Trigger on that table which executes my stored procedure; Fire an 'Update' query from MS Access that would update the parameters in that table and a SQL trigger then gets fired. OR;

2. Eliminate the stored procedure from the equation completely and execute it's individual statements (select/update/insert etc.) through a Sub in MS Access. Don't know if this would cause problems with creating temp tables though.

Can someone please advise on this, this is a real blocker!

Thanks in advance!

Improve this question
3
  • 1
    This should work. Are you sure "qPass" is a Pass-Through query with the correct connection string, and ReturnsRecords = False ? -- What if you run that query manually? Commented Nov 10, 2016 at 15:20
  • 1
    sounds like you diid not create the query qPass as a pass-though. When using the SQL designer, make sure you click on (select) the pass-through option. ONce done your code should work fine. Commented Nov 10, 2016 at 19:53
  • Thanks guys. I don't know why it's not working, the query seems to be a Pass Through to me. For now I've used a trigger to save time since I need to meet a deadline. But I'll into this further! Commented Nov 11, 2016 at 16:00

2 Answers 2

Reset to default
2

You will receive that error message if the QueryDef does not have a valid "ODBC;..." connection string as its .Connect property. That is how Access identifies the QueryDef as a pass-through query.

If you already have an ODBC linked table defined, you can use its .Connect property value for the .Connect property of the QueryDef, like so:

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_table1").Connect  ' grab .Connect string from linked table
qdf.sql = "exec [HS].[spGetXMLExtract]"
qdf.ReturnsRecords = False
qdf.Execute

... or, if the stored procedure does in fact return a result set:

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_table1").Connect  ' grab .Connect string from linked table
qdf.sql = "exec [HS].[spGetXMLExtract]"
qdf.ReturnsRecords = True
Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rst.EOF
    ' do stuff
    rst.MoveNext
Loop
rst.Close
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @GordThompson. I don't know why it's not working even after this. For now I've used a trigger to save time since I need to meet a deadline. But I'll into this further!
2

I do not have access right now to MS-Access, but it seems to me that the good syntax is:

`strSQL = "exec [HS].[spGetXMLExtract]"
 With CurrentDb.QueryDefs(strSQL)
'Fill parameters
 .Parameters(0) = My first param
 .Parameters(1) = My 2nd param
 .Parameters(2) = My last param
'Execute the query
 .Execute
End With `

I hope that will help !

Improve this answer

3 Comments

Thanks @Mohamad. I don't know why it's not working, I seem to have a similar syntax to yours. For now I've used a trigger to save time since I need to meet a deadline. But I'll into this further and provide an update on this thread!
Sorry, if it does not work! What is your mode of access to SQL Server: ADO, OLEDB, ... I advise you to take a look at this link: Http://accessexperts.com/blog/2011/07/29/…. Hope this can help ..
I made a mistake in writing What is your mode of access to SQL Server: ADO, OLEDB, ... Because ADO = OLEDB, I should write DOA, OLEDB, etc ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.