Import/Export Tables' Data in Code

[sandra45]

Hi, I have two places (A and B) that will use the same database (same application).

On a regular basis, let's say place B will send data from all their tables they have at that time to place A for consolidation purposes into A's database. How to do this in VBA code?

I want to create a form for place B with a command button to do the "export" data function in Access. When the command button is clicked, then data in all tables will be exported, let's say into filename.txt. Then place B will e-mail filename.txt to place A for consolidation.

The same for place A, there is a form with a command button to import all data from filename.txt into the same field names under same table names of the database. I reckon that primary key (if using autonumber) might create problem due to duplication in this case.

Can anyone help me in this case? Or probably suggest a simpler solution? Thanks.

Regards,
Sandra

 

[KenReay]

Hi

This sounds like Replication, read up on Replication in Help, or buy yourself a good Access Book (Access Develoeprs Handbook, but there are others).

If you want to write your own routines see DoCmd.CopyObject, DoCmd.Transfer??? in its various guises, that may well do what you want, alternatively you could do this with SQL make Table Queries

To overcome the problem of Autonumbers duplicating see the alternative GUID type (Global Unique IDentifier) type

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sandra45]

Hi Ken, thanks. I read about CopyObject and TransferDatabase, I understand that CopyObject will make a new object, e.g. table1 to table2 (with the same data and structure) while TransferDatabase will transfer for instance, table Employee to table StaffMarketing, at the time of importing, if Access finds there's already table Employee in the database, then Access will create table Employee1. In this case, I don't think these are what I'm looking for.

I need the function where place A will still keep its data AND consolidate overall data from place B. It means place A will store A's overall data PLUS B's overall data. Is replication the answer for this? Can we synchronise the data (let's say every month) via internet connection? If yes, this will be very helpful instead of asking place B to copy their data and send it by e-mail? Thanks.

Regards,
Sandra