News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, April 20, 2011

Relink password-protected database

If you need a function to relink Access tables – there is a good-old Relink Access tables from code. And if your backend database is password-protected you have to modify 2 lines in this procedure to supply password.

Declare password:

Public Const cPassword As String = "MyPassword"

Open database:

Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

Replace with:

Set dbLink = DBEngine(0).OpenDatabase(strDBPath, False, True, ";pwd=" & cPassword)

Set Connect property:

.Connect = ";Database=" & strDBPath

Replace with

.Connect = ";Database=" & strDBPath & ";PWD=" & cPassword


Blogger illibrium said...

This code tweak is great! I could not have gotten Dev Ashish's fRefreshLinks() code to work with my password protected back-end (BE) without these changes.

I have a couple things to add that will hopefully make a lot of other people even happier--One more tweak to the code and it freely works whether the BE is password protected or not.

After applying only the changes above, the code would only work once--the first time you connect to a password protected database from a non-password protected database. After that, I'd get an error I've seen other get where "MS Access" would get appended to tables names and cause the relink to fail.

A fix here posted by JimFox with credit given to TomMcCauley: mostly helped me past this error, but I'd get more errors if using the code to connect from a password protected to non-password protected version of the same database.

After some tweaking to the "fParseTable" function, I came up with this:

************code start*************
Function fParseTable(strIn As String) As String
If InStr(strIn, "MS A") > 1 Then
strIn = Left(strIn, (InStr(strIn, "MS A") - 1))
fParseTable = strIn
End If
If InStr(1, strIn, ";") > 1 Then
fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
End If
End Function
*************code end**************

This modified function allows you to refresh the connection to a password-protected database or connect to a different copy of a password-protected database.

A crazy side effect is that the code will also work with non-password versions of the BE as well! (The password gets ignored and the correct connection string gets used.)

With Dev Ashish's "fRefreshLinks" function, Alex Dybenko's changes above and my modified "fParseTable" function (made with the assistance of JimFox and TomMcCauley), you get a very versatile re-link function that will relink to your BE no matter if it's password protected or not!



12:33 PM  
Blogger Alex Dybenko said...

Thanks for addition!

4:32 PM  
Anonymous Anonymous said...


How could I modify the code to connect to multiple BE?

Thanks for this info!

7:13 PM  
Blogger Alex Dybenko said...

I normally put list of tables to attach into local table, together with BE name or ID, and then go through this table list, attaching each table to corresponding BE

11:52 AM  

Post a Comment

<< Home