Chapter 6: Using DAO

Having read this far, you should now be familiar with some of the basics of VBA programming. We have looked at the nature of event-driven programming; we have written our first code in VBA and investigated some of the programming constructs we can use to add more complex logic to our application; and we have taken a look at the object model of the Access application.

That's all fine - and we need to make sure that we understand the basics before we move onto some of the more advanced concepts - but so far the ground that we have covered has been fairly generic. That is to say that the concepts we have looked at so far have dealt more with the basics of VBA programming as opposed to the specifics of database programming. That's all about to change now, as we get to grips with using VBA to manipulate tables, queries, and data that we keep in our databases.

DAO vs ADO

Anyone who has had dealings in the computer industry will know that use of the TLA (Three Letter Acronym) is rife. You will not be surprised to know that things are no different in the database world. Indeed, Microsoft have elevated the use of the TLA to new heights in recent years by not only repeatedly changing the TLAs that they use to describe their methods of database access, but by trying to devise as many TLAs as they can while using the fewest number of letters .

Try It Out-Invent a Data Access Method (DAM)

1. Take a photocopy of this page.

2. Cut out the following counters (Remember, scissors can be dangerous, so if you are not sure, ask an adult to do this for you):

   

3. Put all of the counters into an empty mug.

4. With your eyes closed, pull out three or four of the counters and lay them on the desk in front of you. Hey presto! You have your own Data Access Method. But we're not finished yet!

5. Wait for about a year until everybody has got use to using the new DAM you have invented. Now put all the counters back in the mug, pull out three or four more, and proclaim the resultant letters as your new (and even better!) Data Access Method.

They have been trying this for years in Redmond and in recent years we have had Microsoft espouse the following TLAs in their attempts to make database access as simple to understand as possible

• DAO ( D ata A ccess O bjects)

• RDO ( R emote D ata O bjects)

• RDS ( R emote D ata S ervices)

• ADO ( A ctive D ata O bjects, later A ctiveX D ata O bjects)

So, before we plunge into looking at how we can use DAO from VBA, let's take a moment or two to look at what it is and how it has been developed.

For the latest information on Microsoft's data access strategy, check out the Universal Data Access section of Microsoft's web site at http://www.microsoft.com/data.

A Brief History of DAO

The initial release of Microsoft Access in November 1992 was popular enough, but one of its few failings was the relatively limited programmatic access that it allowed to the database objects within it. The database engine it used was JET 1.0, and it was possible to access tables and queries in JET from code (which at the time was Access Basic, not VBA) by using an interface known as Data Access Objects (DAO). But the number of operations that could be performed against JET objects through DAO 1.0 was very restrictive .

An interim release of Access in the following summer introduced JET 1.1 and DAO 1.1 which gave programmers the ability to perform more advanced operations against tables and queries, although again the feature set offered by DAO 1.1 still left significant room for improvement. The situation was not helped by the fact that the interim release of DAO was only available through Visual Basic 3 and not natively from within Access 1.1.

A little over a year later in the late spring of 1994, Microsoft released Access 2.0. Again, a new version of the database engine - JET 2.0 - accompanied the release along with a revised programmatic interface. DAO 2.0 was a significant improvement over its predecessors and exposed the objects within JET 2.0 as a complete hierarchical collection of objects with their own methods and properties with support for data definition (creating tables and queries) and security management as well as data manipulation.

A service pack released six months later introduced JET 2.5, but the next significant step forward came with Access 95, which provided the first full 32-bit implementation of JET. This not only offered significant performance improvements over its 16-bit predecessor, but also added support for replication.

With Access 97 came JET 3.5 and DAO 3.5. As the version numbering indicates, the changes from the previous version were fairly modest, with perhaps the most significant enhancement being the addition of ODBCDirect , a technology that allowed programmers to use the DAO interface to access data on remote enterprise servers (such as SQL Server or Sybase) efficiently .

JET 4.0, the version of the database engine that ships with Access 2002 shows a number of improvements over previous versions. This latest incarnation of JET supports Unicode and uses a SQL syntax which is not only ANSI compliant, but is also entirely compatible with SQL Server 7.0, making the task of upsizing a database from Access to SQL Server easier than ever. JET 4.0 also introduces row-level locking, improved replication functionality, and the ability to programmatically determine which users are currently accessing the database. As you would expect, the programmatic interface to JET 4.0 - DAO 3.6 - exposes all of this new functionality to the developer.

