|
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/ .
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.
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.
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.
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.
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.
|