Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jun 7, 2004

MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 4

By William Pearson

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.

Illustration 7: Select Query --> SQL Specific --> Pass-Through

The SQL Specific editor appears.

13.  Click the Properties icon atop the view, as partially shown in Illustration 8.

Illustration 8: Click the Properties Icon atop the View (Partial View)

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.

Illustration 9: Click the Ellipses ("...") Icon to the Right of the ODBC Connect Str Box

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.

Illustration 10: The Create New Data Source Dialog

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.

Illustration 11: Select the SQL Server Driver

20.  Click Next.

The third Create New Data Source dialog appears, confirming our selections, as depicted in Illustration 12.

Illustration 12: The Confirmation Dialog

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:

Pubs DB

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.

Illustration 13: The Create a New Data Source to SQL Server Dialog

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM