Access 2 and SQL Server stored procs

The quick and easy way to handle SQL Server stored procedures from Access 2 (and Access 97, without using ADO) is to make use of the Access "pass-through" query facility.

Create the stored procedure in the normal way on SQL Server (including parameters); as an example let us call it "myproc" with parameters @varint, @varstring
Create a querydef on Access ("GetMyProc") that is defined as pass-through (SQL specific). Define the SQL text to be executed as "exec myproc 1, 'a'" (these are dummy values which will be replaced.)
When calling the querydef in Access, you modify the underlying query when you need to call it by changing the querydef.SQL property. So if your code needs to execute the myproc stored procedure with values 199, 'Fred', all you do in Access is:

Dim db as Database
Dim q as Querydef
Set db = currentdb()
Set q = db.Querydefs("GetMyProc")
q.SQL = "exec myproc 199, 'Fred'"
q.execute [open]

You can expand this to call SQL Server stored procedures that accept both INPUT and OUTPUT parameters. See the examples in Auto Numbering and Auto Tracking .

Location Map

Updates

Access2 and SQL

Access2 and SQL

Access 2 and SQL Server stored procs

Copyright DBU 2000. Last updated 05 May 2003

Copyright DBU 2000.
Last updated 05 May 2003