Solved

MS Access acLink automation

Posted on 2009-04-08
7
441 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

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.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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