Exploring Multiple Active Record Sets with MS Access
(Page 1 of 4 )
This tutorial explains how to access multiple recordsets from a single connection to SQL Server 2005 in Microsoft Access. We will also explore the properties of the recordsets.
Introduction
SQL Native Client, or SQLNC for short, is a data access technology that made its debut with SQL 2005 and ADO.NET. SQLNC is only available when you are working with SQL 2005. It is not bundled with MDAC Stack, a technology which has been popular for many years in various versions.
SQLNC combines both OLEDB and ODBC features, that is, it has both SQLOLEDB as well as SQL ODBC drivers in one API. It brings with it several new features in SQL 2005 such as Multiple Active Record Sets (MARS), support for XML data type, mirroring, and so on. The rationale for the use of this driver is to work with or migrate to SQL 2005 on the one hand, and escape the wrath of 'dlls' from the MDAC stack on the other hand. It offers the possibility to return multiple recordsets from the same connection instead of the one active query per one connection that was possible with OleDB.
In this tutorial we will look at accessing multiple recordsets from a single connection to SQL Server 2005 in Microsoft Access. It will be shown how you may be able make a single connection using this driver and look at three different tables from the same connection. We will also explore the properties of the recordsets so created.
In particular we will connect to the Northwind database on the SQL 2005 server. The Northwind database did not come with the out-of-the-box installation of SQL 2005, but it was moved from a SQL 2000 server which is described in an earlier tutorial. We will be looking at three of the tables from this database, and the design of the tables as they will be referenced in this tutorial is shown in the next three pictures. These are obtained by expanding the columns folder of the related tables in the Northwind database.
Orders Table
Products Table
Order Details Table
Next: Connecting to SQL 2005 using ADO >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 6 
