Dealing with File Names

Access applications can use Jet databases, other databases (as linked tables), or SQL Server. But sometimes you need to work with files on your hard disk. Bogdan Zamfir provides you with every function that you’ll need to work with file names–including converting from UNC names to mapped drives (and back again).

Many Access applications require file processing for “behind the scenes” tasks (backup and restore, importing and exporting data to and from various formats, automating other applications like Word or Excel, merging documents with data retrieved from a database). And file processing involves dealing with file names that may be local or remote files and can be accessed using mapped paths or UNC. In this article, I’ll give you all the functions that you need to make your life easier. Continue reading

Posted in VBA | Leave a comment

DAO verses ADO – DAO isn’t Done Yet

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”

“In Access 2003 and Access 2007, DAO was again installed as the primary reference for recordset handling breaking the ADO primary reference trend in Access 2000 and 2002”  Garry

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:

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:

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):

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:

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.

Posted in VBA | Leave a comment

Convert Embedded Macros

Convert macros that are attached to a form or report

This process converts to VBA any macros that are referred to by (or embedded in) a form or report (or any of its controls) and adds the VBA code to the form or report’s class module. The class module becomes part of the form or report and moves with the form or report if it is moved or copied.

  1. In the Navigation Pane, right-click the form or report, and then click Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. On the Database Tools tab, in the Macro group, click either Convert Form’s Macros To Visual Basic or Convert Report’s Macros To Visual Basic.
  3. In the Convert form macros or Convert report macros dialog box, select whether you want Access to add error handling code to the functions it generates. Also, if you have any comments in your macros, select whether you want to have them included as comments in the functions. Click Convert to continue.
  4. If no class module exists for the form or report, Access creates one and adds a procedure to the module for each macro that was associated with the form or report. Access also changes the event properties of the form or report so that they run the new VBA procedures instead of the macros.
  5. To view and edit the VBA code:
  6. While the form or report is still open in Design view, if the property sheet is not already displayed, press F4 to display it.
  7. On the Event tab of the property sheet, click in any property box that displays [Event Procedure], and then click . To view the event properties for a specific control, click the control to select it. To view the event properties for the entire form or report, select Form or Report from the drop-down list at the top of the property sheet.

Access opens the Visual Basic Editor and displays the event procedure in its class module. You can scroll up or down to view any other procedures that are in the same class module.

Posted in VBA | Leave a comment

Comparing DAO and ADO Recordsets

This month, Russell turns his attention to the Recordset object and shows the similarities and differences between the DAO and ADO Recordsets.

The title of this article is almost certainly a lie. As I’ll show you, the number of methods, properties, and events in the Recordset object makes it impossible to discuss everything in one article. However, I want to emphasize the similarities and differences between the DAO and ADO Recordsets. If you’ve used the DAO records to perform record-by-record processing, you need to understand what’s special about the ADO Recordset object to use it effectively, so you might append “…For DAO Programmers” to the end of this article’s title. Continue reading

Posted in VBA | Leave a comment

An Average Column: I Mean, What Mode is Your Median

This month, Doug Steele follows up on an earlier article by Chris Weber to look at different ways to calculate “measures of central tendency”: mean, median, and mode.

How can I calculate the median for my data?

Before launching into a solution for this question, let’s go over some terminology (if you read Chris Weber’s Computing the Median Again article in the Apr-2005 issue of Smart Access, you’ll be familiar with some of these terms). You may have noticed the phrase “measures of central tendency” used in my introduction. This is a term used in statistics and covers more than just “calculating an average.” The fundamental idea is that one of the best ways to summarize a set of data and still retain part of the information is to represent the set with a single value. Measures of central tendency are ways of calculating that single number that’s representative of an entire list of numbers. There are three commonly used measures of central tendency:

  • Mean–The arithmetic average of a set of numbers (the most common measure of central tendency).
  • Median–The value of the middle item when the data is arranged from lowest to highest (assuming an odd number of observations) or the average value of the two middle items (when there’s an even number of observations).
  • Mode–The observation that occurs most frequently in a data set.

Most of you are probably familiar with the concept of mean (which is often referred to as “arithmetic average”), but the other two measures might not be as familiar to you, so an example might be in order.

Let’s assume you throw three dice a total of 12 times, and get the results shown in Table 1.

Table 1. A sample set of dice rolls.

Roll Total
1 14
2 13
3 8
4 8
5 12
6 8
7 10
8 9
9 5
10 3
11 17
12 10

It’s easy to calculate the mean: It’s (14+13+8+8+12+8+10+9+5+3+17+10)/12 = 9.75. This is the number to use when the values are evenly distributed throughout the range.

How do I find the median?

To find the median, you must arrange the 12 totals in order (it doesn’t really matter whether you arrange them in ascending or descending order, although it’s more common to use ascending order). In this case, that’s 3, 5, 8, 8, 8, 9, 10, 10, 12, 13, 14, 17. Since there’s an even number of samples, the median will be the average value of the sixth and seventh elements. The sixth element is 8 and the seventh element is 10; therefore, the median is (9+10)/2 = 9.5. This is the number to use when the values aren’t evenly distributed–when the results are skewed to one end of the range or clustered in the middle.

Finally, the mode is the value that occurs most often. With the elements arranged in order in the preceding paragraph, it’s fairly straightforward to see that 8 occurs more times than any other value, so the mode is 8. This is the number to use when you’re interested in the “most likely” number.

So the next question is, “How can I calculate these values in Access?”

The average can be easy: Access has a function, DAvg, that will compute the mean of a set of values in a specified set of records (a domain). To calculate the mean of our DiceRolls table, the code would be:

