Relink Tables From Multiple Sources?

[EricG]

I have a "database manager" that links to three different back end files.
Each back end has the same tables in it (meaning tables with the same name
and same fields - they can have different data). I want to programmatically
relink these tables, and have successfully used the code at this link:

http://www.mvps.org/access/tables/tbl0009.htm

However, there is one glitch. Since I link to three back ends with the same
table names, the "database manager" uses aliased names to tell them apart.
For example, let's say each back end has a table named "table1". In the
"database manager", I have links to "table1_file1", "table1_file2" and
"table1_file3". The problem comes when I try to relink one of these tables -
the code complains that it can't find "table1_file1" in the "file1.mdb" back
end file. That makes sense, because the table is actually named "table1" in
"file1.mdb".

I'm sure this is clear as mud, but is there a way to get the actual table
name for a linked table, rather than the alias? Instead of ".Name", is there
a ".RealName" or something like that?

Thanks in advance,

Eric
Access Noob

 

[Marshall Barton]

I have a "database manager" that links to three different back end files.
Each back end has the same tables in it (meaning tables with the same name
and same fields - they can have different data). I want to programmatically
relink these tables, and have successfully used the code at this link:

http://www.mvps.org/access/tables/tbl0009.htm

However, there is one glitch. Since I link to three back ends with the same
table names, the "database manager" uses aliased names to tell them apart.
For example, let's say each back end has a table named "table1". In the
"database manager", I have links to "table1_file1", "table1_file2" and
"table1_file3". The problem comes when I try to relink one of these tables -
the code complains that it can't find "table1_file1" in the "file1.mdb" back
end file. That makes sense, because the table is actually named "table1" in
"file1.mdb".

I'm sure this is clear as mud, but is there a way to get the actual table
name for a linked table, rather than the alias? Instead of ".Name", is there
a ".RealName" or something like that?

You can use the SourceTableName property.

OTOH, since you have a rigorous "alias" naming convention,
you can parse out the back end's table name by using:
Left(tdf.Name, Instr(tdf.Name, "_") - 1)

 

[EricG]

That's it! I just couldn't find it. Thanks for the input. My relink button
works great now. Every now and then I have to work on a non-production
version of these databases to test new code and fields, and relinking
everything was a manual pain in the $#!##. Now it's a one click operation.

Thanks,

Eric