When performing programmatic security operations, you must be logged on as a user with sufficient permissions to do that work. The usual way to do this is to open a Workspace object with the user name and password of an administrator user account, that is, a member of the Admins group. This is accomplished using the CreateWorkspace method of the DBEngine object.
For example, assume your workgroup has an administrator user account named “TopDog” with the password “AllPowerful.” You would use the following code to open a Workspace object from which to perform the remainder of your security operations:
Dim wrk As Workspace
Set wrk = DBEngine.CreateWorkspace("", "TopDog", "AllPowerful")
Workspace objects are often thought of as mere transaction spaces — a way to create multiple independent transactions. However, an equally important use is the ability they provide to programmatically log on to a workgroup as another user. An obvious disadvantage of this approach is that the user name and password of a powerful user are hard-coded into the program code. One way to avoid this is to programmatically prompt the person running the code for a user name and password, as shown in the following code fragment:
Dim wrk As Workspace
Dim strUser As String, strPwd As String
strUser = InputBox("Enter user account.")
strPwd = InputBox("Enter password.")
Set wrk = DBEngine.CreateWorkspace("", strUser, strPwd)
If you want to hard-code security information, you can also save your database as an MDE file (Microsoft Access 97) or as an executable file (Visual Basic 4.0, 5.0), so that no one can view or modify a module.
Note When you open a secure Workspace object in Microsoft Access, you can use that Workspace object only to work with DAO objects. You cannot work directly with Microsoft Access forms, reports, macros, or modules, since they’re not DAO objects.