How to add data to sql server using MS access front end, when user will not always have sql connection

Question

I have a SQL Server backend and an Access Front end database which is working fine.

However, I now have a user that needs to use this database (on a tablet) for some equipment checks out in the production plant that does not have a wifi connection and of course he gets an error since he cannot connect to the SQL server.

What is the best way to handle this? I've never run into this before. (My other databases are used by Office staff only) The user does need to have some information that is on the SQL server for drop down selections, etc.

Do I create a local copy on the tablet and then have him press a "submit" button to commit the record to the server (append query) when he is back in the office with wifi range?

I'll run into issues if others are entering data that is using autonum, etc. Also, any changes made to the dropdowns by office staff would not be updated to him if it is a local copy. Any help would be appreciated.

Answer 1

This is the typical scenario for replication. You could set up a second copy of the SQLServer database on his machine (remember that SQLExpress has limitations on the size of the database - 10 GB - and does not have SQLAgent) using merge replication.

The SQL Server instance you are using now could act as a publisher and the laptop could act as a subscriber. The Access frontend should point to the instance of SQL Server on the laptop instead of pointing to the publisher. When the user gest out of the wifi range, he would still be able to work using the local SQL Server instance. When he gets back to office, the subscriber could sync with the publisher.

Since SQLExpress does not have a task scheduler (no SQL Agent), you would have to use the Windows task scheduler for running the merge agent. In case you need very frequent sync, you could use the continuous switch in the merge agent.

Keep in mind that merge publishing a database is not totally painless: all published tables (articles) will need to have a uniqueidentifier UNIQUE column decorated with the rowguidcol flag. If you don't have such column, it will be added automatically, which will likely break all the insert statements that don't specify the column list. Moreover, all articles will get triggers for insert/update/delete operations and your workload might suffer a noticeable slowdown during write operations, depending on the shape of the workload itself.