Ok, here's what I am trying to do:
I have one mdb with lots of tables, forms, reports, VBA, and macros. Two people in two cities will each get a copy; each will populate data. At some point, User2 will export data and send it to User1 for import into User1's copy. Likewise, User1 may send a dataset to User2. I want this to happen as automatically as possible (as we all know, things go awry when users get in the loop).
Here's what I have so far:
I've created an import/export form that provides a checkbox-list of the tables that can be exported/imported, a text field with a "browser" for selecting the import/export folder-file path, and two buttons..you guessed it: Export and Import. Behind each button I have appropirate VBA/macros to run a variety of code: Export dumps to another mdb file and even opens an email and attaches the new mdb so the user simply fills in the email address and hits the Send button. The Import button has logic to handle "missing" table errors and execute AppendQuerries for each tabel in the received mdb. All seems to work pretty well, thus far.
Here's my question:
It is quite possible for both User1 and User2 to create a data record using the same key field. What I'd like to do is update any records already in existence, and append any records not currently in the import-target table. How do I do this? As I mentioned, i have AppendQueries for the append part - they work quite well, but the tables are "no duplicate", so records in the importing table are not appended if they already exist in the target table. This is fine, but I'd like to somehow take this record data and update the current record to include the "new" data, if this makes sense. Also, it is possible that the two users will create two records for the same primary key with data that is completely different, except for the primary key. is there a way to check for this and then change on of the primary keys to be something else (I already have code that creates an a formatted autonumber)? If this is possible, with this damage links between the table being modified and a linked table, which uses the primary key as the link?
I know this is a lot to throw out without much ex-planation, but any advice on this general topic would be most appreciated. I've described my approach, but am not sure it is the most efficient or foolproof (and we all know there are too many fools out there to be completely foolproof, right). I am somewhat familiar with database synchronization, but a) User1 and User2 will never be connected directly, and b) the databases are likely too large in total to transfer in entirety, so a selected set of table exports is all I'm trying to transfer back and forth.
Any thoughts?
Thanks, in advance,
Tom
I have one mdb with lots of tables, forms, reports, VBA, and macros. Two people in two cities will each get a copy; each will populate data. At some point, User2 will export data and send it to User1 for import into User1's copy. Likewise, User1 may send a dataset to User2. I want this to happen as automatically as possible (as we all know, things go awry when users get in the loop).
Here's what I have so far:
I've created an import/export form that provides a checkbox-list of the tables that can be exported/imported, a text field with a "browser" for selecting the import/export folder-file path, and two buttons..you guessed it: Export and Import. Behind each button I have appropirate VBA/macros to run a variety of code: Export dumps to another mdb file and even opens an email and attaches the new mdb so the user simply fills in the email address and hits the Send button. The Import button has logic to handle "missing" table errors and execute AppendQuerries for each tabel in the received mdb. All seems to work pretty well, thus far.
Here's my question:
It is quite possible for both User1 and User2 to create a data record using the same key field. What I'd like to do is update any records already in existence, and append any records not currently in the import-target table. How do I do this? As I mentioned, i have AppendQueries for the append part - they work quite well, but the tables are "no duplicate", so records in the importing table are not appended if they already exist in the target table. This is fine, but I'd like to somehow take this record data and update the current record to include the "new" data, if this makes sense. Also, it is possible that the two users will create two records for the same primary key with data that is completely different, except for the primary key. is there a way to check for this and then change on of the primary keys to be something else (I already have code that creates an a formatted autonumber)? If this is possible, with this damage links between the table being modified and a linked table, which uses the primary key as the link?
I know this is a lot to throw out without much ex-planation, but any advice on this general topic would be most appreciated. I've described my approach, but am not sure it is the most efficient or foolproof (and we all know there are too many fools out there to be completely foolproof, right). I am somewhat familiar with database synchronization, but a) User1 and User2 will never be connected directly, and b) the databases are likely too large in total to transfer in entirety, so a selected set of table exports is all I'm trying to transfer back and forth.
Any thoughts?
Thanks, in advance,
Tom
