Aug 12 2004, 05:44 AM
I would like to use MS Excel as an offline front end tool to modify data that has been exported from an MS Access 2002 database. This data has to be re-synchronized later with the database because there are several users that may modify the same data offline.
Simply importing the modified data into Access won't do , because existing records get changed offline in Excel and I don't want to have duplicates in the database. Furthermore, I need to detect synchronization conflicts (i.e. someone wants to update a record that has already updated by some else).
I welcome any ideas how to approach this task. Are there maybe tools or Access Add-Ons available?
Aug 12 2004, 06:46 AM
Welcome to UA
Instead of Importing the data into Access, Link the Data
File --> Get external data --> Link Tables.
Aug 13 2004, 04:45 AM
Linking the XLS table works fine (thank you for the hint) as long as I am the only user. But how can I synchronize offline modifications that are made by several people to the same data?
Aug 13 2004, 08:31 AM
I'm not entirely sure what you mean. If multiple people make changes to the SS, then the latest change will be written to the DB Table. Do you mean that you want to keep a record of all changes?
Aug 13 2004, 10:08 AM
I'd like to give each user their own copy of the spreadsheet for offline modifications (let's say they copy the spreadsheet on their laptop and update data there). If I let a user copy his modified spreadsheet back to the one that is linked to the database, all changes made in the meantime by other users are lost.
I am looking for a way to
1. update only those records in the database, that the user has changed in his offline spreadsheet
2. detect a conflict when a user tries to update a record that has already been updated by someone else in the meantime
Aug 13 2004, 11:04 AM
Hi Patrick. I'm afraid I can't answer your question. I've posted elsewhere to see if anyone else can lend a hand. Sorry
Aug 13 2004, 11:47 AM
You could set a date modified field on the file for excel, I'm sure excel has a way to update this via vba. Then you can just import/update the records that user has modified since you last gave them records. There would of course be issues if 2 users can change the same records. This will just be a mess. Thats just my idea though, I've never had to do anything like this in my work.
Aug 14 2004, 08:08 AM
Welcome to UtterAccess!
I would not attempt this with an Excel front end. Your best bet is to stick with an Access FE/BE and replication. It will handle precisely what you're asking for.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here