To open a database that is secured at the user level, you must supply the: Full path to the workgroup information file (system database)
Specify the workgroup information file by using the Jet OLEDB:System Database property as shown in Hands-On 10-5.
©Hands-On 10-5: Opening a Database Secured at the User Level
1. Create another backup copy of the original Northwind database file and name it NorthSecureUser.mdb.
2. Open the NorthSecureUser.mdb database and choose Tools | Security | User-Level Security Wizard to create a new workgroup information file.
3. Follow the steps of the Security Wizard. Do not change anything until you get to the screen asking for User Name and Password. Set up a user account named Developer with a password WebMaster, and click the Add This User to The List button. Click the Next button, assign Developer to the Admin group, and press the Finish button. Access will display the One-Step Security Wizard report. Print it out for your reference. Next, close the Microsoft Access application window with the NorthSecureUser database.
4. Switch back to the Acc2003_Chap10 database (see Hands-On 10-2) and press Alt+F11 to activate the Visual Basic Editor window.
5. In the Visual Basic Editor window, choose Insert | Module.
6. In the module's Code window, type the Open_WithUserSecurity procedure shown below.
7. Choose Run | Run Sub/UserForm to execute the procedure.
Sub Open_WithUserSecurity() Dim conn As ADODB.Connection Dim strDb As String Dim strSysDb As String
On Error GoTo ErrorHandler strDb = CurrentProject.Path & "\NorthSecureUser.mdb" strSysDb = CurrentProject.Path & "\Security.mdw" Set conn = New ADODB.Connection With conn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & strDb & ";" & _ "Jet OLEDB:System Database=" & strSysDb
.Open , "Developer", "WebMaster" End With
MsgBox "Secured database was opened." conn.Close Set conn = Nothing MsgBox "Database was closed." Exit Sub ErrorHandler:
MsgBox Err.Number & ": " & Err.Description End Sub
As mentioned earlier, ADO syntax is very flexible. You can use various ways to specify property settings. The code snippet below demonstrates how to avoid formatting problems with long connection strings by using the Connection object's Properties collection.
Creating and Manipulating Databases with ADO
.Provider = "Microsoft.Jet.OLEDB.4.0;" .Properties("Jet OLEDB:System Database") = strSysDb .Open strDb, "Developer", "WebMaster" End With
Notice that before you can reference provider-specific properties from the Connection object's Properties collection, you must indicate which provider you are using.
Was this article helpful?