Calling Stored Procedures and other SQL statements from MS Access 2003 (Pass Through)

[Marc Ozin]

Hi All,
I had a bit of a dig round on the forums but couldn't find anything to help me work out how to call stored procedures from MS Access.
After a bit of reading I managed to work it out and thought I'd share.

* ENVIRONMENT *
SERVER
MySQL v5.1
Database: TestDB

CLIENT
Windows XP
Access 2003
MySQL ODBC Driver 5.1 (http://dev.mysql.com/downloads/connector/odbc/5.1.html)

* METHOD *

On the client I setup a ODBC link Like this
Click: start > Control Panel > Administrative Tools > Data Sources (odbc)
On the System DSN tab I created a new MySQL ODBC Driver connection to TestDB called TestDB_DSN

In access 2003
Click Queries > New > Design View > OK
Close the Add table dialog
From the menu click: Query > SQL Specific > Pass-Through

you can then type your mysql sql statements. In my case I want to run a stored procedure I created called "spTest". This specific SP was written to take 2 paramerters. In this case I wanted to pass it 123 for the first and 456 for the second. Here's the SQL statement I typed to do this:
CALL spTest(123,456);

When you've written your sql statements you then have to tell access where to send them.
With the pass-through query text window still active, from the menu click:
View > Properties
in the "ODBC Connect str" field you need to enter your connection settings.
In my case I needed the following text:
ODBC;DSN=TestDB_DSN

Also if your SP is going to return record data, you need to make sure "Returns Records" is set to yes.

Lastly, click the save button
A dialog box will prompt you to name your query. I called mine "Q_spTest"

Job done! Yay!


*TO BE NOTED*

Pass-through queries are significantly faster that native ones in MS Access as the query doesn't have to go though Jet. The SQL statements are passed directly to MySQL to handle.
This means the synatx must be in MySQL format, NOT MS Access.
Also, according to my reading (athough I havent tested this) any record data returned from pass-thorugh queries will be read-only.
This means they are not suitable for editing data (athough your SP could) but are perfect for reports, populating drop-down lists, etc.

Hope this helps someone out (probably me when I've forgotten how to do it).

All the best

Marc.



Edited 1 time(s). Last edit at 08/28/2009 12:03PM by Marc Ozin.