The Future of DAO

Before we discuss the specifics of DAO, let's cut to the chase and talk about why this is an important topic. The choice of using DAO and ADO is a dilemma that, depending on your current and future needs, may not be an easy choice. DAO is still the easiest and most efficient method to access data from an Access database. However, DAO has limited capabilities. On the other hand, ADO is more flexible and works well with a client/server database like SQL Server. So, do you keep things simple by using the DAO object model - and accept the limitations of JET - or use the newer ADO objects and write additional code in lieu of many built-in features? The following will help you understand the strengths and weaknesses of this dated object model.

DAO has always been an excellent interface for working with native Access data, but companies keep data in a wide variety of databases other than Access. For example, a great number of companies keep their data in large client-server databases such as Microsoft SQL Server, Sybase, or Oracle. What if you want to join data in SQL Server with data that resides in an Access database?

One approach - which has proved highly effective - is to attach the SQL Server tables to an Access database. The data remains in the SQL Server database but, to all intents and purposes, the attached tables appear just like Access tables and can be accessed programmatically using DAO in the same way that native Access tables can. The 'glue' that is used to attach tables from these client-server databases is a technology called ODBC or Open Database Connectivity. A vendor-independent technology, it was devised in the early 1990s as a method of connecting any client application to any relational database.

Although admirable for its simplicity, this approach had its limitations. For example, by accessing SQL Server tables as if they were Access tables, the developer was often prevented from taking advantage of SQL Server-specific functionality. The primary reason for this was that in order for an Access developer to programmatically fetch data from the base SQL Server table, the developer would have to use DAO, which would in turn call JET, which would in turn determine that the table wasn't actually an Access table but was a remote ODBC table and so (take a deep breath ) would in turn call ODBC, which would in turn load the appropriate database driver, which would in turn fetch the data from the base table in SQL Server (whew!). Although much of this chain of delegation was transparent to the developer, there was an impact - both in terms of response time and memory overhead - resulting from the number of DLLs that needed to be loaded for even simple operations against an ODBC database.

To get around this problem, Access 97 introduced ODBCDirect. This was a development of the RDO (Remote Data Objects) technology that had been in use in Visual Basic for a while. The advantage of ODBCDirect was that it used DAO to call ODBC, completely bypassing JET. Not only could this improve query response times against ODBC databases, but because JET was completely bypassed it meant that developers had more control over low-level connection and query configuration options.

The diagram below illustrates where each of the components we have described so far fits into the big picture as far as accessing data from Access or VBA is concerned .

ODBCDirect was a big improvement, but there was still another problem. You see, ODBC was designed to work against relational databases like SQL Server and Oracle. At a pinch it could be made to work against non-relational tabular sets of data such as Microsoft Excel spreadsheets. But there was still an awful lot of data that ODBC wouldn't work with, simply because the data was not relational. For example, many companies have electronic mail systems such as Microsoft Exchange. Such a system holds a vast amount of information, both in the content of the messages and in the details of the senders themselves . If only we could get to this data

OLEDB and ADO

Enter OLEDB , stage left OLEDB is a newer technology, developed by Microsoft, which provides access to both relational and non-relational data. So OLEDB can be used to extract data from Access databases and from SQL Server databases. But it can also be used to extract information from non-relational sources such as Microsoft Exchange, Microsoft Index Server, Active Directory Services in Windows 2000 and .NET Server, and decision support systems such as OLAP servers. It is the universality of the data that can be accessed through OLEDB - the underlying framework has been christened Universal Data Access (UDA) by Microsoft - that makes this technology so compelling. The diagram below shows the OLEDB architecture.

As you can see from this diagram, OLEDB is able to communicate with these disparate data sources through drivers which are known as "Data Providers". A number of native providers have been written- the provider for Access 2000 and 2002 is the Jet OLEDB 4.0 provider and the native provider for SQL Server is SQLOLEDB. It's possible to connect to ODBC databases for which there is no native provider by using the MSDASQL provider. This provider allows access via existing ODBC drivers.

