Retrieving the connection string for a linked table in PW protected DB

[robert demo via AccessMonster.com]

I'm trying to retrieve the connection string for a linked table in a
backend that is password protected. I've modified the code shown below to
temporarily check that the backend has been successfully opened by showing,
via a messagebox, the last record in the table. The connection string
should be provided in the last line shown, but I always get strTemp = "".
I'm certain that the backend has been opened because of the messagebox.

The code works absolutely fine for non-password protected backends.

Here's the snippet (I've added comments in CAPS for code that I've added):

Public Function GetConnectString(Optional strTableName As String, Optional
BackupFileFullName As String, Optional blFullString As Boolean = False) As
String

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim strTemp As String
Dim lngRecordCount As Long
Dim rsTEST As DAO.Recordset 'TEST RS TO SEE IF PW PROTECTED DB IS OPEN

If BackupFileFullName <> "" Then 'PW PROTECTED DB WAS PASSED
Set dbs = DBEngine.Workspaces(0).OpenDatabase(BackupFileFull Name, False,
False, "MS Access;PWD=loveya")
Else
Set dbs = CurrentDb
End If

'THE FOLLOWING GETS THE LAST RECORD TO VERIFY PW PROTECTED DB WAS OPENED
Set rsTEST = dbs.OpenRecordset(strTableName, dbOpenDynaset)
If rsTEST.RecordCount > 0 Then
rsTEST.MoveLast
MsgBox rsTEST("BUILDING_NO")
End If

'Extract connect string
If Len(strTableName) > 0 Then
Set tdf = dbs.TableDefs(strTableName)
Else
For Each tdf In dbs.TableDefs
If IsUserTable(tdf) And Len(tdf.Connect) > 0 Then
If left(tdf.Connect, 4) <> "ODBC" Then
Exit For
End If
End If
Next tdf
End If
strTemp = tdf.Connect 'THIS IS THE PROBLEM!!!!!

'See if the connect string is valid (an error is generated if invalid)
On Error Resume Next
Set rst = tdf.OpenRecordset
lngRecordCount = rst.RecordCount

'Parse the connect string to return to calling procedure/object
If Err = 0 Then
If Len(strTemp) = 0 Then
GetConnectString = "<None>"
Else
If blFullString = True Then
GetConnectString = strTemp
Else
GetConnectString = Mid(strTemp, InStr(1, strTemp, ";DATABASE=") + Len
(";DATABASE="))
End If
End If
Else
Err.Clear
GetConnectString = "<Not Pointing to Valid File>"
End If
On Error GoTo 0

'Clean up stuff
Set tdf = Nothing
Set dbs = Nothing

--
Message posted via http://www.accessmonster.com

[Sean]

robert demo via AccessMonster.com wrote:

I'm trying to retrieve the connection string for a linked table in a
backend that is password protected. I've modified the code shown below to temporarily check that the backend has been successfully opened by showing, via a messagebox, the last record in the table. The connection string should be provided in the last line shown, but I always get strTemp = "". I'm certain that the backend has been opened because of the messagebox.
The code works absolutely fine for non-password protected backends.

Here's the snippet (I've added comments in CAPS for code that I've added):
Public Function GetConnectString(Optional strTableName As String, Optional BackupFileFullName As String, Optional blFullString As Boolean = False) As String

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim strTemp As String
Dim lngRecordCount As Long
Dim rsTEST As DAO.Recordset 'TEST RS TO SEE IF PW PROTECTED DB IS OPEN
If BackupFileFullName <> "" Then 'PW PROTECTED DB WAS PASSED
Set dbs = DBEngine.Workspaces(0).OpenDatabase(BackupFileFull Name, False, False, "MS Access;PWD=loveya")
Else
Set dbs = CurrentDb
End If

'THE FOLLOWING GETS THE LAST RECORD TO VERIFY PW PROTECTED DB WAS OPENED Set rsTEST = dbs.OpenRecordset(strTableName, dbOpenDynaset)
If rsTEST.RecordCount > 0 Then
rsTEST.MoveLast
MsgBox rsTEST("BUILDING_NO")
End If

'Extract connect string
If Len(strTableName) > 0 Then
Set tdf = dbs.TableDefs(strTableName)
Else
For Each tdf In dbs.TableDefs
If IsUserTable(tdf) And Len(tdf.Connect) > 0 Then
If left(tdf.Connect, 4) <> "ODBC" Then
Exit For
End If
End If
Next tdf
End If
strTemp = tdf.Connect 'THIS IS THE PROBLEM!!!!!

'See if the connect string is valid (an error is generated if invalid) On Error Resume Next
Set rst = tdf.OpenRecordset
lngRecordCount = rst.RecordCount

'Parse the connect string to return to calling procedure/object
If Err = 0 Then
If Len(strTemp) = 0 Then
GetConnectString = "<None>"
Else
If blFullString = True Then
GetConnectString = strTemp
Else
GetConnectString = Mid(strTemp, InStr(1, strTemp, ";DATABASE=") + Len
(";DATABASE="))
End If
End If
Else
Err.Clear
GetConnectString = "<Not Pointing to Valid File>"
End If
On Error GoTo 0

'Clean up stuff
Set tdf = Nothing
Set dbs = Nothing

--
Message posted via http://www.accessmonster.com

Try this...

Dim MyConnString as string
MyConnString = DLookup("[Connect]", "MSysObjects", "[Name]='<< Insert
linked table name here... >>' And [Type]=4")

Hope this helps @:=)