MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 3
June 7, 2004
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:
1. Go to the Start button on the PC, and then navigate to the Microsoft Office Access icon, as we did in the first lesson of this series, Create a Calculated Field with the Expression Builder.
2. Click the icon to start MS Access.
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.
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.
6. Click the Create button.
The new database is created, and we arrive at the Database window, as shown in Illustration 4.
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.
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.
10. Click OK.
11. Click Close to dismiss the empty Show Table dialog box that appears.