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 GetConnectStrin g(Optional strTableName As String, Optional
BackupFileFullN ame 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 BackupFileFullN ame <> "" Then 'PW PROTECTED DB WAS PASSED
Set dbs = DBEngine.Worksp aces(0).OpenDat abase(BackupFil eFullName, False,
False, "MS Access;PWD=love ya")
Else
Set dbs = CurrentDb
End If

'THE FOLLOWING GETS THE LAST RECORD TO VERIFY PW PROTECTED DB WAS OPENED
Set rsTEST = dbs.OpenRecords et(strTableName , dbOpenDynaset)
If rsTEST.RecordCo unt > 0 Then
rsTEST.MoveLast
MsgBox rsTEST("BUILDIN G_NO")
End If

'Extract connect string
If Len(strTableNam e) > 0 Then
Set tdf = dbs.TableDefs(s trTableName)
Else
For Each tdf In dbs.TableDefs
If IsUserTable(tdf ) And Len(tdf.Connect ) > 0 Then
If left(tdf.Connec t, 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.OpenRecords et
lngRecordCount = rst.RecordCount

'Parse the connect string to return to calling procedure/object
If Err = 0 Then
If Len(strTemp) = 0 Then
GetConnectStrin g = "<None>"
Else
If blFullString = True Then
GetConnectStrin g = strTemp
Else
GetConnectStrin g = Mid(strTemp, InStr(1, strTemp, ";DATABASE= ") + Len
(";DATABASE= "))
End If
End If
Else
Err.Clear
GetConnectStrin g = "<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.c om 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 GetConnectStrin g(Optional strTableName As String, Optional BackupFileFullN ame 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 BackupFileFullN ame <> "" Then 'PW PROTECTED DB WAS PASSED
Set dbs = DBEngine.Worksp aces(0).OpenDat abase(BackupFil eFullName, False, False, "MS Access;PWD=love ya")
Else
Set dbs = CurrentDb
End If

'THE FOLLOWING GETS THE LAST RECORD TO VERIFY PW PROTECTED DB WAS OPENED Set rsTEST = dbs.OpenRecords et(strTableName , dbOpenDynaset)
If rsTEST.RecordCo unt > 0 Then
rsTEST.MoveLast
MsgBox rsTEST("BUILDIN G_NO")
End If

'Extract connect string
If Len(strTableNam e) > 0 Then
Set tdf = dbs.TableDefs(s trTableName)
Else
For Each tdf In dbs.TableDefs
If IsUserTable(tdf ) And Len(tdf.Connect ) > 0 Then
If left(tdf.Connec t, 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.OpenRecords et
lngRecordCount = rst.RecordCount

'Parse the connect string to return to calling procedure/object
If Err = 0 Then
If Len(strTemp) = 0 Then
GetConnectStrin g = "<None>"
Else
If blFullString = True Then
GetConnectStrin g = strTemp
Else
GetConnectStrin g = Mid(strTemp, InStr(1, strTemp, ";DATABASE= ") + Len
(";DATABASE= "))
End If
End If
Else
Err.Clear
GetConnectStrin g = "<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]", "MSysObject s", "[Name]='<< Insert
linked table name here... >>' And [Type]=4")

Hope this helps @:=)