path of related tables in VBA

[lucainbici]

Hello, I'm writing in English but I'm Italian: forgive my language errors ...
I'm burning my brain. I'm trying to write some rows of code in a form of the
front-end database, to get the path of a related table in the back-end
database, and put it into a string on a msg box. Someone can help me?
I'm at this point:
---
Dim Tabella As TableDef
Dim Connessione As String
Tabella = Agencies ' Agencies is a related table in the back-end access
database
Connessione = Tabella.SourceTableName
MsgBox "You're working on" & Connessione, vbOKOnly, "Connection"

 

[Dirk Goldgar]

Hello, I'm writing in English but I'm Italian: forgive my language errors
...
I'm burning my brain. I'm trying to write some rows of code in a form of
the
front-end database, to get the path of a related table in the back-end
database, and put it into a string on a msg box. Someone can help me?
I'm at this point:
---
Dim Tabella As TableDef
Dim Connessione As String
Tabella = Agencies ' Agencies is a related table in the back-end access
database
Connessione = Tabella.SourceTableName
MsgBox "You're working on" & Connessione, vbOKOnly, "Connection"

Try this:

Dim db As DAO.Database
Dim Tabella As DAO.TableDef
Dim Connessione As String

Set db = CurrentDb
Set Tabella = db.TableDefs("Agencies")
Connessione = Mid(Tabella.Connect, 11)
MsgBox "You're working on " & Connessione, vbOKOnly, "Connection"

That could be simplified to one statement:

MsgBox _
"You're working on " & _
Mid(CurrentDb.TableDefs("Agencies").Connect, 11), _
vbOKOnly, "Connection"

 

[Tom van Stiphout]

On Tue, 10 Nov 2009 19:27:38 -0800, lucainbici

Your English is better than my Italian

The reason you're getting an error on line 3 is that you are trying to
assign a string to a tabledef object. The proper way to do that is:
set td = currentdb.Tabledefs("Agencies")

Perhaps this sample code will help:
dim td as dao.tabledef
for each td in currentdb.tabledefs
debug.print td.name, td.connect
next

Ciao,

-Tom.
Microsoft Access MVP