robert demo via AccessMonster.com wrote:[color=blue]
> 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[/color]
below to[color=blue]
> temporarily check that the backend has been successfully opened by[/color]
showing,[color=blue]
> via a messagebox, the last record in the table. The connection[/color]
string[color=blue]
> should be provided in the last line shown, but I always get strTemp =[/color]
"".[color=blue]
> I'm certain that the backend has been opened because of the[/color]
messagebox.[color=blue]
>
> 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[/color]
added):[color=blue]
>
>
>
> Public Function GetConnectString(Optional strTableName As String,[/color]
Optional[color=blue]
> BackupFileFullName As String, Optional blFullString As Boolean =[/color]
False) As[color=blue]
> 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[/color]
OPEN[color=blue]
>
> If BackupFileFullName <> "" Then 'PW PROTECTED DB WAS PASSED
> Set dbs = DBEngine.Workspaces(0).OpenDatabase(BackupFileFull Name,[/color]
False,[color=blue]
> False, "MS Access;PWD=loveya")
> Else
> Set dbs = CurrentDb
> End If
>
> 'THE FOLLOWING GETS THE LAST RECORD TO VERIFY PW PROTECTED DB WAS[/color]
OPENED[color=blue]
> 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[/color]
invalid)[color=blue]
> 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[/color]
Try this...
Dim MyConnString as string
MyConnString = DLookup("[Connect]", "MSysObjects", "[Name]='<< Insert
linked table name here... >>' And [Type]=4")
Hope this helps @:=)
