AVG Free and Access

AVG Free automatically scans MDB files when you open them, and this cannot be turned off in the Free edition. Even on a fast PC, it could take 20-30 seconds depending on the size of the database.

This presents itself in two ways:

1. You are using Access 2000-2003, just opened the db moments ago, and you want to do a quick compact & repair. Access tells you either "Cannot open database '(database name)'. It may not be a database that your application recognizes, or the file may be corrupt." or "Cannot open 'database name', file already in use."

If you wait a little while and try it again, the compact & repair succeeds.

2. You are using Access 2007 and wish to convert a 2000-2002 format MDB into the new ACCDB format. Access tells you "You attempted to open a database that is already opened exclusively by user 'Admin' on machine '(machine name)'. Try again when the database is available."

In this case, waiting does not work. You need to close, and then re-open the database while holding the Shift key, to bypass any AutoExec or startup code. Then, you wait for little while and repeat the Convert and it will succeed.

Tracking changes in Access

Here is a method to track edits in MS-Access. The goal is to record the "who what when where" of edits made in an Access database. Since Access does not have triggers like SQL Server, we will be working at the Form level.

NOTE - UPDATED 11/11/2007
added table name to tracker

To keep things readable within the blogger format, you'll need to visit three links that will display details of what is described here.

First, you will add three objects to your database:
1. A VBA module with Windows API calls to identify the user and PC name. This code has been tested with Windows 2000, XP and Vista 32-bit editions.
2. A VBA module to examine the current record & determine what has changed to the record
3. A table to store the results. For text fields, I've indicated the length in the Description. Here is a VBA module that will create the table for you.

After adding these objects, modify each form as follows:
1. Set the Tag property for the form = the name of the underlying table.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.
3. Add the Form_BeforeUpdate event and invoke the tracking code using:

TrackChanges Me

4. If you are using subforms, you'll need to perform these three steps for each subform as well.

UPDATED 09/24/2008 - IMPORTANT NOTE
You can run a Sub in Access by using either

TrackChanges Me

or

Call TrackChanges(Me)

but you cannot use

TrackChanges(Me)

that will result in a compile error.

NOTE - UPDATED 05/23/2010
tracking blank or null fields

There was an important note left out of the original post. In order to properly track when a field is blanked-out, the fields in the tracking table Field_OldValue and Field_NewValue should be set for AllowZeroLength = True. The updated "create table" code is here:

http://www.wvmitchell.com/blogger/Create_tbl__ChangeTracker.htm

if you create the table manually you will need to set that property in the design view.