I am starting out in IT, and learning VBA and SQL to write a database for the front of house staff at the office. Included in the specifications for the database is password protection (easy enough) and a means to change the password programatically (again not too tough).
I’ve written (or more accurately stolen from the textbook and adapted) the following code-
Private Sub cmdChange_Click()
Dim strCurrent, strPath As String
If txtPassword1 = txtPassword2 Then
If IsNull(Me![txtCurrentChange]) Or (Me![txtCurrentChange]) = “” Then
strCurrent = “NULL”
Else: strCurrent = txtCurrentChange
End If
Dim connection As ADODB.connection
Dim strPassword As String
strPassword = “ALTER DATABASE PASSWORD ” & Me.txtPassword1 & ” ” & strCurrent & “;”
Set connection = New ADODB.connection
connection = adModeShareExclusive
Response = MsgBox(strPath, vbInformation)
With connection
.Open “Provider=Microsoft.Jet.OLEDB.4.0; ” & “Data Source= P:\Reception\Receptionists\Database\Reception.mdb”
.Execute (strPassword)
End With
Else
Response = MsgBox(“Passwords do not match”, vbExclamation, “Error”)
End If
End Sub
The problem I have is that it’s a shared database located on the network drive, with shared access as default. This means that the code throws an error as you cannot change a password unless you have exclusive access.
What I should like to know is if you can change the connection to exclusive access during a session (assuming no other users are logged on at the time), or if the database would have to be closed and re-opened as exclusive.
If this is the case, is there a way to test for exclusive access so I can grey-out the command button for the password form unless the database is opened exclusively (along with a suitable error message and instructions).
As a final point, is it possible to list the path of the database dynamically as a string. In the code above I have set a static database path, which is fine until someone moves or renames the database. I’ve heard of something called DBPath, but can’t seem to find anything useful about it.
Any comments will be gratefully received,
Thanks,
Peter