Export & Import Tables

[Guest]

I have a simple three table DB which will be given to three offshore
locations. They will use the DB and then need to export the information from
their side to me so i can consolidate thier data into my copy here. The DB
has the same structure,table names etc and all i need is to have a method on
a regular basis to import the data. Is it efficient or logical to create an
append query for each one of these three tables to update my side? Also,
users are not technically savy so would export ing tables be an easy option
for end users?

 

[John Nurick]

Hi JK,

Perhaps the simplest approach would be to get the each remote users to
put a copy of their database into a zip file (compressed folder in
Windows XP) and email it to you.

If you have split the database (which is worth doing for other reasons)
all they have to send is the back end .mdb file.

At your end, you may need more than just the three append queries. What
do you want to happen if the remote users edit or delete existing
records?

 

[Guest]

Thanks John. This is very simple and basic since the DB is for entering their
data through a form activated through a very user friendly switchboard then
at the end of the week, send me the file containing the entries.Should there
be any deletes, updates,changes on their side, i will do it remotely through
internet based webex. On the switchboard there is an option of export as
excell and that would be easy but then on my side i would have to do the
import for each location in my copy of this DB which would be time consuming

 

[John Nurick]

Well, I've told you what I think is the simplest way. For something more
complicated,

1) Install a blank (empty) mdb file in a fixed location on the users'
computers - e.g. the same folder as the database proper. Let's call it
Template.mdb

Then write VBA code to do the following:

2) If there's a file called Transfer.mdb, delete it

3) Make a copy of Template.mdb called (say) Transfer.mdb

4) Use DoCmd.TransferDatabase to export the three tables to Transfer.mdb

5) Email Transfer.mdb to you.

(There are many alternatives. 1,2 and 3 can be replaced by using VBA
code to create the new blank database each time. 5 can be avoided by
telling the user to email the file to you.)