Make Table query : table in Another Database

marnik · March 19th, 2005, 05:36 AM

I am trying o run a Make-Table query from a mydatabase1.mdb
and create the new table "tblNew" in mydatabase2.mdb .
Is it possible to obtain the File Name eg c:\mydatabase2.mdb
from a table which is in mydatabase2.mdb ?
Thanks

rjweers · March 19th, 2005, 12:39 PM

Well, if you are linked to a table in the second DB you could use the "Connect" property of that linked table. Something like:

mid(currentdb().TableDefs("linkedtablename").Conne ct,11)

That syntax doesn't work in a query. So you can write a function to return the value:

Code:

Function LinkedTableFile(pstrTableName as string) as Variant
on error goto ReturnNull
LinkedTableFile = mid(currentdb().TableDefs(pstrTableName).Connect,11)
Exit_Here:
  Exit Function
ReturnNull:
  LinkedTableFile = Null
  resume Exit_Here
End Function

Beware, there is a lot of overhead created using "CurrentDB()". So using this function in a field in a query with lots of rows could affect your performance.

You can improve your performance for lots of rows by adding:

Code:

Static db as Database
if db is nothing then Set db = CurrentDB()

And change to:

Code:

LinkedTableFile = mid(db.TableDefs(pstrTableName).Connect,11)

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org