Access via ODBC to a SQL procedure

Question

I have an Access database connected via ODBC to a SQL Server backend. In the SQL Server database, I have a stored procedure called Empty_Table.

The procedure does only this:

DELETE FROM TABLE

(The table should only be emptied).

How can I access this procedure in an Access database and execute it in the SQL Server database?

Thanks

This one looks related: https://stackoverflow.com/questions/29593222/execute-a-sql-server-stored-procedure-from-ms-access — Stefan Wuebbe, Sep 14 '22 at 14:32

score 1 · Answer 1 · answered Sep 14 '22 at 13:41

1

Create a pass-through query in Access, with the command EXEC Empty_Table.

More information on pass-through queries can be found here.

answered Sep 14 '22 at 13:41

zep426

179
9

score 0 · Answer 2 · answered Sep 14 '22 at 18:41

Well, as suggested, you create a pass-though query in Access.

Then in VBA code, you can do this:

 currentdb.QueryDefs("abc").Execute

And if you want to execute different server side commands, then you can do this:

with Currentdb.QueryDefs("MyPtQuery")
    .SQL = "exec dbo.MyStorProce"
    .Execute
end with

And since I tend to have ONE PT query, and I use it over and over in code? And if that stored procedure returns data/records? then you can do this:

dim rstData      as DAO.RecordSet

with Currentdb.QueryDefs("MyPtQuery").
    .SQL = "exec dbo.MyStorProce"
    .ReturnsRecords = true
    Set rstData = .OpenRecordSet
end with

Access via ODBC to a SQL procedure

2 Answers2