Microsoft Access 到 SQL Server - 同步
我有一个客户使用销售点解决方案，其中涉及 Access 数据库作为其后端存储。我正在尝试为该客户提供一项服务，出于 SLA 的原因，需要将此 Access 数据库的部分内容复制到我自己的运行 SQL Server 2008 的数据库服务器中的表中。我可能需要定期执行此操作每天约5次。
是否有一种简单的编程方法或可用的工具来做到这一点？我不想手工完成我认为相对常见的任务。
我在 SQL Azure 上运行它，因此我无法在服务器上运行预打包的软件。它必须是开源的并可移植到 Azure，或者可以在客户端计算机上执行。
不幸的是，我认为我必须使用自己的工具才能做到这一点。在我继续之前，有什么建议或更多可以自行完成此操作的工具吗？
I have a client that uses a point-of-sale solution involving an Access database for its back-end storage. I am trying to provide this client with a service that involves, for SLA reasons, the need to copy parts of this Access database into tables in my own database server which runs SQL Server 2008. I need to do this on a periodic basis, probably about 5 times a day.
Is there an easy programmatic way to do this, or an available tool? I don't want to handcraft what I assume is a relatively common task.
I am running this on SQL Azure, so there's no way for me to run prepackaged software on the server. It would either have to be open source and portable to Azure or executable on the client's computer.
I'm unfortunately thinking I'm going to have to roll my own tool to do this. Any suggestions or more tools that are out there that can do this themselves before I go ahead?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有两件事需要考虑：
对于连接，您需要在客户端服务器和您的服务器之间建立某种 VPN 隧道。
然后使用 SSIS 连接到 MS Access，创建包以将数据从 MS Access 提取到 SQL Server 数据库。在 SQL Server 上，您将需要创建新架构，以镜像或关闭 MS Access
在连接方面，另一个选项 - 由于 MS Access 数据库位于文件中，您可以将文件通过 FTP 传输到您的服务器并将 SSIS 指向文件
There are two things to consider:
For connectivity, you will need to establish VPN tunnel of some sort between the client server and your server.
Then use SSIS to connect to MS Access, to create packages to pull data from MS Access to SQL Server database. On SQL Server, you will need to create new schema, to mirror or be close MS Access
On connectivity side, another option - since MS Access db is in the file, you may be able to FTP the file to your server and point SSIS to the file
David，我查看了类似问题的多种解决方案：从 dbf 转换为 mysql，这里有 3 个适合您的解决方案（都是商业的 - 但相对便宜）：
除此之外，我找不到一个开源或免费的强大的数据转换工具。至少对于 DBF 到 MySQL 的转换来说是这样。可能有一些适用于 SQL/Access 的东西。您可以推出自己的解决方案，但这值得您花时间吗？
披露：我最终使用了 Full Convert。
此外，所有这些产品都会生成某种批处理文件，可以使用任务管理器进行调度。
David, I looked at multiple solution for a similar problem: converting from dbf to mysql, here are 3 solutions (all commercial - but relatively inexpensive) that can work for you:
Other than that I couldn't find a good robust data conversion tool that would be open source or free. At least not for DBF to MySQL conversion. There might be something out there for SQL/Access. You could roll out your own solution, but is it worth your time?
DISCLOSURE: I ended up using Full Convert.
Also all of these products generate some sort of batch file, that can be scheduled using Task Manager.