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
The
third Create New Data Source dialog appears, confirming our selections, as
depicted in Illustration 12.
Illustration 12: The Confirmation Dialog
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
» 
Comment and Contribute
