Linking Tables from a password protected back end

[rpeare]

I swear I had this code working earlier this morning but now every time it tries to link the external table I get prompted for a password even though the connection to the back end database is open. Anyone see where I'm going wrong?

Dim fs
Dim sSource
Dim db As Database
Dim tdf As TableDef


'On Error GoTo ERRHANDLER
Set fs = CreateObject("Scripting.filesystemobject")
sSource = CurrentProject.Path & IIf(Right(CurrentProject.Path, 1) <> "\", "\", "") & "Data.accdb"
If fs.FileExists(sSource) Then
    Set db = OpenDatabase(sSource, False, False, "MS Access;pwd=" & fldPW & "")
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            If DCount("[Name]", "Msysobjects", "[Name] = '" & tdf.Name & "'") = 1 Then
                CurrentDb.Execute "DROP TABLE " & tdf.Name
            End If
            Debug.Print tdf.Name
            DoCmd.TransferDatabase acLink, "Microsoft Access", sSource, acTable, tdf.Name, tdf.Name
        End If
    Next tdf
    db.Close
    Set db = Nothing
Else
    MsgBox "This database expects the back end and the front end in the same folder" & vbCrLf & vbCrLf & "The back end database was not found", vbOKOnly, "ERROR Connecting to Back End"
    Exit Sub
End If
Set fs = Nothing

[ranman256]

There's no reason to use code. Just link the table via ODBC with the password. No code failures.

[aytee111]

You are trying to link to tables in an external database, right? You should be looking at tabledefs in THIS database, not the external one.

[rpeare]

I do not need an overly simplistic solution. I am not doing this for spurious reasons.

My back end is password protected.
The front end will be in the same folder as the back end.
Each time the database is opened there will be *no* linked tables
I want this code to examine the tabledefs in the external (password protected) database and link all tables in that external database to the current database.
Part of the database closing procedure is to remove all links so there is no data in the front end if it's opened without a proper password.

[aytee111]

This code is working for me. I don't see where your password is set, did you debug thru the code and see if fldPW has a value when you run this?

[rpeare]

it does I can see it when I do a debug.print fldpw

that's what's driving me crazy

[rpeare]

....
I just went back to it, did not change the code in the slightest and it worked....

[aytee111]

Can you open the back-end normally and view the data? If you hard-code the fldPW to the actual password does that fix it?

[rpeare]

And I closed it and re-opened it and it prompts for the password again on each table... this is really weird behavior.

Even with a hard coded password it still prompts me. I can't figure out what's allowing it to work sometimes and other times not work.

I'm enclosing a sample database because this is driving me crazy.

FrontEnd.zip

the back end password is: $3cR3T!!

[aytee111]

What about the back-end?

[rpeare]

The back end should be in that zip file did it only contain the front end?

[aytee111]

I was using my Downloads folder and getting the same result as you. I copied it to My Docs and it works!

[rpeare]

It has both worked and not worked from a folder on my desktop
I have also copied it to c:\testfolder\ and it still prompts for a password with each table

[aytee111]

I wouldn't think that c:\testfolder would work, c drive sometimes has strange permission settings.

[rpeare]

I found the problem

If DCount("[Name]", "Msysobjects", "[Name] = '" & tdf.Name & "'") = 1 Then
     CurrentDb.Execute "DROP TABLE " & tdf.Name
End If

This section of code is getting confused between doing my dcount on the foreign database or the local database. I've modified the code and all is well.