There’s been a lot of talk these days about how obsolete DAO is and how all code should be ported to use ADO and ADOx as soon as you can manage. Michael and Karleen, however, discuss several areas where ADO falls short and where you’re still going to need DAO. Note: This article was called Don’t Stick the Fork in, They’re Not Done Yet”
It’s impossible to pick up a trade publication these days that doesn’t contain the same story: ADO/ADOx from Microsoft is the “data access method of the month.” DAO is out. No new features are being put into DAO, and lots of new features are going into ADO with each version. The authors of these articles tout that all new code must go into ADO and, as much as you can manage, you should migrate your existing DAO code to ADO (with white papers coming soon from Microsoft detailing how this should be done). In this month’s issue, Stephen Forte has written an article that discusses many of the good things about ADOx (see “Creating Tables, Views, and Procedures in Access with ADOx”). The message is clear: DAO is DOA.
However, before you start this mass migration, you should have a better idea of what can’t be done in ADO with Access–at least if you want to do right by your applications and your clients. Some of the problems are due to bugs, some problems are caused by the ADO design not meshing well with Jet, while some other problems fall under the category “unknown.” Hopefully, this list will keep you from wasting many hours trying to move code to ADO that, for now, won’t work there.
Optimistic and pessimistic locking
One of the first and most important issues for developers who are working on multi-user applications is how to handle record locking. The traditional choices are optimistic and pessimistic locking:
- Pessimistic locking will lock the record as soon as it’s put into edit mode. This ensures that when the user is ready to commit their change, they’ll be able to do so. However, other users can be frustrated waiting for the record to unlock so that they can make their changes.
- Optimistic locking causes the record to remain unlocked until the moment of update. This means a second user may change the record and commit after the first user has put the record into edit mode but before the user has saved their change. At this point, you have a problem: Do you overwrite the first user’s changes or discard the second user’s changes?
ADO provides a simple LockType property. The documentation for this property says that it requires a value of adLockPessimistic for pessimistic locking and adLockOptimistic for optimistic locking. Unfortunately, the property only works in the context of the current connection, where it’s never really useful. Users who are connected from other machines or in other instances on the same machine aren’t affected by this property setting in any instance of Access but their own. Although you can change this property in DAO and get the behavior that you want, there’s no way in ADO to get the appropriate behavior.
Multi-database transactions
When you use DAO, Microsoft Jet supports transactions at the Workspace object level. If you’re updating multiple databases and need to make sure that all updates to all databases are either saved or discarded together, you need only open all of the affected databases in the same Workspace. There are many applications that leverage this capability, which has been around in DAO since Access 2.0/Jet 2.x.
Unfortunately, the world of ADO has its top hierarchy item the Connection object, which can only point at a single database. This means you can never have a transaction that spans multiple databases. If your business logic requires this rather reasonable and long-present feature to be used, you either have to:
- do it yourself with custom logic that manually tracks multiple transactions;
- realize DAO still has some tricks up its sleeve and stick with its support; or
- use components running under Microsoft Transaction Server (MTS).
Locking a table exclusively
There are times when your application must lock an entire table so no one can read from or write to it. When this functionality is required, there’s no really good workaround or manual substitute that can lock down the table. Luckily, DAO provides a special way to do this: Simply open a Recordset on the table and use the dbDenyRead or dbDenyWrite flag. From the OpenRecordset topic in Access 2000 online Help:
- dbDenyWrite prevents other users from modifying or adding records (Microsoft Jet Recordset objects only).
- dbDenyRead prevents other users from reading data in a table (Microsoft Jet table-type Recordset only).
You probably don’t need to guess–you can’t do this in ADO. If you need the functionality, don’t remove that DAO reference just yet.
Retrieving explicit and implicit permissions on an object
In Access 2.0/Jet 2.5, you might have tried to use code similar to the following to retrieve permissions for user Phydeaux on the table tblSecretProducts:
Dim db As DAO.Database
Dim cnt As DAO.Container
Dim doc As DAO.Document
Set db = CurrentDb()
Set cnt = db.Containers("Tables")
set doc = cnt.Documents("tblSecretProducts")
doc.UserName = "Phydeaux"
Debug.Print doc.Permissions
Unfortunately, this code only does half the job. While it will tell you whether Phydeaux has explicit permissions to the table, it won’t tell you about any implicit permissions Phydeaux has as a member of a group. In Access, you had to scroll through the Groups collection of the user Phydeaux, ORing together all of the bits that described the security permissions. If you didn’t do this, there was no way to find out whether a user had the permissions to do anything, explicitly or implicitly.
In Access 95/Jet 3.0, according to George Snelling, former program manager in charge of security for Microsoft Access, the Jet Engine keeps the full permissions (explicit and implicit) users had for every object. Furthermore, this is exposed as a special property called AllPermissions. It works almost the same as the previous code:
Dim db As DAO.Database
Dim cnt As DAO.Container
Dim doc As DAO.Document
Set db = CurrentDb()
Set cnt = db.Containers("Tables")
set doc = cnt.Documents("tblSecretProducts")
doc.UserName = "Phydeaux"
Debug.Print doc.Permissions
Here’s the description of the AllPermissions property from the online Help for Access 2000 (almost identical to prior versions):
“This property contrasts with the Permissions property, which returns only the permissions that are specific to the user and doesn’t include any permissions that the user may also have as a member of groups. If the current value of the UserName property is a group, then the AllPermissions property returns the same values as the Permissions property.”
According to George Snelling, this excellent addition to DAO (present since version 3.0) would be a performance gain for applications that extensively used security, such as tools documenting security permissions. Scrolling through all of the groups for every single user (for every single object) can be very time-consuming, especially if there are a lot of groups. Using AllPermissions allowed applications to potentially avoid those extensive enumerations.
I might be starting to sound like a broken record, but (you guessed it!) ADO and ADOx don’t expose this property. In order to check permissions with ADOx through its users and groups, you must abandon the optimized solution that’s been around for three versions and move back to the old method of manually enumerating the implicit Group permissions yourself.
Or you could just hang on to the DAO code and make life easier (and faster!).
Creating users and groups, and the personal identifier
When you create users and groups for Microsoft Jet, Jet takes the combination of the account name and personal identifier (PID) to make the security identifier (SID) that’s used to validate the user. Here’s a good description of this process this from the Access 95/Jet 3.0 Security whitepaper (written by George Snelling):
“When you create a new user or group account, you’re prompted for a user name and a PID (personal identifier). The PID is not the password. It’s a variable-length, alphanumeric, human-readable string that’s fed, along with the user’s name, to an encryption program to generate the SID for that account. The purpose of the PID is to enable you to re-create a user or group’s SID if your workgroup database ever becomes lost or corrupted, or if you need to create identical SIDs (identical accounts) in multiple workgroups. If you feed in the same user name and PID to the encryption program, you’ll get the same SID. The only time the PID has any relevance is when a new account is being created. It’s not used or referred to after that, nor is it stored anywhere in the database or the workgroup file.”
In case you’re not convinced of the importance of keeping the information about the PIDs intact, here’s a note from another security guru (and former Smart Access editor), Paul Litwin, from a conference paper entitled “Secure Data Solutions Using the Jet Database Engine”:
“When you create a new user or group account in Jet, you must enter a non-blank, 4—20 character, case-sensitive string. Jet combines the name of the account with the PID to create a SID for each user or group account. Once you’ve entered a PID, you can never view or change it.
“We recommend that only a single database administrator create accounts and PIDs and that this individual keep a written off-site record.”
Assuming you still retain the slightest bit of skepticism, the Security FAQ for Microsoft Access 2.0, 95, and 97 (written by Access MVPs and Smart Access contributing editors Andy Baron and Mary Chipman, as well as security gurus Chris Bell and Paul Litwin) contains this unmistakable warning in the steps to securing your database:
“Create a new user, which is the account you’ll use to secure the database. Add this new user to the Admins group. Write down the strings you use for the name and PID in case you ever need to recreate your workgroup information file. The PID isn’t the password–it’s encrypted along with the Name to create a unique token identifying the user.”
This point is emphasized many other times in the FAQ, which is available on the Web at http://support.microsoft.com/kb/165009.
It appears as though the people who implemented ADOx didn’t think this would be an important feature; they didn’t supply any way to specify a PID when creating users and groups. ADOx randomly assigns a PID to the user or group you create without giving you some other way to create that user or group account again. Here, in fact, is some early documentation on migrating DAO to ADO that mentions this “feature” of ADOx:
“Unlike with DAO, with ADOx you don’t have to create a User object before adding the user to the database with the Append method. With ADOx, you can create a new user simply by passing the name and password to the Append method of the Users collection. Note that there’s an additional parameter, PID, supplied when creating a user in DAO. This parameter isn’t required when creating a new user in ADOx because the Jet Provider automatically generates PID values.”
Isn’t that nice of them? Usually, when people potentially lock us out of our databases and make it so we can never recreate the workgroup accounts again, we prefer that they not treat it as a “feature.”
There’s an ADO way around this problem, as Jet 4.0 has added an entire new set of valid SQL DDL statements, including both “Create User” and “Create Group,” both of which take a PID parameter (and thus can be used to work around the ADOx silliness).
However, due to the high visibility of ADOx, the way many people are scared of using SQL DDL in general for fear of getting the grammar wrong, and the fact that DAO is being so openly abandoned by Microsoft, this “workaround” isn’t going to save the tons of users who unwittingly shoot themselves in the foot by trying to migrate from DAO to ADO.
Securing Access-specific objects (forms, reports, macros)
Since Access 2.0, all of the Access-specific objects have been securable through the same security model Jet uses for tables. Access specifically defined the following values back in Access 2.0 (which used a* constants, but the values are the same as the ac constants here):
Const acSecFrmRptExecute = 256 (&H100) Const acSecFrmRptReadDef = 4 Const acSecFrmRptWriteDef = 65548 (&H1000C) Const acSecMacExecute = 8 Const acSecMacReadDef = 10 Const acSecMacWriteDef = 65542 (&H10006) Const acSecModReadDef = 2 Const acSecModWriteDef = 65542 (&H10006)
For Access 2000, code modules are no longer secured through Jet, so only the top six constants are relevant anymore. The appropriate constants can be used on the Forms/Reports/Scripts containers to be inherited by new objects, or on existing objects as well.
So, what happens with ADOx? Well, they appear to have tried to add support for these object types by adding the following special constants to be used when trying to secure the objects:
Const JET_SECURITY_FORMS = _
"{c49c842e-9dcb-11d1-9f0a-00c04fc2c2e0}"
Const JET_SECURITY_REPORTS = _
"{c49c8430-9dcb-11d1-9f0a-00c04fc2c2e0}"
Const JET_SECURITY_MACROS = _
"{c49c842f-9dcb-11d1-9f0a-00c04fc2c2e0}"
Const JET_SECURITY_MODULES = _
"{c49c8432-9dcb-11d1-9f0a-00c04fc2c2e0}"
However, it seems that no one ever checked to make sure the permissions Access was using for validation were considered valid by ADOx’s SetPermissions/GetPermissions method. Why do we say that? Well, because almost all of the bits are masked out, and some can’t be set at all.
There isn’t any workaround for this problem. Your only real option is to keep using DAO if you need to secure Access-specific objects programmatically, or even to retrieve permissions on those objects!
The tip of the iceberg?
Kevin Collins, a program manager at Microsoft for the Jet Engine, has specifically stated on several occasions that in accordance with Microsoft’s policies on the TCO (total cost of ownership), Jet has no plans at present to change its file format in future versions as they have with Jet 4.0. In fact, he stated that it’s unlikely they would have changed the format this time were it not for the need to convert to Unicode for the storage of strings.
So, it’s unlikely that DAO will go away, since the existing version (DAO 4.0) should be able to connect to Jet databases of future versions. However, it’s unlikely that Microsoft will do any major work on it in the future except to fix severe bugs in future versions. Luckily, it seems almost a certainty that Microsoft will continue to ship a version of DAO, whether they call it 4.1 or something else.
It’s highly likely that our little list of things that ADO and ADOx can’t do right, and the features that have apparently been cut without hesitation in the move from DAO, are just the tip of the iceberg. You might find through testing that many other items and features are gone or broken as well. Until Microsoft addresses the entire issue of DAO and ADO parity, we really recommend that you:
- don’t fall for the hype that says you must use ADO, and even convert existing code, just because someone else says so.
- thoroughly test any code you try to migrate–that’s how the issues detailed here were found!
- don’t forget your DAO skills. You’re still going to need them for some time to come.
If you find any such bugs or issues, feel free to forward them on to either of us; if we receive enough, we might be right back here with Part 2 of this list of issues in DAO to ADO migration.
