Vba Code To Create Or Change (accdb) Linked Table Password

[afslaughter]

I have scoured the internet for a solution to this and have found only dead ends or “it can’t be done”. I am really hoping one of the VBA geniuses here will be able to help me out because this will be my last attempt for an answer.



Here is the break down, I have seen solutions for MDB tables, and this won’t work for accdb tables. I have seen a solution that is a user form that is not linked to the table that will change the password for a table not in use. Not the option I was looking for but I’ll take what I can get. This seems to work but it then it corrupted the table.


The problem seems to be that the table must be opened in exclusive mode and this can’t be done within the front end user file.


Optimally I would like the front end user file to be able to ether create or change the password of its linked table.


I was thinking one possible way to do this is to kill and close the link and do it then but I couldn’t figure out how to do that ether.


Thanks for any help and if this is not possible please let me know.

[afslaughter]

OK, so no help on that.
Well then can someone tell me why adding a password would corrupted the table?

[geekgirlau]

Go back a step - why do you want to change the password via code? How often will you need to do that?

[afslaughter]

I designed the database for users that are not very access savvy, so I wanted to make user interfaces for everything they may need to do. Also I will not be there to manage this for them. I split the database to have a front end user interface in one file and a the tables in the other. In the autoexec I put code to relink the tables just in case they decide to move it to another directory. So as long as everything is in the same directory it will work fine. I wanted the database to be secure so adding a password to the tables is a must. I wanted them to be able to change this without me. The solution I came up with was to generate a encrypted txt file that contain the password when the password was changed for the tables. This file would be used to retrieve the password for the tables in the autexec relink code.

On a side note I tried passwording the tables on a different computer and they did not corrupt. After it had a password the computer that originally corrupted the tables was able to change the password without corruption. I found something about SP1 pack may have fixed this problem but the information was vague. So maybe the first computer was not updated with the proper Service packs.

After this I was able to work out having a separate frontend user interface just change the table password with no problems. I was still not able to do this in the frontend file that was linked to the tables. No big deal though because everything is working fine and I think I prefer it this way.

If anyone is interested this is the code I found that worked:


strDBPath = MyPath & "MTO.accdb"
DBPass = Trim(Me.TxtCurrent)
Dim strOpenPwd As String

strOpenPwd = ";pwd=" & Trim(Me.TxtCurrent)

Set obDB = OpenDatabase(Name:=strDBPath, Options:=True, ReadOnly:=False, Connect:=strOpenPwd)
If Trim(Me.TxtCurrent) = DBPass Then
If Trim(Me.TxtNew) = Trim(Me.TxtConfirm) Then
With obDB
.NewPassword Trim(Me.TxtCurrent), Trim(Me.TxtNew)
.Close
End With
Else
MsgBox "New Passwords are not matched.", vbExclamation + vbOKOnly, "Retry"
Exit Sub
End If

Else
MsgBox "The password you have entered is not valid for the chosen database", vbExclamation + vbOKOnly, "Invalid old password."
Exit Sub
End If



Thank you for the interest and if you have any suggestions I would love to hear any input from the experts... I am very much a novice.
Thank again.

[msowards]

I've had this same problem and I'd like to report i have finally fixed it. (most of the time) . There are a couple of caveats the DB must be opened is Exclusive mode first and the project must be compiled (i.e. made an MDE or ACCDE)

Below are excerpts from my Change password routine...
Private Sub Pswd2_Txt_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Pswd2_Txt_KeyDown

'
Dim dbsDB As DAO.Database, Errtn As Integer

' there is another sub that sets the global DB_File_Type when the DB is opened
Errtn = 1
If DB_File_Type = "MDB" Or DB_File_Type = "MDE" Then

Errtn = 3
Set dbsDB = CurrentDb
Errtn = 5

' Set or change password.
With dbsDB
Errtn = 7
.NewPassword OldPswd, Pswd2
.Close
End With
Errtn = 9

Set dbsDB = Nothing
Else
Errtn = 2
Set ADO_Cnnct = CurrentProject.AccessConnection


' Create connection string by using current password.
Errtn = 4

strAlterPassword = "ALTER DATABASE PASSWORD [" & Pswd2 & "] [" & OldPswd & "];"
Errtn = 6

ADO_Cnnct.Execute (strAlterPassword)

'Clean up objects.
Errtn = 10
ADO_Cnnct.Close
Set ADO_Cnnct = Nothing
End If
Errtn = 15


Exit_Pswd2_Txt_KeyDown:
Exit Sub

Err_Pswd2_Txt_KeyDown:

Select Case Err.Number
Case 3031
MsgBox "There was a discrepancy with the Password." & vbCrLf & "Step : " & Str(Errtn) & vbCrLf & _
"The password was not changed!", vbOKOnly + vbExclamation, "Password change process Failed"
Case 3188
MsgBox "There was an error gaining exclusive access to the database file." & vbCrLf & "Step : " & Str(Errtn) & vbCrLf & _
"The password was not changed!", vbOKOnly + vbExclamation, "Password Change process Failed"
Case 3621
MsgBox "This Database is not opened in 'Exclusive' Mode." & vbCrLf & "Step : " & Str(Errtn) & vbCrLf & _
"To change the database password use the Short cut provided " & vbCrLf & _
"or use the run command line with" & vbCrLf & _
CurrentProject.FullName & " /excl <<- this comand line flag.", vbOKOnly + vbInformation, "Database Password change."
Case -2147467259
MsgBox "File is in Use." & vbCrLf & "Step : " & Str(Errtn) & vbCrLf & "Cannot Change Password at this time.", vbOKOnly + vbCritical, "Password Change Process failed"
Case Else
MsgBox Err.Description & ": " & Str(Err.Number) & vbNewLine & "Step : " & Str(Errtn) & vbCrLf & "The password changed process has failed", _
vbCritical + vbQuestion + vbOKCancel, Me.Name & ": Pswd2_Txt_KeyDown"
End Select
' clean up
If Not IsNull(ADO_Cnnct) Then
Set ADO_Cnnct = Nothing
End If
If Not IsNull(dbsDB) Then
Set dbsDB = Nothing
End If
Resume Exit_Pswd2_Txt_KeyDown



I left the error section in to see some of the possible errors I've run accross. This method works most of the time. I have occasionally still gotten "File in Use" error, but I do a compact and repair (also a VBA programed button on the same form) and then it works fine.

Here is the biggest however. It doesn't work in the Run time only environment.

So if any body else can see some improvements please feel free to illuminate us...:friends: