定期将访问数据库复制到SQL服务器 [英] Copy access database to SQL server periodically

问题描述

我有一个访问2003数据库，其中包含我的所有业务数据。此访问数据库在白天每隔几小时更新一次。

I have an access 2003 database that holds all of my business data. This access database gets updated every few hours during the day.

我们正在写一个网站，需要使用访问数据库中的数据。此网站（暂时）只有只读功能。意思是只需要单向传输数据（Access - > SQL）。

We're currently writing a website that will need to use the data from the access database. This website (for the time being) will have only read only capabilities. Meaning there will only need to be one way transfer of data (Access -> SQL).

我的映像有一种方法来执行此数据从访问到SQL迁移服务器。任何人都有任何链接到我可以阅读的东西。

I'm imaging there's a way to perform this data migration from access to SQL server programatically. Does anyone have any links to something I can read about?

如果这种做法听起来很奇怪，你想建议另一种方式做这个数据可以两种方式（Access - > SQL，SQL - > Access），这是完全正常的。

If this practice sounds odd, and you'd like to suggest another way to do this (or a situation where data can go both ways (Access -> SQL, SQL -> Access), that's perfectly fine.

公司将继续使用Access 2003的业务功能。没有办法，但我想在SQL Server之上构建（只读）网站。

The company is going to continue using Access 2003 for their business functionality. There's no way around that. But I'd like to build the (readonly) website on top of SQL Server.

推荐答案

您可以使用 INSERT 查询将新的Access行复制到SQL Server，如另一个答案中所述。

The strategy you outlined can be very challenging. You could use INSERT queries to copy new Access rows to SQL Server, as described in another answer.

但是，如果您对现有的Access行进行了更改，并且您希望将这些更改传播到SQL Server，那么它不会那么简单，如果您希望删除的Access行已删除，则会更加复杂

However, if you have changes to existing Access rows, and you also want those changes propagated to SQL Server, it won't be so simple. And it will be more complicated still if you want deleted Access rows deleted from SQL Server, too.

对我来说，使用不同的方法似乎更合理。将数据迁移到SQL Server一次。然后使用ODBC链接将Access数据库中的表替换为SQL Server表。此后，对Access应用程序中的数据的更改不需要单独的同步步骤...它们将已经在SQL Server中。而且你不需要写任何代码来同步它们。

It seems more reasonable to me to use a different approach. Migrate the data to SQL Server once. Then replace the tables in your Access database with ODBC links to the SQL Server tables. Thereafter, changes to the data from within your Access application will not require a separate synchronization step ... they will already be in SQL Server. And you won't need to write any code to synchronize them.

如果您担心Web服务器和SQL Server之间的连接是只读的，那么只需设置它们。您仍然可以独立允许Access应用程序的读写权限。

If your concern is that the connections between the web server and SQL Server be read-only, just set them up that way. You can still independently allow read-write permissions for your Access application.

这篇关于定期将访问数据库复制到SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！