Access via ODBC to a SQL procedure

Jockel 0
Viewed 58

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

ms-access sql-server sql
2 Answers

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

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

zep4260

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
Albert D. Kallal0
Related