Featured Database Articles

MS Access for the Business Environment: Extend Access with Pass-Through Queries

About the Series ...

This article continues the series, MS Access for the
Business Environment. The primary focus of this series is an
examination of business uses for the MS Access relational database management
system. The series is designed to provide guidance in the practical application
of data and database concepts to meet specific needs in the business world. The
majority of the procedures I demonstrate in this article and going forward will
be undertaken within MS Access 2003, although most of the concepts that
we explore in the series will apply to earlier versions of MS Access, as well.

For
more information on the series, as well as the hardware / software requirements
to prepare for the tutorials we will undertake, please see Tutorial
1: Create a Calculated Field with the Expression Builder. Along with MS Access, of which we have made
repeated use in the previous articles of the series, additional application considerations
apply for this tutorial, because it introduces another Microsoft RDBMS, MSSQL
Server 2000.

For those joining the
series at this point because of a desire to work with MSSQL Server 2000 and
its components from an MSSQL Server perspective, it is assumed that MSSQL
Server 2000 is accessible to / installed on your PC, with the appropriate
access rights to the MSSQL Server 2000 environment to parallel the steps of the
article. If this is the first time MSSQL Server 2000 is being accessed on your
machine, you may need to consult the MSSQL Server 2000 online documentation for
installation and configuration instructions.

Introduction to this Tutorial

This article focuses on a topic
that is related to an earlier group of articles we have published, all of which
dealt with the use of "SQL specific" or "direct SQL"
within MS Access. As many of us are aware, MS Access can play many varied
roles in client / server architecture, and it is quite common to find it acting
as a front end to various enterprise-level, back-end databases. MSSQL
Server is a natural for the back-end partner in such arrangements, and we
can manage communication between MS Access and MSSQL Server in multiple ways.
This flexibility includes using links, ActiveX data objects, or SQL
Pass-Throughqueries to allow communication between MS Access and
SQL Server

In this article, we will devote
our efforts to the latter of the three options, and concentrate on the use of Pass-Through
queries as the medium of communication. Our examination of Pass-Through
queries will include the following:

A discussion of the nature of Pass-Through
queries, and instances in which their use is warranted;

A discussion of the advantages and
disadvantages incumbent within the choice to use Pass-Through queries;

A hands-on practice exercise that
includes the creation and operation of a Pass-Through query to a MSSQL Server 2000
database.