Transfer/export tables to another DB

[Miha Abrahamsberg]

Hello!

I'm trying to export some of the linked tables from one MS Access DB to
another MS Access DB.

I use this code (I just want to transfer the structure of the tables and the
tables to be local in the destination DB):

###
DoCmd.TransferDatabase transfertype:=acExport, databasetype:="Microsoft
Access", _
databasename:=dbName, _
objecttype:=acTable, Source:="tblStranka", _
Destination:="tblStranka1", structureonly:=True
###

The result is not what I expect. The table is copied as a linked table
including all the data.

Any suggestions?

Thanks!

Miha

 

[Jeff Boyce]

Miha

Since the tables don't actually exist in the Access DB you are using (you
did say "linked tables", right?), I don't believe you can "copy their
structure" to another DB.

What if you use the actual underlying source, rather than the links?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Miha Abrahamsberg]

Well, the source is MySQL 5. Copying from there I think would be even more
complicated.

In the end I can still choose to go through all the trouble of reading the
structure of existing (linked) tables and creating new ones (tabledefs).

I just thought there must be an easier way...

Miha

 

[Jeff Boyce]

That would probably depend on what you want to accomplish...

If you want a second database (Access) to point to the same data as the
first one, your "links" will work just fine.

If you want a second database that duplicates the tables/table structure of
the underlying data, I wonder what having this duplicate (which will very
shortly be out-of-sync with the original) will allow you to do (i.e., the
underlying business need)?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Miha Abrahamsberg]

Hi!

I have a database with over 50 linked tables (to MySQL) that includes
different data from thousands of clients... The data changes every day
(through our website directly by our clients).

From time to time I have to prepare some sort of simulations/calculations
that are only related to a couple of tables and also just a part of the data
in these tables.

Because the data gets changed every day I want to make "local" copies of
that tables including just the data I need for the calculations. Some sort
of a "snapshot" of the data used for the "Simulation on the 3rd of March
2008 12:24 AM" for example.

I hope now I was more clear.

So, is there any simple (code) way to make a local copy of a linked table
without going through all the hassle of creating new tables /
tabledefinitions...?

Miha

 

[Jeff Boyce]

I guess I'm still not clear on the need to 'take a snapshot'.

Do those MySQL tables include a field that holds the date/time of the
record's creation (*or update*)? If so, a simple query should be able to
get all the data "as of" a particular date/time.

However, if the tables are designed more like spreadsheets than relational
database tables, the data that is being stored might, in fact, be REPLACING
earlier data, making the "as of" view problematic.

If you'll provide a bit more description of the data structure, folks here
may be able to offer you an approach that eliminates the need to 'take a
snapshot'...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Miha Abrahamsberg]

Hello!

I really want to make a snapshot of the state of the database, because of 2
reasons:

1. I want the "snapshot" to be local (not on the network computer that holds
the MySQL database)

2. I would have to track to many changes (updates and deletions) in the
database to "exclude" my need for a snapshot

Miha