Connecting to a Secure Database

The following procedure connects to a secured Jet database named UserLevel. I created the database with the Chap10Admin user while I was joined to the SystemDemo.mdw workgroup created earlier in this chapter. The database has a single table name WebBasedList. This is the same table used in the "Producing Form Letters" section of Chapter 9. I stripped the Users group of all table/view permissions in the database so that no user had any permissions for the WebBasedList table except the Chap10Admin user. The UserLevel.mdb and the SystemDemo.mdw files for the workgroup are available in the book's companion materials.

The string for connecting to a database protected with user-level security requires four phrases. The first phrase designates the Provider property for the procedure's Connection object. It specifies the Jet 4.0 OLE DB provider. The second phrase assigns the system database property for the connection. This is the name and path to the workgroup information file. This file is SystemDemo.mdw in the Access11Files folder (or wherever you saved it). The third phrase specifies the data source, which in this case is the secured database file, UserLevel.mdb. The fourth phrase denotes user ID and password values for logging into the secure database. In this sample, the procedure logs on as the Chap10Admin user with a Null password. No other user in the workgroup has any permissions for the WebBasedList table.

Sub OpenUserLevel()
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
   
'Open connection to target user-level secured data
'source; specify path for workgroup information
'file; designate logon ID and password (if appropriate)
cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn1.Properties("Jet OLEDB:System database") = _
    "C:\Access11Files\SystemDemo.mdw"
cnn1.Open "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "User ID=Chap10Admin;Password=;"
   
'Print first field from first record to confirm connection
rst1.Open "WebBasedList", cnn1, , , adCmdTable
Debug.Print rst1.Fields(0)
   
'Clean up objects
cnn1.Close
Set cnn1 = Nothing
   
End Sub

The two lines following the comment open a recordset based on the connection and print the first field's value from the first record. This simply confirms the operation of the sample. The table, WebBasedList, is the same one used in the earlier multiuser sample.

Adding and Deleting Users

When you develop and manage a custom application with user-level security, you are likely to add and delete users. Before you can add users, you must log on as a member of the Admins group, such as Chap10Admin. You can use the Append method of the Users collection to add users to a catalog or group. You must specify a name for the new user, and you can designate a password. ADO lets you assign a password later using the ChangePassword method for the User object. Unfortunately, you cannot assign a personal ID (PID) with this approach. Instead, ADO picks one randomly.

The following two procedures show one approach to invoking the Append method to add a new user to an application. The CallMakeUser procedure launches the MakeUser procedure as it passes along two arguments. The first argument designates a new user's name. The second argument sends a password. In the sample, the string "password" is the value of the password argument.

'Make sure NewUser account does not exist prior to running
'this procedure; for example, run callDeleteUser first
   
Sub CallMakeUser() 
MakeUser "NewUser", "password"
End Sub
   
Sub MakeUser(usrName As String, secureWord As String)
Dim cat1 As ADOX.Catalog
Dim usr1 As ADOX.User
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog for
'use in adding a new user
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Append user passed from calling routine
'and assign password
Set usr1 = New ADOX.User
usr1.Name = usrName
cat1.Users.Append usr1
cat1.Users(usr1.Name).ChangePassword "", secureWord
   
'Clean up objects
Set usr1 = Nothing
Set cat1 = Nothing
   
End Sub

The MakeUser procedure specifies a target for the new group using the Catalog object's ActiveConnection setting. Note that it designates a user ID with the authority to make a new user, and it points to a workgroup information file. The Append method in MakeUser adds a new member to the Catalog object. Therefore, this new user is not yet a member of any groups. The user created by MakeUser does not have any permissions for database objects. You can also add a member to a Group object so that the user has immediate membership in that group. One of the samples to follow uses this technique.

Even without any permissions for database objects in a database, the NewUser user can connect programmatically to any database in the SystemDemo.mdw workgroup. The user has a name and a password, and the LogonNewUser procedure demonstrates the syntax for connecting NewUser securely to the UserLevel database in the SystemDemo.mdw workgroup. Later in the chapter, after a demonstration of how to assign database object permissions and group memberships, you'll see another procedure demonstrate more advanced functionality from a programmatically created user.

Sub LogonNewUser()
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim str1 As String
   
