Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 19th, 2005, 05:36 AM
Registered User
Join Date: Mar 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Make Table query : table in Another Database

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 ?
Old March 19th, 2005, 12:39 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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:

Function LinkedTableFile(pstrTableName as string) as Variant
on error goto ReturnNull
LinkedTableFile = mid(currentdb().TableDefs(pstrTableName).Connect,11)
  Exit Function
  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:

Static db as Database
if db is nothing then Set db = CurrentDB()
And change to:
LinkedTableFile = mid(db.TableDefs(pstrTableName).Connect,11)
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Table Query CongoGrey Access 2 June 1st, 2007 06:22 AM
Can a Make Table Query produce a Linked table? kronik Access 5 May 16th, 2006 06:17 AM
Make table Query and Datepart marshaji Access 1 June 21st, 2005 12:36 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM
Error on make table Query rylemer Access 2 June 19th, 2003 10:38 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.