Re-link backend that is password protected 1

[billmeye]

I have successfully used Microsofts code 'How to relink back-end tables with the common dialog control in Access 2000' to relink my backend (their Article ID : 209862) with a back end that is not password protected. But, when I password protect it the code first says it can't find my backend (even though it hasn't moved) and then when I try to relink it I get error 52:bad name or file number.

Anybody have any idea?

Thank You.

 

[ByteMyzer]

The following function should help you:

Public Sub RefreshLinkSetPWD(tableName As String, password As String)

    Dim db As DAO.Database

    Set db = CurrentDb
    db.TableDefs(tableName).Connect = _
        Replace(db.TableDefs(tableName).Connect, _
        ";DATABASE=", ";PWD=" & password & ";DATABASE=")
    db.TableDefs(tableName).RefreshLink
    db.Close

End Sub

To use this function to set the password in the front end table link named MyTable1, with a password of MyPassword, simply use:

RefreshLinkSetPWD "MyTable1", "MyPassword"

See if this works for you.

 

[billmeye]

Thank you for your reply. I'm not sure how to use it.

Here is the code that checks for back end links:

' Tests a linked table for valid back-end.
On Error GoTo Err_Form_Open
Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then ' Is a linked table.
On Error Resume Next ' Turn off error trap.
strTest = Dir(Mid(td.Connect, 11)) ' Check file name.
On Error Resume Next ' Turn off error trap.
strTest = Dir(Mid(td.Connect, 11)) ' Check file name.

On Error GoTo Err_Form_Open ' Turn on error trap.
If Len(strTest) = 0 Then ' No matching file.

If MsgBox("Could not find Data File " & _
Mid(td.Connect, 11) & ". Please choose the location of the data file.", _
vbExclamation + vbOKCancel + vbDefaultButton1, _
"Can't find data file.") = vbOK Then ...

When my back end has no password everything works great and
mid(td.connect,11) is c:\folder\database_be.mdb
and
Dir(Mid(td.Connect, 11)) is database_be.mdb.
When my back end has a password nothing works and
mid(td.connect,11) is PWD=mypassword;DATABASE=c:\folder\database_be.mdb
and
Dir(Mid(td.Connect, 11)) is "".

I'm a novice and do not know the full meaning behind this code. Could there be something that needs to be modified to accept the password? Thank You for your help.

 

[PHV]

Replace this:
Mid(td.Connect, 11)
By this:
Mid(td.Connect, InStr(td.Connect, "DATABASE=") + 9)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[billmeye]

PHV you have my eternal gratitude. That solved it!
Could you explain the meaning behind the number after the .connect command?

Thanks.