Practice: Establish MS Access as a Front End to MSSQL Server 2000
We
will establish the Pubs sample database, which is installed with MSSQL
Server 2000 as a part of a typical installation, as the back end for the
present exercise once again, primarily to make the article self-contained from
the standpoint of working with Stored Procedures from scratch. To do
so, we will specify the target database details, and create a connection
between MS Access and SQL Server.
NOTE: If you completed this section in
the previous article of the series, Extend Access with Pass-Through Queries, you can use the same MS Access
database, the same connection, or perhaps both, that we created to the Pubs
database for the purposes of that article, to accomplish the procedures that
follow this section. If this works best for you, skip this section and begin
at the appropriate juncture below.
Let's start MS Access
and proceed with the preparation for executing a Stored Procedure on the
back-end database from MS Access, specifically with the establishment of a data
connection to the targeted Pubs database. To do so, we will take the
following steps:
MS
Access opens, and may display the initial dialog. If so, close it.
3.
Select File
-> New from the top menu.
4.
Select Blank
Database... from the options that are available.
The
selection can be made from the Task Pane in MS Access, as shown in Illustration
2, among other ways. Depending upon your version of Access, this may
differ.
Illustration 2: Select Blank Database ... (Compact View)
The File New Database dialog appears. Here we give
the new database a name and designate where we wish to place it.
5.
Type SQL_Server_Stored_Procedure
into the File Name box of the dialog, after navigating to a place to put
the database.
The File
New Database dialog appears as depicted in Illustration 3.
Illustration 3: The File New Database Dialog
6.
Click the Create
button.
The new database is created, and we arrive at the Database
window, as shown in Illustration 4.
Illustration 4: The New Database Opens in the Database
Window
7.
Click the Queries
icon in the Objects pane on the left side of the window.
8.
Click New atop
the Query window that appears, as depicted in Illustration 5.
Illustration 5: Click New ...
The New
Query dialog appears, from which we can select the means by which we create
our query, or, more precisely, the query object that will house the Stored
Procedure EXECUTE statement. Because we must use syntax specific to MSSQL
Server 2000, as we discussed earlier, we will not be able to use wizardry at
this juncture.
9.
Click-select Design
View.
The New
Query dialog, with our selection, appears as shown in Illustration 6.
Illustration 6: New Query Dialog - Design View Selected
10.
Click OK.
11.
Click Close
to dismiss the empty Show Table dialog box that appears.
» 
Comment and Contribute
