MS Access应用程序脱机工作 [英] MS Access Application Work offline

问题描述

我有一个通过链接表链接到SQL Server(2012)的MS Access(2010)应用程序.有什么方法可以让用户脱机工作并在连接后同步到服务器?

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?

预先感谢

推荐答案

要做的方法是将表链接到2个DB上的表:SQL Server和本地副本Access DB文件.

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. 您将日期从远程复制到本地数据库
2. 让用户处理本地副本
3. 将数据同步回远程数据库

如果您使用相同的名称保留本地表，并在指向远程表名称的链接("Remote"或"rmt")中添加一些前缀，则可以保持大多数逻辑:您仍在处理相同的表，只是链接了到另一个位置. 您的主要问题仍然保持同步.您需要考虑一种方法，具体取决于此系统中的数据流.
在这种情况下，一般的同步代码为:

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

此代码从远程数据库中删除所有数据，而从本地数据库中插入数据.查看这是否是您需要的同步方法，或者应该修改代码以适合您的需求.在每个表的每个记录中添加一个LastUpdate字段(并在每次数据修改时对其进行更新)，可以支持良好的同步决策:哪些记录要删除 ，哪些记录要 Insert ，并在上述语句中添加适当的WHERE子句.
您还可以使用常规UPDATE SQL 假设每个表的主键都被命名为表名，并带有"ID"前缀:

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

这篇关于MS Access应用程序脱机工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！