HomeMS Access

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

William Pearson
By William Pearson

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-Through queries 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.
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.
Previous article
Find string in procedure, view, functions, defaults etc.
Next article
High Availability with SQL Server 2000 failover and DoubleTake-Part II

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.