Jose's Database Programming Corner

Understanding Jet Security

Note: You must have a copy of Access available to create a secured database application. The Jet security system relies on having a uniquely defined system database which can only be created by the Workgroup Administrator applet that ships with Access.

Introduction

The Jet database engine employs a user level security system. In a manner similar to Windows NT and NetWare network security, the objects themselves do not have passwords assigned, but have permissions granted to user and group accounts. Once validated, a user will have the authority to do whatever is authorized by the permissions granted to the user account or to any group of which the user is a member. This permits an administrator to manage a very granular set of security attributes where, for example, an individual user may have the authority to read but not write data. Although the security model does not have the ability to assign column level permissions, through the use of queries and the "run with owner permissions" attribute, you can effectively handle permissions at the row and column level for data.

There are two core objects involved in the Jet security system: the system database and the Workspace object. The system database, which can only be created by the Access Workgroup Administrator, stores user and group account information. The Workspace object is used to open databases in the context of a user account. Permissions are then assigned to user and group accounts for database objects.

Once a system database has been established, the primary tasks in administering Jet security are creating and maintaining user and group accounts and assigning permissions to database objects.

If you haven't already done so, you should also read the Jet security white paper. You can download the white paper and the Jet security Wizard from my downloads page or from the Microsoft® Access web site.

One dissapointment in Visual Basic 4.0 and 5.0 was the continued lack of ability to create a system database without having a copy of Access available. I find this and the rather poor documentation for creating and using secured databases with VB to be a serious omission on the part of Microsoft. I'll digress for a moment to encourage you to send mail to vbwish@microsoft.com to request that the ability to create a system database be included in the next version of VB so that VB may at last have complete independence from Access. Although I think Access is one of the most powerful database programming tools available to VB programmers, I also think it's unfair of Microsoft to require that VB developers purchase a copy of Access for the simple function of creating a system database. Additionally, this makes it impossible to programmatically create a system database - something I also consider a glaring omission.

Return to Top of Page

User and Group Accounts

User and group accounts are the cornerstone of the Jet security system. User accounts represent individual users of the database. Group accounts are used to collectively assign permissions to database objects.

Default Accounts

In addition to the user and group accounts that you create, the Workgroup Administrator creates several accounts by default.

User Accounts
The Workgroup Administrator will always create the following user accounts:
- Admin
  The Admin account is the default account used by the Jet engine. If no account name and password is supplied when you start Microsoft Access or create a Workspace object, Jet will attempt a login using the Admin account and a blank password.
- Guest
  The Guest account can be used to provide access to the database for individuals who do not have a user account in the system.
Group Accounts
The Workgroup Administrator also creates several group accounts:
- Admins
  The Admins group has a special role in the Jet security system. Admins by default has administrative authority over all database objects and must always have at least one member. The Admin user account is the only member of the Admins group when a system database is created.
- Guests
  Like the Guest user, the Guests group can be used to provide access to the database to users not having regular accounts. The Guest user account is the only member of the Guests group.
- Users
  The Users group contains all user accounts except the Guest account, which by default is a member only of the Guests group.

One common cause of confusion centers around the Admin user account. Admin, with the exception of being the default account used by Jet and being a member of Admins by default, has no special authority in a database. In fact, the Admin user would be better named as "Default", since that's really all it is - the default account. Unfortunately, the name was poorly chosen by the developers.

The Admins Group

