MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 4
June 7, 2004
Just as we cannot rely upon the graphical query design tools to create a Pass-Through query, neither will they assist us in specifying the Stored Procedure we wish to call on the back-end server. We have to manually code here, and our input has to be compatible with the dialectal requirements of the back-end RDBMS. As we did for the Pass-Through query, we will need to leave the graphic query builder and get to the SQL Specific design view.
We are simply using the Pass-Through query option as a conduit through which to send a command to the back-end database. Through this channel, the command will arrive, untranslated, in the syntax that the targeted server understands.
12. Select Query --> SQL Specific --> Pass-Through on the Query menu, as depicted in Illustration 7.
The SQL Specific editor appears.
13. Click the Properties icon atop the view, as partially shown in Illustration 8.
The Query Properties page opens. Here we will assign connection information for the back-end server. As most of us are aware, we might have created ODBC connections in advance, or perhaps have a connection already set up that we might use for our immediate purposes, but we will walk through the complete process here, as if we were doing it "on the fly," so as to cover all the bases. For more information on establishing an ODBC connection, see the MSSQL Server documentation, MS Access documentation, or other relevant sources.
NOTE: If we do not specify a connection string here, MS Access will use the default that initially appears, "ODBC." If we take that route, we will be prompted for connection information at query run time.
Please note also that some of the connection information that I depict in the illustrations will obviously need to be supplanted with settings that are contextually correct from the perspective of your own environment.
14. Click Build, the ellipses icon ("...") that appears on the right side of the ODBC Connect Str box on the Query Properties page, as shown in Illustration 9.
The Select Data Source dialog appears, defaulted to the File Data Source tab. While there are various options for the types of source we can establish, we will create a Machine Data Source at this point.
15. Click the Machine Data Source tab.
16. Click New.
The Create New Data Source dialog appears.
17. Select the System Data Source radio button.
The Create New Data Source dialog appears as depicted in Illustration 10.
18. Click Next.
19. Scrolling as necessary in the next Create New Data Source dialog that appears ("Select a driver ..."), select the SQL Server driver, as shown in Illustration 11.
20. Click Next.
The third Create New Data Source dialog appears, confirming our selections, as depicted in Illustration 12.
21. Click Finish.
The Create a New Data Source to SQL Server dialog appears.
22. Type the following in the Name text box of the dialog:
23. Type the following in the Description text box of the dialog:
Pubs DB Back End
24. In the Server selector box, select the server to which you wish to connect (mine is MOTHER in the illustrations).
The Create a New Data Source to SQL Server dialog appears as shown in Illustration 13.