Microsoft Office 2000/Visual Basic Programmer's Guide

Using VBA Code to Work with User-Level Security

There are three ways to work with User-Level security from VBA code in Access databases (.mdb):

• By using the Microsoft ADO Extensions 2.1 for DDL and Security object library
  
  
• By using the Microsoft DAO 3.6 object library
  
  
• By using Microsoft Jet 4.0 SQL commands, such as CREATE USER, CREATE GROUP, ADD USER, GRANT, and REVOKE

This section provides examples of how to use ADO to work with User-Level security. For information about how to use DAO to work with User-Level security, search the DAO Reference Help for information about the Groups and Users collections and the Permissions property, or see Chapter 10, "Managing Security" in the Microsoft Jet Database Engine Programmer's Guide, Second Edition (Microsoft Press, 1997). You can use the new Microsoft Jet 4.0 SQL commands either from ADO by using the Execute method of the Connection object, or from DAO by using the Execute method of the Database object. For information about the Microsoft Jet 4.0 SQL commands and syntax, see the Microsoft Jet SQL Reference Help (C:\Windows\System\Jetsql40.chm).

Note   DAO code that is based on the DAO 3.5 object library will run in Access 2000 if you establish a reference to the new DAO 3.6 object library. For this reason, DAO security code written in previous versions of Access will continue to run correctly after you convert or enable a database to work in Access 2000, and you can write DAO code to work with security in new Access databases after establishing a reference to the DAO 3.6 object library. However, you should be aware that DAO will allow you to get and set permissions on modules in an Access 2000 database. For example, Set doc = dbs.Containers("Modules") _.Documents("Module1 will establish a reference to the Module1 module, and you can get and set permissions on it. But Access 2000 no longer stores modules in the Microsoft Jet Modules Container object, and the Jet database engine will ignore any permissions set on modules. To properly secure all modules in an Access 2000 database, you should lock the database's VBA project.

Caution   Although the Access user interface may warn you if you are changing security in a way that might lock you out of your database, Microsoft Jet does not. To avoid causing irreparable damage to your database, use caution when making programmatic changes to security. Be especially careful when making changes to permissions for the Admins group or when removing user accounts from the Admins group. Make a backup copy of the database first.