Practice: Establish MS Access as a Front End to MSSQL Server 2000
We
will be establishing 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. To do so, we need to specify the target database particulars
and create a connection between MS Access and SQL Server.
Let's start MS Access
and proceed with the preparation for building a Pass-Through query to MSSQL
Server 2000, specifically with the establishment of a data connection to the
targeted Pubs database, taking 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 2003, 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_Front_End 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. 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.
Again,
we cannot rely upon the graphical query design tools, as we must concoct a
query using SQL that meets the dialectal requirements of the back-end RDBMS.
We will need to leave the graphic query builder and get to the SQL Specific
design view.
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. We might have created ODBC
connections in advance, 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 that 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.
NOTE: 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 Properties Icon atop the View
(Partial View)
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.
» 
Comment and Contribute