'Open connection to target user-level secured data
'source; specify path for workgroup information
'file; designate logon ID and password
With cnn1
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Mode = adModeShareDenyNone
    .Properties("Jet OLEDB:System database") = _
        "C:\Access11Files\SystemDemo.mdw"
    .Open "Data Source=C:\Access11Files\UserLevel.mdb;" & _
        "User ID=NewUser;Password=password;"
End With
   
'Clean up objects
cnn1.Close
Set cnn1 = Nothing
   
End Sub

The next two procedures remove a user from the catalog for a database. The Delete method for the Users collection has the same syntax as the Delete method for the Tables, Procedures, and Views collection objects. The first procedure, CallDeleteUser, passes a single argument—the user name—to the second procedure, DeleteUser. The second procedure removes the user from the catalog and concurrently removes the user from any groups as well.

'Make sure NewUser account exists prior to running this
'procedure; for example, run callMakeUser
   
Sub CallDeleteUser() 
DeleteUser "NewUser"
End Sub
   
Sub DeleteUser(usrName As String)
Dim cat1 As ADOX.Catalog
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog for
'use in dropping a user
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
    
'Remove user from workgroup
cat1.Users.Delete usrName
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

You must log on to a database as a member of the Admins group to delete a user. The Delete method does not require a password. All that the second procedure needs is a string argument naming the user to delete.

Assigning Groups to Users

One common technique for administering permissions is to assign groups to users and manage permissions for groups. Users can derive all their permissions implicitly through their group memberships. The samples in this discussion add and remove group memberships from a user account. Both samples use the built-in Users group, but the same techniques work for custom groups.

The following two procedures add a group to a user account called NewUser. Make sure the user account exists before running the procedure (for example, run the CallMakeUser procedure if the user account does not exist). The first procedure, CallAddGroupToUser, passes a user name and a group name to the second procedure, AddGroupToUser, which uses the Append method to add the Group object to the Groups collection for the user. The sample passes arguments to the second procedure that tell it to add the new group to the Groups collection for a particular User object.

Sub CallAddGroupToUser()    
AddGroupToUser "NewUser", "Users"
'AddGroupToUser "Admin", "MySecretGroup1"
End Sub
   
Sub AddGroupToUser(usrName As String, grpName As String) 
On Error GoTo AddTrap
Dim cat1 As New ADOX.Catalog
Const acctNameAlreadyExist = -2147467259
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog for
'use in adding a new group to an existing user
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Append new group to Groups collection for the
'workgroup and then add group to the Groups
'collection for a user account passed in as an argument
cat1.Groups.Append grpName
cat1.Users(usrName).Groups.Append grpName
   
AddExit:
Set cat1 = Nothing
Exit Sub
   
AddTrap:
If Err.Number = acctNameAlreadyExist Then
'If account already exists, ignore the run-time error
    Resume Next
Else
    Debug.Print Err.Number; Err.Description
End If
   
End Sub

The second procedure invokes the Append method in an attempt to create a group with the name of the second argument passed to it. This procedure works for groups whether or not they already exist. Because Users is a built-in group account, it will always exist. If a group with the name of the second argument does not already exist, the Append method succeeds; otherwise, the procedure falls into an error trap with error number -2147467259 and moves on to the next statement. Then, the procedure appends the group to the Groups collection for the NewUser object. Again, if the group is already in the Groups collection for the user, the procedure progresses to the next statement.

The next two procedures remove a group from a user's Groups collection. The first procedure, CallRemoveUserFromGroup, passes user and group name parameters to the second procedure, RemoveUserFromGroup, which does the work. There is no error checking in this sample, so make sure the group belongs to the user. You can do this by running the preceding sample.

'Make sure the group account exists for the user
'prior to running this procedure
'For example, run CallAddGroupToUser
   
Sub CallRemoveUserFromGroup() 
RemoveUserFromGroup "NewUser", "Users"
End Sub
   
Sub RemoveUserFromGroup(usrName As String, grpName As String) 
Dim cat1 As ADOX.Catalog
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog for
'use in dropping a group from the Groups collection for
'an existing user
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Drop the group from the Groups collection for a user
cat1.Users(usrName).Groups.Delete grpName
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

You invoke the Delete method to remove a group from the Groups collection for a User object. Notice the hierarchical specification for an individual user. After identifying a user, the syntax requires the designation of the Groups collection and, finally, the Delete method. The syntax designates the group name as a parameter for the Delete method.

