数据库同步 - MS Access
我目前遇到一个问题，即在笔记本电脑上使用多个（相同架构）访问 2003 数据库。
我需要找到一种自动化方法将数据同步到中央访问数据库中。
笔记本电脑上的数据仅被附加，因此更新/删除操作不会成为问题。
哪些工具可以让我轻松地做到这一点？ 哪些因素会影响最佳工具或解决方案的决策？
I have an issue at the moment where multiple (same schema) access 2003 databases are used on laptops.
I need to find an automated way to synchronize the data into a central access database.
Data on the laptops is only appended to so update/delete operations wont be an issue.
Which tools will allow me to do this easily?
What factors will affect the decision on the best tool or solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
可以使用 Access 中内置的 Jet 复制，但我会警告您，它非常不稳定。它还会弄乱你在任何表上执行的 PK，因为它会选择随机有符号整数来尝试避免密钥冲突，因此你最终可能会得到 -1243482392912 作为给定记录上的下一个 PK。如果您要对其进行任何类型的查找（例如客户 ID、订单号等），那么这是一个需要输入的 PITA。您无法自动执行 Access 同步（也许您可以使用 VBA 来伪造类似的内容。但仍然如此） ，仅在打开数据库时运行）。
我建议的方法是在“中央”数据库上使用 SQL Server 2005/2008，并使用 SQL Server Express Edition 作为“远程”数据库的后端，然后使用 Access 中的链接表连接到这些数据库SSEE 数据库和复制以同步它们。 设置合并复制或快照复制，您的“中央”数据库作为发布者，您的 SSEE 数据库作为订阅者。与 Access Jet 复制不同，您可以控制 PK 编号，但对您来说，这不会成为问题，因为您的订阅者不会推送更改。
除了 SQL Server 带来的可扩展性之外，您还可以使用 Windows 同步管理器自动执行此操作（如果您有同步文件夹，那就是在您登录/注销时弹出并同步它们的烦人的小框），并将其设置为它以给定的时间间隔、启动、关闭或一天中的某个时间和/或计算机空闲时进行同步，或仅按需同步。即使 Access 一个月没有运行，每次用户连接到网络时，其数据集都可以更新。非常酷的东西。
It is possible to use the Jet replication built into Access, but I will warn you, it is quite flaky. It will also mess up your PK on whatever tables you do it on because it picks random signed integers to try and avoid key collisions, so you might end up with -1243482392912 as your next PK on a given record. That's a PITA to type in if you're doing any kind of lookup on it (like a customer ID, order number, etc.) You can't automate Access synchronization (maybe you can fake something like it by using VBA. but still, that will only be run when the database is opened).
The way I would recommend is to use SQL Server 2005/2008 on your "central" database and use SQL Server Express Editions as the back-end on your "remote" databases, then use linked tables in Access to connect to these SSEE databases and replication to sync them. Set up either merge replication or snapshot replication with your "central" database as the publisher and your SSEE databases as subscribers. Unlike Access Jet replication, you can control the PK numbering but for you, this won't be an issue as your subscribers will not be pushing changes.
Besides the scalability that SQL server would bring, you can also automate this using the Windows Synchronization manager (if you have synchronized folders, that's the annoying little box that pops up and syncs them when you logon/logoff), and set it up so that it synchronizes at a given interval, on startup, shutdown, or at a time of day, and/or when computer is idle, or only synchronizes on demand. Even if Access isn't run for a month, its data set can be updated every time your users connect to the network. Very cool stuff.
访问复制可能很尴尬，并且由于您只需要附加查询并进行一些检查，因此最好自己编写一些东西。如果每台笔记本电脑收集的数据不能重叠，这可能不会太困难。
您将需要考虑主键。最好将用户或笔记本电脑名称合并到密钥中，以确保记录正确关联。
Access Replication can be awkward, and as you only require append queries with some checking, it would probably be best to write something yourself. If the data collected by each laptop cannot overlap, this may not be too difficult.
You will need to consider the primary keys. It may be best to incorporate the user or laptop name in the key to ensure that records relate correctly.
此线程中的答案充满了关于 Jet Replication 的错误信息，这些人显然没有使用过 Jet Replication，只是重复他们所听到的内容，或者将问题归咎于 Jet Replication，而实际上反映了应用程序设计错误。
Jet Replication 并不脆弱。如果使用得当，它是完全可靠的，就像任何其他复杂的工具一样。确实，某些在非复制数据库中不会引起问题的事情在复制时可能会导致问题，但这是合理的，因为任何数据库引擎进行复制的性质决定了这一点。
代理自动编号 PK 从一开始就不应该暴露给用户。它们是用于在幕后连接记录的毫无意义的数字，如果您将它们暴露给用户，那么这就是您的应用程序设计中的错误。
如果您确实需要序列号，则必须自行设计并处理如何防止副本之间发生冲突的问题。但这对于任何数据库引擎中的复制来说都是一个问题。 SQL Server 提供了在数据库引擎级别为各个副本分配序列号块的功能，这是一个非常好的功能，但它的代价是维护多个 SQL Server 实例增加了管理开销（以及所有安全和性能问题）这需要）。在 Jet Replication 中，您必须在代码中执行此操作，但这并不是一个复杂的问题。
另一种替代方法是使用复合 PK，其中一列表示源副本。
但这并不是 Jet 复制实现中的缺陷——对于任何需要有意义的序列号的复制场景来说，这都是一个问题。
这显然是不真实的。如果安装 Jet 同步器，您可以安排同步（直接、间接或 Internet 同步）。即使没有它，您也可以安排 VBScript 定期运行并进行同步。这些只是无需打开 Access 应用程序即可实现自动 Jet 同步的两种方法。
引用 MS 文档：
JRO 实际上并不是管理 Jet Replication 的最佳方法。其一，它只有一项 DAO 本身所缺乏的功能，即在代码中启动间接同步的能力。但是，如果您要向应用程序添加依赖项（JRO 需要引用，或者可以通过后期绑定使用），您不妨添加对用于控制 Jet Replication 的真正有用的库的依赖项，这就是 TSI Synchronizer，由 Michael Kaplan 创建，曾是世界上最重要的专家Jet Replication（此后他将国际化作为他的关注领域）。它使您能够以完全编程方式控制 Jet 公开的几乎所有复制功能，包括安排同步、启动各种同步以及急需的 MoveReplica 命令（在不中断复制的情况下移动或重命名副本的唯一合法方法）。
JRO 是 Microsoft 已流产的 ADO-Everywhere 活动中丑陋的继子之一。其目的是提供 Jet 特定的功能来补充 ADO 本身支持的功能。如果您不使用 ADO（并且您不应该使用具有 Jet 后端的 Access 应用程序），那么您实际上并不想使用 JRO。正如我上面所说，它只添加了 DAO 中尚不可用的一项功能（即启动间接同步）。我不禁认为微软是怀有恶意，为 Jet 特定的功能创建了一个独立的库，然后故意遗漏了所有他们本来可以支持的非常有用的功能。
现在我已经处理了上面提供的答案中的错误断言，这是我的建议：
因为您有一个仅附加基础设施，所以请按照@Remou的建议进行操作，并设置一些内容来手动将新记录发送到任何需要的地方。他是对的，您仍然需要处理 PK 问题，就像使用 Jet Replication 时一样。这是因为这是在多个位置添加新记录的要求所必需的，并且对于所有复制/同步应用程序来说都是常见的。
但需要注意的是：如果将来只添加场景发生变化，您将被淹没并且必须从头开始或编写一大堆繁琐的代码来管理删除和更新（这并不容易 - 相信我，我已经做到了！）。仅使用 Jet Replication 的优点之一（尽管它对于双向同步（即在多个位置进行编辑）最有价值）是，它可以毫无问题地处理仅添加场景，然后轻松处理完全合并复制（如果需要）未来的要求。
最后，Jet Replication Wiki 是开始使用 Jet Replication 的好地方。资源、最佳实践和难以置信的事情页面可能是最好的起点。
The answers in this thread are filled with misinformation about Jet Replication from people who obviously haven't used it and are just repeating things they've heard, or are attributing problems to Jet Replication that actually reflect application design errors.
Jet Replication is not flakey. It is perfectly reliable when used properly, just like any other complex tool. It is true that certain things that cause no problems in a non-replicated database can lead to issues when replicated, but that stands to reason because of the nature of what replication by any database engine entails.
Surrogate Autonumber PKs should never be exposed to users in the first place. They are meaningless numbers used for joining records behind the scenes, and if you're exposing them to users IT'S AN ERROR IN YOUR APPLICATION DESIGN.
If you do need sequence numbers, you'll have to roll your own and deal with the issue of how to prevent collisions between your replicas. But that's an issue for replication in any database engine. SQL Server offers the capability of allocating blocks of sequence numbers for individual replicas at the database engine level and that's a really nice feature, but it comes at the cost of increased administrative overhead from maintaining multiple SQL Server instances (with all the security and performance issues that entails). In Jet Replication, you'd have to do this in code, but that's hardly a complicated issue.
Another alternative would be to use a compound PK, where one column indicates the source replica.
But this is not some flaw in the Replication implementation of Jet -- it's an issue for any replication scenario with a need for meaningful sequence numbers.
This is patently untrue. If you install the Jet synchronizer you can schedule synchs (direct, indirect or Internet synchs). Even without it, you could schedule a VBScript to run periodically and do the synchronization. Those are just two methods of accomplishing automated Jet synchroniziation without needing to open your Access application.
A quote from MS documentation:
JRO is really not the best way to manage Jet Replication. For one, it has only one function in it that DAO itself lacks, i.e., the ability to initiate an indirect synch in code. But if you're going to add a dependency to your app (JRO requires a reference, or can be used via late binding), you might as well add a dependency on a truly useful library for controlling Jet Replication, and that's the TSI Synchronizer, created by Michael Kaplan, once the world's foremost expert on Jet Replication (who has since moved onto internationalization as his area of concentration). It gives you full programmatic control of almost all the replication functionality that Jet exposes, including scheduling synchs, initiating all kinds of synchronization, and the much-needed MoveReplica command (the only legal way to move or rename a replica without breaking replication).
JRO is one of the ugly stepchildren of Microsoft's aborted ADO-Everywhere campaign. Its purpose is to provide Jet-specific functionality to supplement what is supported in ADO itself. If you're not using ADO (and you shouldn't be in an Access app with a Jet back end), then you don't really want to use JRO. As I said above, it adds only one function that isn't already available in DAO (i.e., initiating an indirect synch). I can't help but think that Microsoft was being spiteful by creating a standalone library for Jet-specific functionality and then purposefully leaving out all the incredibly useful functions that they could have supported had they chosen to.
Now that I've disposed of the erroneous assertions in the answers offered above, here's my recomendation:
Because you have an append-only infrastructure, do what @Remou has recommended and set up something to manually send the new records whereever they need to go. And he's right that you still have to deal with the PK issue, just as you would if you used Jet Replication. This is because that's necessitated by the requirement to add new records in multiple locations, and is common to all replication/synchronization applications.
But one caveat: if the add-only scenario changes in the future, you'll be hosed and have to start from scratch or write a whole lot of hairy code to manage deletes and updates (this is not easy -- trust me, I've done it!). One advantage of just using Jet Replication (even though it's most valuable for two-way synchronizations, i.e., edits in multiple locations) is that it will handle the add-only scenario without any problems, and then easily handle full merge replication should it become a requirement in the future.
Last of all, a good place to start with Jet Replication is the Jet Replication Wiki. The Resources, Best Practices and Things Not to Believe pages are probably the best places to start.
您应该阅读 访问 数据库 复制，因为那里有一些信息。
但我认为，为了使其能够与您的应用程序一起正常工作，您必须使用 为此目的可用的方法和属性。
您绝对应该阅读 文档的同步数据部分。
You should read into Access Database Replication, as there is some information out there.
But I think that in order for it to work correctly with your application, you will have to roll out a custom made solution using the methods and properties available for that end.
You should definitely read the Synchronizing Data part of the documentation.
我在 a00 中使用复制多年，直到被迫升级到 a07（当它消失时）。我们在企业层面遇到的最棘手的问题是管理冲突。如果管理不及时，或者数量太多，用户会感到沮丧，数据也会变得不可靠。
当我们的远程站点并不总是连接到互联网时，复制效果很好。这使他们能够处理数据，并在可能的情况下进行同步。每天至少两次。
我们在管理同步的远程计算机上安装了一个单独的数据库，因此用户只需单击桌面上的图标即可引发同步。
用户有一个单独的按钮来推/拉指定 FTP 文件的源，该文件将从旧系统更新。
这个过程非常有效，因为我们有 30 个这样的“节点”在全国各地工作，管理它们的数据并更新到 FTP 服务器。
如果您正在认真考虑这条道路，请告诉我，我可以向您发送我的文档。
I used replication in a00 for years, until forced to upgrade to a07 (when it went away). The most problematic issue we ran into, at the enterprise level, was managing the CONFLICTS. If not managed timely, or there are too many, users get frustrated and the data becomes unreliable.
Replication did work well when our remote sites were not always connected to the internet. This allowed them to work with their data, and synchronize when they could. At least twice daily.
We install a separate database on the remote computers that managed the synchronization, so the user only had to click an icon on their desktop to evoke the synchronization.
The user had a separate button to push/pull in feeds off a designated FTP file that would update from the Legacy systems.
This process worked quite well, as we had 30 of these "nodes" working around the country, managing their data and updating to the FTP servers.
If you are seriously considering this path, let me know and I can send you my documentation.
您可以编写自己的同步软件，该软件连接到笔记本电脑，从其数据库中选择差异并将其插入主数据库。
此操作的简单程度取决于您的数据方案。
（如果你有很多带有 FK 的表......你需要巧妙地做到这一点）。
我认为如果你自己写的话，效率会最高。
自动化这种行为称为复制，并且访问支持，显然，但我从未见过它实现。
正如我猜想的那样，大多数时候笔记本电脑没有连接到主数据库，无论如何（复制数据）都不是一个好主意。
如果您要寻找第 3 方工具来执行此操作 - 寻找可以在复制之前轻松执行表之间差异的工具，并且当然可以增量执行此操作。
You can write your own synchronization software that connects to the laptop selects the diff from it's db and inserts it to the master.
It is depends on your data scheme how easy this operation will be.
(if you have many tables with FKs... you will need to do it smartly).
I think it will be the most efficient if you write it yourself.
Automating this kind of behavior is called replication, and Accesss Supports that apparently, but I've never seen it implemented.
As I guess most of the time the laptop is not connected to the main DB it is not a good idea anyway (to replicate data).
if you will look for a 3rd party tool to do it - look for something that can easily do the diff between the tables before copying, and can do it incrementally of course.
FWIW：
FWIW: