I have an access database used at a Boy Scout camp for tracking scouts progress. Currently it is an access front end with a SQL server back end. What I need is to make a portable version that can be used when there is no network access and then sync up when there is. Does anyone have any suggestions on how to accomplish this? We are talking about a very small amount of data. Maybe 1500 records in 4 tables.
You could create a copy of the database (mdb) on the client. Then two sets of queries, one for updating existing records and one for inserting new records should do the trick.
Testing whether the server is available or not isn't too difficult either. The real problem is how you solve conflicts, i.e. what if a record was changed on both sides (server database and local copy) while the two were not connected.
You would also need timestamps of some kind to determine the date of any modification as well as the date of the last synchronization. One can also imagine an audit trail or log table (or file) that would keep a history of every data modification. This is easy with a SQL Server but can be hard to implement in Access, due to the lack of triggers.
Another solution would consist in installing a "personal" version of SQL Server and have the database replicated on it. The synchronization mechanism would be far simpler.