Connecting to Microsoft Access with ADO

(Page 1 of 6 )

Readers of this tutorial will learn how to make a Universal Data Link file which provides the basis for connecting to an MS Access database file. They will also go through the steps to be followed in writing VBA code to work with the database in accessing the Connection object.

Introduction

Access 2000 was a major milestone in MS Access as Microsoft shifted gears to make the OleDb COM interface its core data connectivity technology. This new technology, ActiveX Data Objects, became the core for MS Access and other related Office products. OleDB continues to grow as a Universal Data Access vehicle to connect to a very large number of databases including the legacy data.

This tutorial is very basic, written with the very limited objective of encouraging readers who might be planning to use ADO (DAO, RDO, E-SQL and DB Library are going to be a part of the history). The tutorial explains how to make a Universal Data Link file which provides the basis for connecting to an MS Access database file, or for that matter any other database product that has a OLEDB driver. It also describes the steps to be followed in writing VBA code to work with the database in accessing the Connection  object. A few more tutorials in the works will describe fully all aspects of ADO you need to know.

Microsoft Data Access Strategies and ADO

MDAC, or Microsoft Data Access Components, take care of connecting to a variety of relational and non-relational data sources. The components in this MDAC stack are:

• ADO (including ADOMD and ADOX): This is a high-level language that is continuously being upgraded. Its performance is somewhat inferior to direct use of OleDb or ODBC. It has great scripting support with VB and JScript. ADOMD is for Multidimensional uses such as in an Analysis Server and OLAP. ADOX is an ADO extension to take care of DDL and security related aspects of data.
  
• OleDb (SQL OleDb Provider, OleDb providers for Oracle, odbc drivers, data shape and remote data): This is the key to opening most databases of any form, including messaging, active directory, and so on.
  
• ODBC (including SQL ODBC and Oracle ODBC drivers): This is a C language interface for relational data.

The current version is MDAC 2.8.

Next: Overview of ActiveX Data Objects >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Connecting to Microsoft Access with ADO

· Yes, that is true coming straight from the vaults of MS. These remain for backward...    · i'm new person want to use the aplication asp. Can u give me the sample how to...    · I have created a system DSN called 'ForAsp' for your illustrationI have an IIS...    · Send me your email, I will send the code.Jay   >>> Post your comment now!