Question Transferring Data from one Access DB to another (1 Viewer)

I have built a database application which is deployed to a number of remote users. About three times a year each of the users must export their new records to a central database.

In my current arrangement I have a DataStatus Field that is used as criteria so the export queries know which records are new. I transfer two related tables to sheets in an Excel workbook then run an update query to stamp them as exported. But I have run into big headaches exporting to Excel just to import back into a nearly identical Access DB, mainly due to data type conversion issues. A colleague suggested I just stay in Access format.

So I have been trying to develop a means of producing a small subset of my database that included the two tables new records only while still being able to update the Datastatus after export.

But, I am running into limitations trying to export from Access to a small portable DB such as not being able to export relationships. And trying to build a small Export DB and import the data from there gets too complicated (the way I have been experimenting) for my users.

Perhaps I should just export the tables to a blank DB and not worry about relationships. I would greatly appreciate some conceptual feedback on how best to go about building a simple process for exporting records incrementally.

I assume that you are on a LAN. If I understood you correctly then you may try the following as a solution to your problem:

1. Create a Database with all the table Structures involved for establishing the Relationships between them.
   
2. If you import the tables from an existing database then you can import the Relationship definitions also along with the tables. Put a checkmark in the Relationship Option in Options menu of the Import Dialog Box.
   
3. You can simply create Append Queries (with appropriate criteria settings to identify fresh data for appending to the backup database) in the distributed copies of the Database for directly appending data into the backup database. Not necessary to link the tables.
   
4. Control the frequency of running the Query through VBA.
   
5. A sample append Query SQL is given below, that appends data into the external database directly into the target Table2 (with the same structure of the original table):

Check the IN Clause ( IN 'C:\mdbs\dbBackup.mdb') in SQL that sends the data directly into an external database. You can use UNC type addressing (IN '\\ServerName\FolderName\SubFolder\dbBackup.mdb') for LAN.