The Admins group, rather than the Admin user, is the account that has special authority under Jet. Admins always has the Administer permission for database objects. This means that members of Admins can change permissions or change ownership of objects. This gives members of the Admins group the ability to assign authority to any object in the database to other users and groups. In a secured system, you will want to control who has membership of the Admins group because these users are essentially gods in the databases they use. Even object owners (we'll discuss ownership later) are subject to the powers of members of Admins since the Admins group has the authority to change the ownership of objects (but not databases).

The term "admin" was probably used a little too liberally in Access, creating confusion for users. Admin user, Admins group, Administer permission, etc. It would have been less confusing if some variation of "admin" were used for some of these, but unfortunately its something you're going to have to learn to live with. The true security key in Access is the Administer permission - which grants authority to change permissions and ownership. The Administer permission is always available to the Admins group and by default also is available to the Admin user.

Remember that the authority of the Admins group applies only to members of the Admins group in the system database in use at the time the database was created. This is a key point which cannot be overlooked if you are securing a Jet database. If you are using the default Access system database, your application will not be secure. Anyone opening the database using the default Admin account will have Administer permission on all database objects.

Database Object Owners

The owner of a database object is the user that created the object. Along with the Admins group, owners also have special permissions on the objects they own. In fact, owners of objects always have full and irrevocable permissions on the objects they own. This is somewhat disguised by the Access user interface. While a member of Admins can revoke the Administer permission for an object owner, the action is ignored by the security system.

I've used this capability to my advantage in Access for objects that I use as templates. When creating new reports, for example, I have a blank template with several properties, controls, and procedures already set up. Using the security menu, I then revoke my own permission to modify the design. This prevents me from accidentally changing the design of the template. If I ever do need to modify the template, I can simply grant myself the Modify permission, make and save my changes, then revoke Modify permission for myself - essentially making the object read-only again.

The database owner, database object owners and the Admins group have a interesting relationship. By default, Admins will always have Administer permission for database objects. Although the object owner or a member of Admins can revoke the Administer permission, a member of Admins can grant the permission to himself or the Admins group. There's really no effective means of preventing the Admins group of having access to an object. To demonstrate this, open a database as the database owner, go to the permissions dialog box, and attempt to set the permissions on the system tables (MSys...). You'll find that the database owner can change the permissions set by Jet on the system tables for the database, even though they are not owned by the database owner. (Don't do this with a production database!)

Return to Top of Page

Permissions

There are several levels of permissions available for database objects.

Object Type	Permission	Description
Database	Open/Run	Open the database file.
Database	Open Exclusive	Open the database exclusively. Note: Generally you should probably clear this permission for all users except those who actually need it. This will prevent users from inadvertently opening the database exclusively in Access by setting the "Exclusive" check box in the file open dialog.
Table	Read Data	Open the table and read the data in the records.
	Update Data	Modify existing records.
	Insert Data	Create new records.
	Delete Data	Delete records from the table.
	Read Design	Read the design specifications for the table. Note: If any permission for the data in a table is granted, Read Design permission must also be granted. The database engine cannot present the data without being able to read the design. Revoking Read Design revokes all rights for the table.
	Modify Design	Change the design of the table.
	Administer	Assign permissions and change ownership.
Query	See Table	The permissions which can be assigned for queries are identical to those for tables.
Form	Open/Run	Open and use the form.
	Read Design	Read but not modify the form design, including code.
	Modify Design	Change the form design.
	Administer	Set permissions or change ownership.
Report	See Form	The permissions for reports are the same as those for forms.
Macro	Open/Run	Execute the macro.
	Read Design	View but change the macro definition.
	Modify Design	View and change the macro definition.
	Administer	Set permissions and change ownership.
Module	Read Design	View but not change the code in the module. Note: There is no administrative way to prevent someone from executing the code in a module. If you wish to prevent a user from running the code, you will have to build a switch in the code based on the current user account or group membership.
	Modify Design	Change the code in the module.
	Administer	Set permissions and change ownership.

There are a few things to consider regarding object permissions:

If a user has Open Exclusive permission for a database, that user can block access to other users if they successfully open it exclusively. All that's required to do this is to set the Exclusive check box in the file open dialog. Additionally, this flag is set to true by default unless its explicitly changed in the Access options dialog box. I've made it a habit to revoke Open Exclusive permission for all users. If at some point I need it, I can always grant myself the permission. This prevents someone from accidentally (or intentionally) gaining exclusive access to the file.
For administrative purposes (and most others), Jet treats tables and queries as being equivalent. They share the same name space in a database (a table and query cannot have the same name, but a table and a form can), and they share the same set of permissions. Since most queries are simply views of data, they are treated the same as tables. This has the additional effect of not being able to grant users the authority to create and save ad-hoc queries while preventing them from creating tables or data modification queries. While having tables and queries sharing the same name space has advantages in building applications, it can be limiting administratively.
Forms and reports have the same set of permissions. Although normally a user can't do anything with the data in a report, the report itself can in its code. When assigning permissions for reports, you need to be aware of what the code in the report does. For example, if you've revoked permission to create tables for regular users and the report code generates a temporary or permanent table as part of the setup, the code will fail because the user will not have authority to create the table.
You can prevent someone from running a macro, but you can't prevent someone from running one of several macros in a saved Macro object.
You can't administratively prevent someone from running code. If a user has permission to open a form or report, they can run the code behind it. Users can always run code in general modules. If you need to prevent a user from running a block of code, you'll have to build a switch into the code based on the current user or the user's group membership.
Oddly enough, users do not have the authority to see what groups they belong to. If you're writing code where you want to examine the Groups collection for a user, you'll need to create a priviledged Workspace object to do so. This means that if you're building a switch in code based on the current user, you'll need to use the CreateWorkspace method to create a workspace object based on an account with authority to read the group list for a user.
The built in CurrentUser() function in Access will return the name of the current user. There is no Visual Basic equivalent to the Access CurrentUser() function. You'll need to keep track of this yourself - presumably in the startup code.
If you're creating priviledged Workspace objects in code, you'll need to hard-code a priviledged user name and password somewhere. Be careful about how you code it and who has access to the module in design view. Additionally, since you can't prevent someone from running code, you must use caution in how you implement the string constants. If you, for example, create global constants called SUPERUSER_NAME and SUPERUSER_PASSWORD, there's nothing to stop any user from opening the Access debug window and entering ? SUPERUSER_PASSWORD to gain access to the account. Building a procedure that returns the name or password won't help either since the user could also run that code in the debug window. If you're using the security system simply to prevent users from causing inadvertent harm or track activity, this isn't much of a problem since it would be unlikely to be abused. If you truly need secured data, you'll need to go through some hoops to secure the values. This is more difficult in Access 2.0 than in VB4 or Access 7.0 since functions in Access 2.0 cannot return object variables. In VB4 or Access 7.0, you can simply lock up design view of a module and create a procedure that returns a priviledged Workspace object. Of course, a knowledgeable user could use this object to gain access to data by writing their own code - creating another security hole.
Although the permissions can be assigned for Container objects, the Access user interface does not provide access to them. If you wish to set permissions on Containers, you'll need to do so in code. Normally, you would use the permissions of the Container objects to grant or revoke permission to create new objects.
Under each of the object types in the Access permissions dialog box (except database), you can set default permissions for new objects of that type. When creating a new database, you'll save yourself considerable administrative effort if you create your user and group accounts before creating objects and then walk through each of the object types and assign default permissions for new objects. Keep in mind that changing the default permissions for new objects will not change permissions on existing objects.
A user inherits the permissions of all groups of which he is a member. This means that when permissions are checked, the least restrictive set of permissions are assigned based on those permissions explicitly assigned to a user or the groups where the user has membership.
If you have built a split front-end/back-end design (tables in one file and forms, reports, code, etc., in another), users must have permissions for tables in both files. If you grant permission for a table in the data file, but don't grant it for the attached table in the application file, the user will not have access to the table.
If you are working with server tables, you also need to synchronize permissions in the server database. Although Jet is not aware of server security, it cannot violate it.
When installing an application, don't forget that the user must also have rights to the file or directory where the data is located on the network. Make sure the user has network rights to access the database file and the system database file. Remember that while your code can do and end-run around Jet security by creating a priviledged Workspace, there's no way for your code to bypass network security (unless of course you're an expert at cracking security systems).

Return to Top of Page

Securing a Jet Database

There are several steps involved in securing a new or existing Jet database, but one item is absolutely essential for the security system to be effective. You must use the Access Workgroup Administrator to create a new and unique system database.

Here are the steps to follow to secure a new database:

Create a System Database
Create the Owner Account
Add the Owner to Admins
Remove the Admin User from the Admins Group
Enable the Login Dialog
Login as the Owner
Create the Database
Revoke All Permissions for Admin and the Users Group

Creating a System Database

To create a system database, start the Workgroup Administrator and click the Create... button. In the Workgroup Owner Information dialog box, enter your name, organization, and a workgroup ID of up to 20 characters. Before continuing, write these values down. If you ever need to recreate the system database, you will need to enter these values exactly as you enter them here. It's also a good idea to use a difficult to guess workgroup ID so no one else can create an identical system database. The workgroup administrator will then prompt you for a location for the new file (the file name is normally system.mda, although that name isn't required). After choosing OK again, you will see the Confirm Workgroup Information dialog. Verify that the information is correct and click OK. You will receive a message that you have successfully created the new system database. Dismiss this message and the original dialog for the Workgroup Administrator. When you're done, put the values for the name, organization and workgroup ID in a safe place where you can find them if you need them. Remember, you must use the exact same values if you ever need to recreate the system database.

Setting up Accounts

Once you have created a new system database, start Access and open any database. You will be logged on as the Admin user. From the Security menu, choose Users..., click New... and enter the name you wish to use for the owner account and a Personal ID for this account. Make sure you write these values down and store them in a safe place. If you ever need to recreate the account, you will have to enter the same values exactly as you enter them here. After creating the account, you can immediately add it to the Admins group.

Once the new owner account has been created and added to the Admins group, you can remove the Admin user from the Admins group. Remember, you must first add the new owner account to Admins before you can remove the Admin user since the Admins group must always have at least one member. At this point, you should also enable the login dialog box by assigning a password to the Admin user account.

Unless you have a need for a guest login, I also recommend that at this point you assign a difficult to guess password to the Guest user and remove the user from the Guests group. While the Guest account by default will have no permissions for any objects, disabling a free login gives you a small extra bit of insurance.

Creating the Database

After you have completed setting up the accounts, you are ready to create the database. First, you must quit and restart Access. With a password assigned to the Admin user, you will see the login dialog box. Enter the name of the owner account you created earlier (it won't have a password yet). From the empty database window, click New Database... on the File menu and create the new database.

To protect the owner account, you should immediately assign it a password. (As always, make the password difficult to guess.)

Before creating any objects, you should setup the default permissions you will use for users and groups. From the Security menu, choose Permissions.... In the permissions dialog, you will see your user account, Admin, and Guest. If you select Groups, you'll see Admins, Guests, and Users. Since at this point there are no objects in the database, each class of object will have only the entry in the list with the exception of the Database object type, which will show only . Starting with the current database, revoke all permissions for the Admin user. Next, revoke (at a minimum) Administer permission for the Users group.

As an added security measure, I revoke all permissions from the Users group for all objects and create my own equivalent of Users for the database. This ensures that only those users I have expressly granted authority to will be allowed even to open the database. Additionally, since the default Admin user will always be a member of Users, this also ensures that anyone who is not using your system database will not be able to open the database.

Once you have completed these steps, you should quit and restart Access, logging in using the new owner account and password. Before beginning to create database objects, you may wish to setup whatever user and group accounts you intend to use and assign default permissions to the various classes of database objects. This will make it easier to manage permissions later when you have a large number of objects created.

Return to Top of Page

Accessing a Secured Database

There are two keys to accessing a secured Jet database: the SystemDB property of the DBEngine and the Workspace object. Before setting up a workspace, you must set the SystemDB property to point to your system database so that you can use your user and group accounts, then use the appropriate account to create a Workspace object. Let's take a look at a code sample:

' Note: msUser, msPassword, and msSystemDB are module level variables whose
' values have been established elsewhere. mWS is a module level Workspace object

  DBEngine.SystemDB = msSystemDB
  Set mWS = DBEngine.CreateWorkspace(msUser, msUser, msPassword)

You'll need to establish the value of the system database, user name, and password before creating the workspace. Since the system database is typically a fairly stable property, you can probably store the value in the registry using SaveSettings and GetSettings. (In a really stable environment, you may even be able to get with coding the path to the system database as a constant.) The user name and password, however, are typically more dynamic (they may change every time the program is run). You will probably need to build a login dialog box to obtain these values.

Do NOT append your Workspace object to the Workspaces collection of the DBEngine. The Workspaces collection is shared by all instances of the DBEngine, so if the Workspace is appended, another application could loop through the collection and use your Workspace to bypass your login dialog box.

Once a Workspace has been established, you can proceed to call the OpenDatabase method to open your database file. Depending on the nature of the application and your preferred strategy for developing the application, you may wish to declare the Workspace and Database object variables so that they are persistent for the life of the application (created in the startup code as global or module variables) or create them on demand. At a mimimum, you will wish to declare they user name and password variables so their duration is the life of the program or the user will need to provide them every time you open a database. The "conventional wisdom" is to create data access objects on demand to lessen the possibility of a corrupted database if the application or machine should crash while the application is open. If you are willing to risk the possibility of corrupted files, you may choose to have the objects be persistent for the life of the program. If you decide to create them on demand, keep in mind that you will take a performance penalty for doing it.

Return to Top of Page

Programming Jet Security

In most cases, you can use Microsoft Access to manage security for your application. Access has a user interface for creating user and group accounts, assigning users to groups, and granting or revoking permissions. While the interface is somewhat clumsy to use, it is sufficient for most tasks. There are, however, some circumstances where you may need to manage security programmatically.

Assigning permissions to Container objects
The database Container objects can be assigned permissions, but there is no interface in Access for doing this. The most common reason to assign Container permissions would be to prevent users from creating objects of a certain class.
Preventing user accounts from being added to the Users group
The Access user interface - not the Jet engine - automatically puts user accounts in the Users group. If you don't want users in the Users group, you can either programmatically remove them or programmatically create them and simply not add them to the Users group.
Changing permissions dynamically
If you want to change object permissions dynamically while the code is running, you'll need to program the security settings. Keep in mind that an alternative to dynamically changing permissions in the program is to simply perform the work in the context of a priviledged Workspace object. Also remember that in either case, you'll need to provide a means of creating a priviledged Workspace in code - meaning that somewhere you'll need to hard-code the name and password of an account that's a member of the Admins group. Whether you code the values as strings in the CreateWorkspace method, use string constants in the application, or save the values in the registry or some other configuration file, you will be to some extent exposing your security system to being cracked.
Providing administrative capability for end users without Access
If you have end users that need to administer the accounts or permissions but don't have Access available, you'll need to build your own user interface for managing security.

You may of course simply not like the Access interface and want to replace it. I don't like it myself and would probably write a replacement if I had the time. Here are some of the problems with the Jet security interface:

The small, fixed size dialogs make it difficult to work with large number of objects.
There is no means provided to see the inherited permissions that users have for objects.
You need to use several different little dialog boxes to work with related objects, rather than a single, coordinated interface that does it all.
Users are prevented by the Jet engine from seeing their own group memberships. Although I suppose that this helps prevent malicious users from gaining access to places where they don't belong, it's fairly unusual. Users of Windows NT and NetWare, for example, can see but not control their group memberships.

The lack of ability to see inherited permissions in particular is something I consider to be a serious deficiency in the Access security interface. This can make it extremely difficult to detect who has permissions for an object or what permissions any given user has for an object.

These are the steps you need to take to manage user and group accounts:

Create a User Account
Use the CreateUser method of the Workspace object. Keep in mind that this needs to be done in the context of a user account that has the authority to create accounts.
Add a User to a Group
Use the Append method of the Users collection of the Group object or use the Append method of the Groups collection of the User object. This is often somewhat confusing since there are essentially two means of seeing the same data. Each User object has a Groups collection that represents the Groups of which they are a member. Additionally, each Group object has a Users collection that represents the users which have membership in the group. You can use either method, but you should probably make it a habit to consistently use one approach or the other rather than mixing and matching both.
Delete a User or Group account
Use the Delete method of the Users or Groups collections of the Workspace object.

You can find sample code in the Visual Basic help files.

Granting and revoking permissions for database objects is done by using the Permissions property of (normally) the appropriate Document object. The Document objects are members of the Documents collection of the associated Container object. These objects are a common point of confusion for many Jet programmers, so let's take a closer look at them.

A Jet database has a large number of objects and collections. One of the most misunderstood is the Containers collection and it's children. When you look at a database using the Access database window, you'll see tabs for Tables, Queries, Forms, Reports, Macros, and Modules. Additionally, when looking at the database programmatically, you can see a TableDefs collection (representing saved tables), a QueryDefs collection (presenting saved queries), and from within Access, a Forms and Reports collection. If you look at an object model chart of a database, you'll also see a Containers collection having the following Container objects: Tables, Forms, Reports, Scripts, and Modules. Here's what the various objects represent:

Object	Represents	Usage
TableDefs Collection	Saved Tables	Use the TableDefs collection to see or modify the structure of the table.
QueryDefs Collection	Saved Queries	Use the QueryDefs collection to see or modify the structure of queries.
Forms Collection	Open Forms	This represents currently open forms and is used only at run-time within Access. You can see and modify form properties while the user is working with the form or even design the form programmatically.
Reports Collection	Open Reports	This is the same as the Forms collection, except it refers to open reports. Again, this collection is only used within Access and is not available in Visual Basic.
Tables Container	Saved Tables and Queries	Remember, tables and queries in Access share the same name space, thus the single container for both types of objects. Use the Tables container to get to the individual Document objects representing each saved table or query. It is the Document object that has the Permissions property.
Forms Container	Saved Forms	Unlike the Forms collection, the Forms container provides access to all saved forms and can be used to set form permissions.
Reports Container	Saved Reports	This is the same as the Forms container, but refers to reports. Use the Reports container to set permissions for reports.
Scripts Container	Save Macros	Why the Access developers called this "Scripts" instead of "Macros" is beyond me, but there it is. Scripts represents saved macros. Remember when assigning permissions for Macros that there can be many individually callable macros within a single Script document object. You can only set the permissions for the entire Document object, not the individual macros contained within it.
Modules Container	Saved Modules	Use the Modules container to set design time permissions for code modules in Access. Remember, there is no way to administratively prevent someone from running the code. You can only grant or revoke permission to see it or modify it.

To assign permissions, take the following steps:

Create a reference to the Document object representing the database object for which you wish to assing permissions.
Set the UserName property of the Document to the name of the user or group who's permissions you wish to modify.
Modify the value of the Permissions property to grant or revoke the appropriate permission.

When modifying the Permissions property, the key to remember is that the value is a bit field representing all available permissions. To grant a permission, use the bitwise Or operator with the appropriate permission constant. To revoke a permission, use the bitwise And Not operator. Be careful not to use the addition (+) or subtraction (-) operators.

The following example revokes and then grants permission to delete data from a table for the Users group:

  ' db object reference created elsewhere
  Dim doc As Document

  ' create object reference for Customers table
  Set doc = db.Containers("Tables").Documents("Customers")
  ' set UserName property to the Users group
  doc.UserName = "Users"
  ' revoke the Delete Data permission
  doc.Permissions = doc.Permissions And Not dbSecDeleteData
  ' grant it
  doc.Permissions = doc.Permissions Or dbSecDeleteData
  ' release reference
  Set doc = Nothing

Return to Top of Page

Suggestions for Managing Accounts

As with any user level security system, you can save yourself a considerable amount of administrative effort by using groups to assign permissions and using user accounts strictly to validate logins. I generally divide users into several potential groups:

Administrators
Administrators manage the security system and control access to database objects. In a larger organization, this may a separate group, but in most cases it will be one or two users who are also database developers. This group will typically have full permissions on all objects.
Developers
This group is responsible for building and modifying database objects. In a smaller organization, this group will also be responsible for administration. Developers will generally have modify permission on all objects and normally will need to have full permissions on all data.
Managers
This group is responsible for managing vital data and business rules. The group typically will have special access to sensitive or business critical data, values available for lookup tables, and other special configuration items. These users will generally not have design permission for objects but will have full permissions on most data. The only data which would normally be unavailable to this group is data used strictly for configuration by the developers.
Regular Users
This are the typical front-line users of the database who will be doing data entry, ad-hoc querying, and reporting. This group will normally have the ability to insert, update, and delete data only for those tables that their job requires them to use.
New Users
These are users who either have not received training on the system or are in the process of being trained on the system. Access to modifying and creating records is restricted until they have learned the proper procedures and business rules that apply to the data.

With one exception, I always assign permissions only to groups rather than users. That exception is the database owner account. Rather than work with an individual user account, I create a special account for administrative use. In this case, the database owner is a role rather than an individual user. This account (often simply "dbo") is the account which actually owns the database and often owns many of its objects. The account has complete authority over all objects and data.

In addition to managing object permissions, another common use for user accounts is to track user activity. It can often be helpful to know who is creating and modifying data. However, in order to do this, you will need to code the appropriate insert, update, and delete procedures for the tables on which you wish to enable this type of tracking.

There are two general methods to enable this type of tracking. You can add fields to the table to store the name of the user who created or last modified a record or you can use a log table. Adding fields to the table is generally simpler, but using a log table can be more flexible and may provide better performance.

Adding Fields
You can add one or two fields to track the user who created and if desired the user who last modified a record. The problem with the approach is that you cannot see what modification was done and you cannot track deletions. Additionally, you will be adding the overhead of retrieving the fields if you use SELECT * FROM in a query. One benefit, however, is that you do not need to join tables to retrieve the values when you need them.
Using a Log Table
By using a log table, you can track inserts, updates and deletes. A typical log table might contain the name of the table being modified, the primary key for the row being inserted, updated, or deleted, the user name performing the change, the date and time of the change, and the SQL statement that changed the data. While this approach provides complete flexiblity and complete accountability, it requires additional coding to managing inserting rows in the log table, periodic maintenance of the log (removal of old records), and a more complex query to retrieve the data. Additionally, this assumes that you can express the insert, update, or delete as a SQL statement or can determine some other method of tracking the changes.

In either case, you will need to modify your code to make sure that updates are properly logged. If you are using Visual Basic to develop the application, this requires adding additional code anywhere the data is changed. In an Access application, this can be more complex since you will need to prevent users from opening the tables for modifications in the database window. One method of doing this is to use the security system to make the appropriate tables read-only, then using a priviledged Workspace in code to change data. Another method is to use queries designed with the WITH OWNERACCESS OPTION clause in the query. This allows you to design a query that allows modifications on a table for which the end user does not have permission. (In the Access query design window, set the Run Permissions to Owners to enable this option.)

Regardless of the method you choose, you may wish to restrict the number of tables which have activity tracking in order to minimize the associated overhead and coding complexity. Using a log table only for critical data and adding created by and/or modified by fields to other tables that are less sensitive can also be an acceptable compromise.

Return to Top of Page