So that is how OLEDB communicates with the different data sources. What is really cool, however, is how client applications communicate with OLEDB. OLEDB has a single programmatic interface called ADO (ActiveX Data Objects) and this is what client applications use irrespective of the type of provider. So you use the same syntax whether you are connecting to tables in SQL Server, tables in Access, or data that is in an Exchange Server or in Index Server. That's a real big plus. So, where does that leave DAO?

Microsoft has made it clear that DAO has a limited shelf-life. There will be no further development to DAO and Microsoft assert that the presence of DAO in Access 2002 is primarily intended to ease the migration path for developers whose Access 97 applications contain a large DAO codebase . That is the reason why - despite substantial changes between JET 3.x and JET 4.0 - the changes between DAO 3.5 (Access 97) and DAO 3.6 (Access 2000 and 2002) are minimal.

However, it is not as simple as that (it never is!). You see, DAO really is very, very good for working with data in Access. And ADO? Well, like any newcomer it's got quite a lot to learn. The incarnation of ADO included with Access 2002 and, more specifically , the native provider for JET (Jet OLEDB 4.0) just don't offer the same functionality. True, there are some things you can do with ADO that you can't do with DAO (such as viewing who is currently logged in to the database) but for a lot of the bread-and-butter tasks , DAO still has the edge. For example, if an Access form is based on an ADO recordset (the data in the form comes from Access tables via ADO rather than DAO) then the records are not updateable. Now that's quite a significant limitation. So, is it back to DAO then? The advice we would offer is this:

• If you will be working primarily with data in Access tables and you are unlikely to upsize your application to a client-server database such as SQL Server, then stick with DAO.

• If you will be using Access as a front end to a client-server database, then use ADO instead (and create an Access project).

• If you are working primarily with data in Access but think that you might upsize the application to a client-server database like SQL Server, then it's your call. Using ADO from the start will make the migration to a client-server database much easier but, such are the limitations of the current version of the JET 4.0 OLEDB provider, you will probably be counting down the days until you can upsize the database and use ADO in an environment which shows it off in its best light.

The decision over whether to base this book around DAO or ADO is one that has vexed the authors considerably. The problem is that for working with Access data - which is the primary focus of this book - DAO is still both faster and more feature-rich. For the moment, it is a question of choosing the trusty old linebacker over the promising new draft pick.

Important 

If you want to know more about ADO, you will be glad to know that we will be using it (albeit briefly ) in Chapter 13 (Classes). In addition, there are two appendices at the back of this book with information about ADO. The first (Appendix B) goes into more detail about choosing between ADO and DAO. The second (Appendix C) details the ADO object model.

ADO.NET and the Future of Data Access

Before we jump into this topic, let's make sure you understand that Access 2002 and .NET have nothing to do with each other and that you need not concern yourself with any of this in the current version of Access. It will certainly be important as you work with future versions of Access.

The fate of DAO (and ADO for that matter) is a little uncertain . The one thing that is very certain is that things will be changing in Access - significantly - in the near future. In February of 2002, Microsoft officially released Visual Studio .NET and the .NET Framework after nearly three years of intensive development and testing. This technology is changing the face of enterprise application development. The new data access method, ADO.NET, is built into the .NET Framework, which is a collection of some 64,000 object classes that replace practically everything software developers have used in the past. The interesting thing is that, aside from some similarly named objects, ADO.NET has little in common with ADO. In fact, it's not even an acronym for anything! Apparently the marketing folks at Microsoft figured that they had positive name recognition with ADO and wanted to leverage that as they moved forward. The main advantage of ADO.NET is that it allows data to be moved around over the Internet which was challenging or impossible using DAO, RDO, or ADO.

My crystal ball is a little cloudy but we will probably see more emphasis placed on using Access as a front end for SQL Server databases rather than for JET and then ADO will eventually be replaced by ADO.NET. I think that DAO will continue to be the right tool to use with native Access (JET) databases as long as this type of Access database is available (which should be a long while). Support for ADO.NET and the .NET Framework should be part of the next version of Microsoft Office where they will begin to move us from VBA to Visual Studio for Applications (VSA) which will look and feel more like Visual Studio.NET. For the present time, DAO is your best choice for programming an Access database in Access 2000 and Access 2002.