MS Access Application Work offline

Question

I have an MS Access (2010) application linked to SQL Server (2012) via linked tables. Is there any way that I can have the users work offline and sync to the server when connected?

Thanks in advance

Answer 1

The method to do it is having linked tables to tables on 2 DBs: to the SQL server, and to a local copy Access DB file.

1. You copy date from remote to local DB
2. Have the user work on local copy
3. Synchronize data back to remote DB

If you keep the local tables with the same names, and add some prefix to links to remote table names ("Remote" or "rmt"), You can keep most of you logic: You still process same tables, just linked to a different location. Your main issue remains synchronization. You need to think of a method, depending on data flow in this system.
In this case, general Sync Code would be:

For Each tdf In CurrentDb.TableDefs  
    If Left (tdf.name, 3) = "rmt" then   
        CurrentDB.Execuet "DELETE FROM " & tdf.name 
        CurrentDB.Execuet "INSERT INTO " & tdf.name & " SELECT * FROM " & Mid (tdf.name, 4)
    End If
Next tdf

This code deletes all data from remote DB, and inserts data from Local DB instead. See if this is your synchronization method you need, or you should modify the code to suit your needs. Adding a LastUpdate field to each record in each table (and having it updated at each data modification), could support good synchronization decision making: What records to Delete, and what to Insert, and add the appropriate WHERE clause to the above statements.
You could also have a General UPDATE SQL Assuming Primary Key of each table is named as the table name, with "ID" prefix:

Dim strSQL As String, srsbl As String, PK As String
Dim tdf As DAO.TableDef, fld As DAO.Field
For Each tdf In CurrentDb.TableDefs  
    if Left (tdf.name, 3) = "rmt" then  
        srsTbl =  Mid (tdf.name, 4) 
        PK = "ID" & srsTbl 
        strSQL = "UPDATE " & tdf.name & " Inner Join " & srsTbl & " ON " &  tdf.name & "." & PK & " = " & srsTbl & "." & PK & " SET "
        For Each fld in tdf.Fields
            if fld.Name <> PK then
                strSQL = strSQL & tdf.name & "." & fld.Name & " = " & srsTbl & "." & fld.Name & ", "                    
            End If
        Next 
        ' Remove Last comma:
        strSQL = Left (strSQL, Len(strSQL) - Len(","))
        ' strSQL = strSQL & " WHERE "...
    End If
Next tdf

This was the technical part. The main issue here is Synchronization method. Search on Data-Base Concurrency control