Exporting & Importing Tables

[gavin31]

A current conundrum I have is with an existing Access database that is used in our office, with a local version in use in a satellite office. The satellite office does not have access to the network in the head office.

A requirement has become apparent to merge/import the data tables from the satellite office into the database of the head office on a weekly basis.

I had thought that, say, every Friday afternoon, an engineer in the satellite office would run a macro/function to export the relevant tables and email them to the head office, where another engineer would merge the receieved tables into the master copy of the database.

My questions are:-
1. What is the best format to send the tables in (considering size, time to send, limitations of email attachments etc.) - CSV, XML or just all the tables in an mdb file?

2. It's not something I have ever used, but could Data Access Pages be effectively used for this export/import procedure?

3. Is there a merge tables "procedure" in Access that would highlight any clashes with duplicate records?

Any thoughts/ideas/advice on these or related points would be much appreciated.

Gavin

 

[hwkranger]

You my friend, are undertaking a bit of a task.

I would never be so bold as to suggest what you are attempting to anyone, it's just not a fun task.

With that being said -- Should you have your schema based on strong entities with identifying relationships, you could probably achieve this in any of the suggested ways you have mentioned.

If this is the case, using a CSV file is simple enough. Then use a insert statement, limited to only new records.

Else, I suggest getting a MSSQL server to which both offices have access.

P.S. You could use a replicated database, but -- that is just as nasty.


Randall Vollen
Merrill Lynch