Creating, Deleting, and Tracking Groups in a Catalog

When you develop custom user-level solutions, you'll probably want to create custom groups with names that are meaningful to your clients and whose permissions fit the special requirements of your custom application. The four upcoming samples do the following:

• Create a custom group

• Delete a custom group

• Prepare a report itemizing all the groups in a catalog and the groups associated with each user account

• Toggle the membership of a user in the Admins group

• The next two procedures add a group named MySecretGroup1. After referencing a database file with a user ID sufficient to make the addition, the procedure invokes the Append method of the Groups collection. You must specify a container for the Groups collection. When you add a new group to the project's Users collection, the container is a Catalog object. When you assign a group to the Groups collection of a User object, you must specify the user as the root object for the Groups collection.

  'Make sure MySecretGroup1 does not exist before running
  'this procedure; for example, run callDeleteGroup
     
  Sub CallMakeGroup() 
  MakeGroup "MySecretGroup1"
  End Sub
     
  Sub MakeGroup(grpName As String)
  Dim cat1 As ADOX.Catalog
     
  'Instantiate catalog
  Set cat1 = New ADOX.Catalog
     
  'Set ActiveConnection property for catalog for
  'creating a custom group
  cat1.ActiveConnection = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Access11Files\UserLevel.mdb;" & _
      "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
      "User Id=Chap10Admin;Password=;"
     
  'Add custom group to the catalog's Groups collection
  cat1.Groups.Append grpName
     
  'Clean up objects
  Set cat1 = Nothing
     
  End Sub
  

The following two procedures remove a group from a catalog. You must make sure that the group already exists in the catalog before running the procedures. You can do this by running the preceding sample. In fact, the next sample removes the group added in the preceding one.

'Make sure MySecretGroup1 exists prior to running this
'procedure; for example, run callMakeGroup
   
Sub CallDeleteGroup() 
DeleteGroup "MySecretGroup1"
End Sub
   
Sub DeleteGroup(grpName As String)
Dim cat1 As ADOX.Catalog
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog for
'dropping a custom group
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Drop a group from the catalog's Groups collection
cat1.Groups.Delete grpName
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

The syntax for deleting a group closely parallels that for adding a group. The code invokes the Delete method of the catalog's Groups collection. You pass the method a single parameter—the name of the group to delete.

As you add and delete groups and users and reassign groups to users, you can easily create a custom report that tracks the group memberships for the Catalog and individual User objects. The following procedure itemizes the groups in a Catalog object that points at a specific database. Then it itemizes the Groups collection members for each user in the catalog's Users collection.

Sub ListGroupsInCat()
Dim cat1 As ADOX.Catalog
Dim grp1 As New ADOX.Group
Dim usr1 As ADOX.User
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
Set usr1 = New User
   
'Set ActiveConnection property for catalog for
'looping through groups and users
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Groups in overall catalog
Debug.Print cat1.Groups.Count & " groups are in the catalog"
For Each grp1 In cat1.Groups
    Debug.Print String(3, " ") & "* " & grp1.Name
Next grp1
Debug.Print
   
'Groups in each user
'Creator and Engine are special users that appear in the
'Users collection but do not need to be tracked
For Each usr1 In cat1.Users
    If usr1.Name <> "Creator" And usr1.Name <> "Engine" Then
        Debug.Print String(5, " ") & usr1.Groups.Count & _
            " group(s) are in  " & usr1.Name
        For Each grp1 In cat1.Users(usr1.Name).Groups
            Debug.Print String(8, " ") & "* " & grp1.Name
        Next grp1
        Debug.Print
    End If
Next usr1
   
'Clean up objects
Set usr1 = Nothing
Set cat1 = Nothing
   
End Sub

The report generated from this procedure appears in Figure 10-7. The report displays the total number of groups in the workgroup information file, and it lists the names of those groups. Then, the procedure reports the number of groups to which each user belongs. If a user does belong to any groups, the procedure prints their names. The Admin user belongs to the built-in Users groups, but the Chap10Admin user, which serves as a workgroup administrator, belongs to the built-in Users and Admins groups. The NewUser user does not belong to any group, but the ReadOnly and ReadUpdate users, which were created for the manual user-level security demonstrations, both belong to the Users groups. You can use the samples just discussed to create and delete users, groups, and user membership in groups.

Figure 10.7: A group membership report from the ListGroupsInCat procedure.

The following procedure shows one possible application of the ListGroupsInCat procedure shown a moment ago. The ToggleNewUserInAdminsGroup procedure does what its name implies. It toggles the membership of the NewUser object in the Admins group. It also documents the current status of the NewUser object in the Admins group by calling the ListGroupsInCat procedure.

Sub ToggleNewUserInAdminsGroup() 
On Error GoTo ToggleTrap
Dim cat1 As ADOX.Catalog
Const notInAdmins = 3265
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Attempt to delete membership of user in the Admins group
cat1.Users("NewUser").Groups.Delete ("Admins")
   
ToggleExit:
Set cat1 = Nothing
ListGroupsInCat
Exit Sub
   
ToggleTrap:
If Err.Number = notInAdmins Then
'If user not in Admins group, add it to the group
    cat1.Users("NewUser").Groups.Append "Admins"
Else
    Debug.Print Err.Number; Err.Description
End If
Resume Next
   
End Sub

Notice that the toggling procedure relies on error trapping. After connecting to the target database and the workgroup information file through the cat1 object reference, the procedure attempts to delete Admins from the Groups collection of NewUser. If it is successful, the procedure closes by calling ListGroupsInCat and exiting. Otherwise, an error occurs. If the error occurs because the group is not in the user's Groups collection, the procedure adds Admins to the NewUser Groups collection. Then it closes by resuming as though no error had occurred.

Setting Permissions

You can use the SetPermissions method for Group and User objects to manage the permissions available to a security account. You invoke the GetPermissions method for these objects to return a Long value that specifies the types of permissions assigned to a group or to a user. Both methods offer a wide array of outcomes; they can assign and report various permissions for a number of database object types. In addition, you can use the SetPermissions method to assign, revoke, and deny permissions as well as audit their use.

The two procedures that follow grant a group full permissions for any new table. Setting the permission for new tables has no impact for existing tables. Therefore, a group can have full permissions for all new tables and no permissions for existing tables.

'Make sure MySecretGroup1 exists before running procedure
Sub CallSetAllTablePermissionsForGroup() 
SetAllTablePermissionsForGroup "MySecretGroup1"
End Sub
   
Sub SetAllTablePermissionsForGroup(grpName As String)  
Dim cat1 As ADOX.Catalog
   
'Instantiate catalog, group, and user
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Assign full rights to new tables to grpName group
cat1.Groups(grpName).SetPermissions Null, adPermObjTable, _
    adAccessSet, adRightFull
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

The first procedure passes a group name, MySecretGroup1, to the second procedure. The second procedure invokes the SetPermissions method for the group member with that name. Therefore, you must make sure that the group exists before you run the procedure or add error-trapping logic. The method's first parameter has an explicit Null value. This parameter normally specifies the name of a database object, such as a table. A Null value indicates that you should set permissions for any new database objects. The second parameter designates a Table object type. The third parameter serves as a verb; it indicates that the command will set a permission. Other constants indicate different actions that the method can launch, such as revoking permissions. The fourth parameter grants the user full rights. Other rights grant data manipulation privileges, such as insert, update, and delete rights, as well as data definition privileges, such as create and drop rights for objects such as tables. The method and its parameters grant MySecretGroup1 full rights for all new tables in the UserLevel.mdb database file with the SystemDemo.mdw workgroup information file.

This basic design is flexible and can serve in many different situations. For example, to revoke all rights for new tables, you change the third parameter for the SetPermissions method from adAccessSet to adAccessRevoke. To set rights for an existing database object, you replace the Null for the first parameter with the database object's name.

Putting It All Together

The following two procedures tap a cross-section of prior samples and show a new twist to the SetPermissions method. The first procedure calls the MakeGroup procedure to create a new group in the SystemDemo.mdw workgroup information file. Then, it invokes the second procedure and passes along the new group's name as well as the name of a database object for which it wants to assign permissions. The last two lines in the first procedure create a new user named NewUser2 and add MySecretGroup2 to its Groups collection. In this way, NewUser2 inherits the permissions assigned to MySecretGroup2 by the second procedure.

Sub CallSetRIDTablePermissionsForGroupTable() 

'This procedure makes a group called MySecretGroup2
'Assigns Read/Insert/Delete permissions for
'WebBasedList table to MySecretGroup2
'Next, it creates NewUser2 and assigns
'MySecretGroup2 to NewUser2
   