Unfortunately, Access doesn’t have similar functions to compute median and mode, so you’ll have to create your own. To be consistent with DAvg (and other Domain functions), I called the function to calculate the median DMedian and named the arguments Expr, Domain, and Criteria:

The parameters are as follows:

  • Expr is an expression that identifies the field containing the numeric data for which you want to calculate the median. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field.
  • Domain is a string expression identifying the set of records that constitutes the domain (a table name or a query name).
  • Criteria is an optional string expression used to restrict the range of data on which the DMedian function is performed. Criteria is equivalent to the WHERE clause in a SQL expression, without the word WHERE. If Criteria is omitted, the DMedian function evaluates Expr against the entire domain. Note that any field that’s included in Criteria must also be a field in Domain.

The next part of the function declares the variables that I’ll need later in the function:

I begin processing by creating a SQL string that will return Expr in a sorted order. If a value was supplied for Criteria, include it in the SQL statement, ignoring any Null values (if for no other reason than to be consistent with DAvg):

Next I instantiate a recordset, using the SQL statement created earlier, to return all of the relevant data from the domain and make sure that records are actually returned:

Assuming records were returned, I have to determine how many. To do this, I move to the end of the recordset so that RecordCount will return an accurate count. I then check to see if an odd number of records is returned by dividing the RecordCount by 2 using the Mod operator, which returns the remainder left by the division (if the remainder is 0 then I know that I have an even number of records). Dividing the RecordCount by zero also lets me know how many elements to move backwards from the end of the recordset to reach the midpoint of the records. The median will be that element.

If there’s an even number of records, I have to move backwards to the element after the midpoint of the recordset and retrieve that value. I then move backwards once more to retrieve the element before the midpoint and retrieve that value. Computing the mean of the two values retrieved will give the median:

If no records were returned, the median will be Null:

With the median calculated, I clean up after myself and the function is complete:

You’d use this function in the same way as DAvg:

Okay, can I determine the mode values of my data as well?

Determining the mode is complicated by the fact that it’s possible for more than one value to be the mode. For instance, it’s not hard to imagine a set of dice rolls that returned three 8s and three 5s. Therefore, the DMode function needs to be able to return an array. As you can see, the definitions for Expr, Domain, and Criteria are the same as with my DMedian function:

As before, a SQL statement must be created. This time, though, the SQL statement doesn’t simply return all of the qualifying values in the domain. Instead, I’m going to create an Aggregate query that returns each unique value in the domain, plus how many times that value occurs. Further, it’s going to return the values in descending order of occurrence. In other words, it will return the value that occurs the most times, followed by the value that occurs the second most times, and so on until the values that occur the fewest times appear at the end of the recordset:

As before, I instantiate a recordset, using the SQL statement I just created, to return all of the relevant data from the domain and check that records were actually returned:

Assuming that records were returned, I determine how many occurrences there were for the value that occurred the most number of times (which is right at the top of the recordset) and save that value in a variable called lngMaxFreq. I then loop through the recordset until a value with fewer occurrences is encountered. For each value that occurs the same number of times as what’s stored in lngMaxFreq, I add that value to an array. The contents of that array will represent the mode value(s) for the domain.

Finally, as before, I clean up after myself, and the function’s done:

Unfortunately, it’s not quite as easy to use this DMode function as it is to use the other Domain functions. If you were to type the following code into the Immediate window, you’d get a runtime error 13 (Type Mismatch):

Calling the DetermineMode routine, you’d see something like this:

What happens, though, if my sample data does have two modes (as in Table 2)? It turns out that the mean and median for this data are exactly the same as before, as demonstrated by these examples:

Table 2. AlternateDiceRolls.

Roll Total
1 14
2 14
3 8
4 8
5 11
6 8
7 10
8 9
9 6
10 5
11 14
12 10

However, now I have two different values that are both modes for my data:

What if my numbers aren’t in a table and I want to compute the median?

VBA allows you to use the keyword ParamArray as the last argument in the list of arguments for a function or subroutine to indicate that the final argument is an Optional array of Variant elements. This feature allows you to pass an arbitrary number of values to the routine, and you can treat that list of values as a single array.

This means that it’s possible to declare a function like this:

You can then call the function like this and have all of the values gathered into the ParamArray DataPoints:

Within the function, you must sort the array DataPoints into ascending order before you can find the middle position and determine the median.

There are several restrictions to using the ParamArray keyword. The important one here is that you can’t pass the ParamArray array to another routine, so you can’t sort the array by passing to a sort routine that will arrange the numbers into ascending order.

A second issue, though, means that passing the array to a sort routine probably wouldn’t be a good idea in any case. Since you can pass anything to the array, you must validate all of the values before you try to compute the median. For instance, how would you determine the median for red, blue, yellow, white? Since you have an even number of arguments, you’d have to calculate the average of the middle two terms: yellow and blue (presumably green if you’re mixing colors additively).

One way to deal with both issues is to create a new array within the routine and transfer only valid (that is, numeric) values into the new array. Assuming that at least one numeric value ends up in this new array, you can sort the new array and compute the median.

While I don’t intend to discuss the routine I used for doing the sort (there are plenty of comments in the code in the download database), something like the following will let you compute the median for an arbitrary number of values:

Passing my first set of dice roll values to this function would give this result:

I’m not sure there’s really any reason for such a function: As far as I’m concerned, requiring the ability to calculate the median like this is likely an indication that your tables haven’t been properly normalized. However, now you have that function if you need it.

Your download file is called Steele_AA200510.accdb
Posted in VBA | Leave a comment