HandsOn Setting a Database Password

This hands-on requires the prior completion of Hands-On 19-3 in Chapter 19.

1. Prior to running this procedure, run the procedure in Hands-On 19-3 (Chapter 19) to create the Sites.mdb file. As a workaround, you can also create a blank database in the Microsoft Access user interface, close it, and then run the SetDbPassword procedure.

2. Open the Acc2003_Chap22.mdb file from the book's downloadable files, or create this file from scratch using the Microsoft Office Access user interface.

3. Switch to the Visual Basic Editor window and insert a new module.

4. In the module's Code window, enter the SetDbPassword procedure as shown below.

Sub SetDbPassword()

Dim conn As ADODB.Connection Dim strPath As String

Part III

On Error GoTo ErrorHandler strPath = CurrentProject.Path

Set conn = New ADODB.Connection With conn

.Mode = adModeShareExclusive

.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & strPath & "\Sites.mdb;" .Execute "ALTER DATABASE PASSWORD secret null " End With ExitHere:

If Not conn Is Nothing Then

If conn.State = adStateOpen Then conn.Close End If

Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

After opening a database in exclusive mode, the above procedure changes the database password from Null to "secret." Notice that the new password is listed first, followed by the old password. Notice also how the above procedure uses the State property of the ADO Connection object to determine whether the connection to the database is open. State returns adStateOpen if the Connection object is open and adStateClosed if it is not.

0 0

Post a comment