Solved

MS Access acLink automation

Posted on 2009-04-08
7
442 Views
Last Modified: 2013-11-27
I have a database where i need to link ALL tables from two other databases.
what I want to do is provide the user with open file dialog, have them select the mdb then link all tables.

Where I have a question is how can I get a list of all the tables in the mdb selected so I can loop through them with TransferDatabase acLink?

0
Comment
Question by:keschuster
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 8

Expert Comment

by:Joe Overman
ID: 24099247
use the TableDefs collection

For a = 1 To CurrentDb.TableDefs.Count
    CurrentDb.TableDefs(a).Name
Next a
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 24099269
Conceptually, you will need to open the external database, then loop through the TableDefs collection to get the list of tables.

mx
0
 

Author Comment

by:keschuster
ID: 24099279
Isn't this for the table I'm linking FROM

I need to get the tables from what I'm linking TO.  So basically I asked the user to give me the name of the table.  Now that I know the name of the mdb - what are the tables in it
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:keschuster
ID: 24099284
DatabaseMX - how would I do that with VBA?
0
 
LVL 8

Assisted Solution

by:Joe Overman
Joe Overman earned 100 total points
ID: 24099319
MX is right,
You will have to open the database you are trying to link to and loop through its TableDefs collection using code.  As you loop through you can link the tables.
0
 
LVL 3

Accepted Solution

by:
Dirk Eberhardt earned 300 total points
ID: 24099350
You can use

    Dim dbBackend As Database
    Set dbBackend = DBEngine(0).OpenDatabase("YourPath")

an then

    For i = 0 To dbBackend.TableDefs.Count - 1
        Debug.Print dbBackend.TableDefs(i).Name
    Next
0
 
LVL 75
ID: 24099378
There you go ...

mx
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question