'Before running this, delete MySecretGroup2 and
'NewUser2 from UserLevel.mdb if they exist
   
MakeGroup "MySecretGroup2"
SetRIDTablePermissionsForGroupTable "MySecretGroup2", "WebBasedList"
MakeUser "NewUser2", "password"
AddGroupToUser "NewUser2", "MySecretGroup2"
   
End Sub
   
Sub SetRIDTablePermissionsForGroupTable(grpName As String, tblName) 
Dim cat1 As New ADOX.Catalog
Dim grp1 As New ADOX.Group, usr1 As New ADOX.User
   
'Instantiate catalog
Set cat1 = New ADOX.Catalog
   
'Set ActiveConnection property for catalog
cat1.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "Jet OLEDB:System database=C:\Access11Files\SystemDemo.mdw;" & _
    "User Id=Chap10Admin;Password=;"
   
'Assign read, insert, and delete rights to an existing table
'for grpName group
cat1.Groups(grpName).SetPermissions tblName, adPermObjTable, _
 adAccessSet, adRightRead Or adRightInsert Or adRightDelete
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

The second procedure assigns read, insert, and delete permissions for the WebBasedList table in UserLevel.mdb to MySecretGroup2. Notice update permissions are missing from the list of rights. This procedure is similar to the earlier sample that applied rights for a specific database object, but this one illustrates how to concatenate three separate rights to get a combined set of permissions. Notice that the syntax uses an Or operator for concatenating rights.

The major substantive outcome of the preceding pair of procedures is that NewUser2 has select, insert, and delete permissions through its membership in the MySecretGroup2 group. The following SelectInsertDeleteAndUpdateWithNewUser2 procedure demonstrates how to connect NewUser2 to the UserLevel database and invoke its rights in the database for the WebBasedList table. The procedure starts by connecting to the database. Then it successively selects, inserts a record, and deletes the added record. Finally, it attempts to update the last record. This attempt fails since NewUser2 does not have an update right for the WebBasedList table. However, you can manually or programmatically add this right, and the attempt will succeed. A sample in the "User-Level Security via Jet SQL and ADO" section of this chapter demonstrates one approach to this task.

Sub SelectInsertDeleteAndUpdateWithNewUser2() 
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim str1 As String
   
'Open connection to target user-level secured data
'source; specify path for workgroup information
'file; designate logon ID and password
cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn1.Mode = adModeShareDenyNone
cnn1.Properties("Jet OLEDB:System database") = _
    "C:\Access11Files\SystemDemo.mdw"
cnn1.Open "Data Source=C:\Access11Files\UserLevel.mdb;" & _
    "User ID=NewUser2;Password=password;"
    
'Select and print LastName field from last record
rst1.Open "WebBasedList", cnn1, adOpenKeyset, _
    adLockOptimistic, adCmdTable
rst1.MoveLast
Debug.Print "Last Name before update: " & rst1("LastName")
   
'Add a new record
rst1.AddNew
    rst1.Fields(1) = "Rick"
    rst1.Fields(2) = "Doe114"
rst1.Update
   
'Print LastName field from last record
rst1.MoveLast
Debug.Print "Last Name before update: " & rst1("LastName")
   
'Delete previously added record
rst1.MoveLast
rst1.Find "LastName = 'Doe114'", , adSearchBackward
rst1.Delete
   
'Print LastName field from last record
rst1.MoveLast
Debug.Print "Last Name before update: " & rst1("LastName")
   
'Clear Err object and trap for error with attempt to update
Err.Clear
On Error Resume Next
   
'Save FirstName field before attempting to update its value
str1 = rst1("LastName")
rst1("LastName") = str1 & "updated"
   
'Check results in Err object and respond appropriately
If Err.Number = -2147217911 Then
'Print no permission message
    Debug.Print "No UPDATE permission for WebBasedList table"
ElseIf Err.Number <> 0 Then
'Print alternate message for another error
    Debug.Print Err.Number, Err.Description
    Exit Sub
Else
'For LastName field update table, print updated value
'and restore original LastName field value
    rst1.Update
    Debug.Print "Last Name after update: " & _
        rst1("LastName")
    rst1("LastName") = str1
    rst1.Update
End If
   
'Clean up objects
cnn1.Close
Set cnn1 = Nothing
   
End Sub