Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

Post by Sall » Wed, 17 Nov 1999 04:00:00



I have several Access tables, 30 or so, that should be linked to each other.
I have a diagram on paper that shows how the tables link to each other.
When I opened up the Access files to start working on this project, I
discovered they were in separate files.  How is this?  Is there a way to
link a table in one Access file to a table in another Access file?

After the file linking is accomplished, I need to transfer about 30,000 rows
from these tables into one Access table.

Right now I'm using VB6/DAO to query the information and plug it into the
new table one row at a time.  With 30,000 rows, this method will take more
than a few hours.

By the way, the new table will also have different column names.  So the
info will have to get copied to a table with a different column names or
after the table is populated, the column names will have to be changed;
which ever is easiest.

Is this table linking possible and could someone suggest a method to speed
up this process?

Thanks
Sally

 
 
 

Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

Post by Eric Larse » Thu, 18 Nov 1999 04:00:00


Hi Sally,

I'm not sure I understand your question but I'll give it shot.  If you open
a brand new mdb file you can go to the File tab on the tool bar.  One of the
option is "Get External Data."   A sub menu pops up with two options --
Import , Link Tables.  You want to select Link Tables.  At that point you
given a file dialog that allows you navigate the directories you need to
select the mdb's you want to link to.  Once you select the mdb, a dialog box
will come up which will show you which tables you want to link to your new
data base.  You select the tables you want to link to and press OK.

When you have all your tables linked in your new data base, you can select
the query tab and write a Make Table query for the new table you want to
create.  Another way is to create the table and field definitions and then
write an Append query.  Either way will work.

Good Luck.
Eric


Quote:> I have several Access tables, 30 or so, that should be linked to each
other.
> I have a diagram on paper that shows how the tables link to each other.
> When I opened up the Access files to start working on this project, I
> discovered they were in separate files.  How is this?  Is there a way to
> link a table in one Access file to a table in another Access file?

> After the file linking is accomplished, I need to transfer about 30,000
rows
> from these tables into one Access table.

> Right now I'm using VB6/DAO to query the information and plug it into the
> new table one row at a time.  With 30,000 rows, this method will take more
> than a few hours.

> By the way, the new table will also have different column names.  So the
> info will have to get copied to a table with a different column names or
> after the table is populated, the column names will have to be changed;
> which ever is easiest.

> Is this table linking possible and could someone suggest a method to speed
> up this process?

> Thanks
> Sally


 
 
 

1. Strange ODBC errors using DAO to access linked tables in Access .MDB

We have an MFC/DAO application which is encountering all sorts of trouble
accessing an .MDB file which links tables via an ODBC data source (SQL
Server 6.5).

The same tables and queries seem to work fine if run directly from Access
via the same MDB file, so I believe that the tables and links are all set
up correctly. The original .MDB file (prior to upsizing to SQLServer and
then linking the tables) also works great in both places.

Turning on ODBC tracing indicates that DAO/Jet is supplying an invalid
window handle to ODBC on the SQLDriverConnect call, but this happens only
intermittently and with only some of the queries. Has anyone seen anything
like this? Is there a workaround?

I'd be interesting in hearing from anyone who has successfully used the MFC
CDao classes to access ODBC data sources to any significant extent. VB's
VisData sample can access the .MDB file with the linked tables
successfully, but Stingray's DAO grid sample has problems accessing it.
This leads me to suspect the MFC/DAO interface, but I don't have enough
data yet.

Thanks
--
Randy Baker (remove Z from address in email replies)

2. Comparison between Oracle and Ingres ?

3. Unable to Append Link table from MDB File to MDB file

4. failover failure

5. Attach a link to an oracle table to an access mdb

6. SQL connection ConnectionOpen(CreateFile))

7. MDAC 2.1 and linked tables from access 2.0 in access97.mdb failed

8. abf imageapp error

9. ASP problems with linked SQL tables in an Access mdb

10. Linked tables in Access .mdb

11. * Backup SQL Server Tables to Access *.MDB file