General discussion

Locked

Change password of shared database

By p.w.d.stone ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

You have to close the

by Tony Hopkinson In reply to Change password of shared ...

Connection to change the mode.
Short of using the os to see if the mdb file is in use you've no way of finding out if the file is in use. Short of twiddling with file permissions you've
no way of guaranteeing your connection will be successful.

Another desktop database PIA, have you thought of using a server based DBMS ?
MSDE might suit.

Collapse -

MS Access?

by rkuhn In reply to Change password of shared ...

Looks like VBA in Access to me.

Why not just split the db into a front end and a back end and put the password level security on the front end.

Keep it simple.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums