Find linked database location

[Guest]

In VBA I need to have a string value with the location of the linked
database. Currently I have this code:

strTableName As String
strTableName = "MyTableName"
strSourcePath = Mid$(CurrentDb.TableDefs(strTableName).Connect, InStr(1,
CurrentDb.TableDefs(strTableName).Connect, "=") + 1)

This works but I would like to remove the need to name a table, with VBA
looking at the table set. All tables are linked to just one database.
Help?

Thanks in advance.

 

[strive4peace]

Hi Angus,

if all tables will be linked to the same thing, you can search the
tables until you find one with a link...

'~~~~~~~~~~~~~~~~~~
Function GetFirstLinkedDB( _
) As String

'return name of first linked database
'crystal
'strive4peace2006 at yahoo dot com

Dim db As DAO.Database _
, tdf As DAO.TableDef

Dim mPos As Integer

Set db = CurrentDb
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 1 Then
mPos = InStr(tdf.Connect, "Database=")
GetFirstLinkedDB = Mid(tdf.Connect, mPos + 9)
GoTo Proc_Exit
End If
Next tdf

Proc_Exit:
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
Exit Function
End Function
'~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*