Solved

MS Access acLink automation

Posted on 2009-04-08
7
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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