Chapter 21: ActiveX Data Objects - ADO

team lib

Back in Chapter 6 when we discussed DAO, ActiveX Data Objects (ADO) was briefly touched upon. However, it is important for future programming with Microsoft Access and your own development as a programmer that you have a basic understanding of ADO. In this chapter we will examine ADO and how it relates to Microsoft Access 2002. We will look at what ADO is, the basic Object Model, how we can use ADO with Access 2002 and some of the basic code structures used in VBA. We will also touch on some of the main differences with DAO.

All code is included in the ADO IceCream.mdb database Note that this database is in Access 2002 format so cannot be used in earlier versions of Access. We are also using ADO 2.7 and have all the latest Microsoft Access Data Components installed. For information on the latest MADC updates please visit http://www.microsoft.com/data/ .

Universal Data Access

One of the main ideas behind ADO is the ability to permit you to access data wherever it is held. This is not strictly limited to databases but applies to data held in text files, spreadsheets, and in other structured forms. In many organizations data is held in many different forms and on many different applications. Universal Data Access (UDA) is designed as a means to make accessing this data easier. Universal Data Access is a strategy rather than a technology, and ADO and OLE DB are the means used to make that strategy work.

Object Linking and Embedding Database (OLE DB)

OLE DB is the technology on which ADO operates. It is designed to work with almost any data store for which an OLE DB provider exists. OLD DB simply works as a translation service, taking your ADO instructions and translating them into the language of the target databases or non-database data stores. ODBC, which is commonly used to communicate with databases, was limited to only that type of data storage. OLE DB on the other hand permits you to "speak" to the system storing the required data. Furthermore, ODBC is an older technology designed from the ground up to interact with SQL relational databases, while OLE DB is the latest technology from Microsoft. In addition to interacting with databases it is designed to provide access to data wherever the data is being held.

So, What is OLE DB?

OLE DB can be thought of as a component or set of components, which can provide you with access either to a database or non-database data store. OLE DB providers are available for many current applications, for example, Oracle, SQL Server, DB2, and other non-database data stores such as Excel or text files. Its functionality can be broken down into four components:

  • Data provider - Provides access to data held on different systems and file formats.

  • Data consumer - The application that uses OLE DB methods to interact with the data. For example Microsoft Access would be the consumer when working with SQL Server, where SQL Server holds the data you require.

  • Data service provider - Generally any third-party component that provides query or cursor facilities.

  • Business component - A COM-based object that performs some business function and which in itself can be a data consumer.

ODBC is still there, but OLE DB is the preferred method for working with data held in relational databases. In fact specific optimized providers are available when you are working with ADO.NET, SQL Server, Oracle, and Jet. ADO is a mature technology and we are currently at version 2.7. Its worth noting that the default ADO library installed with Access 2002 is ADO 2.1 and you will have to change this reference to Microsoft ADO 2.7 to take advantage of the updated library. In general OLE DB will give you greater performance when working with both SQL and non-SQL data. The diagram opposite shows how using OLE DB native providers bypasses ODBC thus speeding up access to your data store. In this case an OLE DB Data Store refers to both relational databases and non-database data, such as data held in Microsoft Excel.

click to expand

As shown in the figure above, we have the OLE DB Consumer at the top level, which for our purposes is a Microsoft Access database. Using a specific OLE DB provider from within Access we can then access data held in any system, including relational databases, for which we have an OLE DB provider. When you are using an OLE DB provider directly you are said to be using a Native Provider (for example connecting to Oracle using the Oracle OLE DB Provider). However there is an OLE DB provider for ODBC that can be used with a DSN (Data Source Name ) that points to your database. For example, given a DSN of Ice we could create the connection as:

   Conn.ConString ="DSN=Ice"     Conn.Open   

The major advantage and performance gain is due to the fact that the native OLE DB provider communicates with the database in its own "native language" and bypasses the translation required when using ODBC.

ADO vs. DAO

Before looking at ADO let's take a moment to compare DAO and ADO, and provide some information for those moving over. ADO may prove more valuable when you're working with data held in systems and file formats other than Microsoft Access, and in particular, when working with SQL Server and Access Data Projects; when DAO is not available ADO is your only choice. If you are working with Internet-based applications you will again use ADO in your Active Server Pages. However one thing to watch out for when you are referencing both DAO and ADO object libraries, for example when moving a database from DAO to ADO, is that you may need to retain some DAO code while the conversion process is ongoing. However, because both libraries have object names in common you must prefix your DAO objects as such. For example, Dim rs as DAO.recordset , in this way no confusion with libraries can arise. The following short list shows you when you need to use ADO:

  • When you're dealing with non-relational data stores

  • Working with Access Data Projects and SQL Server 2000

  • If you are connecting to Oracle or DB2 or other "large" Relational Databases

  • If you access your data via the Web

  • If you intend, at some point, to move up to SQL Server you will already be half way there if you're using ADO for your coding

Many DAO objects, such as recordsets, are still available using ADO, but you may find that how they are referred to and how you use their methods and properties has changed. For example to open a recordset in DAO we could use:

   Dim rs As DAO.Recordset     Set rs = CurrentDb.OpenRecordset("tblcompany", dbOpenDynaset)   

Using ADO we would still be using a recordset but the syntax to open a recordset is slightly different:

   Dim rs As New ADODB.Recordset     rs.Open "tblcompany", CurrentProject.Connection, adOpenStatic   

As you can see we are still using a recordset but the way in which it is referenced and opened has changed.

If you're working purely in the world of Jet at the moment DAO still remains the language of choice as it is optimized for use with the Jet engine and will improve performance. However, you can expect that DAO will no longer be enhanced by Microsoft. So the features you have now are likely to be the last and ADO will be the main focus of Microsoft for programming data access. The following table lists some of the major DAO methods with their ADO counterparts. This table is not exhaustive but intended as a guide only to the major objects.

DAO Object

Property

ADO Object

Property

DBEngine

IniPath

ADO Connection

Jet OLEDB:Registry Path2

DBEngine

LoginTimeout

ADO Connection

ConnectionTimeout

DBEngine

SystemDB

ADO Connection

Jet OLEDB:System Database2

DBEngine

Version

ADO Connection

Version

DBEngine

BeginTrans

ADO Connection

BeginTrans

DBEngine

CommitTrans

ADO Connection

CommitTrans

DBEngine

Rollback

ADO Connection

RollbackTrans

DBEngine

CreateDatabase

ADO Catalog

Create

DBEngine

CreateWorkspace

ADO Connection

Open

DBEngine

OpenDatabase

ADO Connection

Open

Recordset

AddNew

ADO Recordset

AddNew

Recordset

CancelUpdate

ADO Recordset

CancelUpdate

Recordset

Clone

ADO Recordset

Clone

Recordset

Close

ADO Recordset

Close

Recordset

CopyQueryDef

ADO Recordset

Source

Recordset

Delete

ADO Recordset

Delete

Recordset

Edit

N/A

N/A

Recordset

FindFirst

ADO Recordset

Find

Recordset

FindLast

ADO Recordset

Find

Recordset

FindNext

ADO Recordset

Find

Recordset

FindPrevious

ADO Recordset

Find

Recordset

Move

ADO Recordset

Move

Recordset

MoveFirst

ADO Recordset

MoveFirst

Recordset

MoveLast

ADO Recordset

MoveLast

Recordset

MoveNext

ADO Recordset

MoveNext

Recordset

MovePrevious

ADO Recordset

MovePrevious

Recordset

OpenRecordset

ADO Recordset

Open

Recordset

Requery

ADO Recordset

Requery

Recordset

Seek

ADO Recordset

Seek

Recordset

Update

ADO Recordset

Update

As can be seen many of the properties used in ADO are much the same to their DAO equivalents. How you actually interact with them, though, may change as we shall see when we discuss many of the objects.

 
team lib


Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net