从SQL Server如何从MS Access数据库读取到一个或多个表列中的UPDATE数据？

My SQL Server database table has a column that needs to be Updated with data from an MS Access file. How do I query the MS Access data to perform such an update?

我的SQL Server数据库表有一列需要使用MS Access文件中的数据进行更新。如何查询MS Access数据以执行此类更新？

Import Wizard seems to only handle Inserting of new data and not UPDATE existing data? Or am I misunderstanding how to use the wizard?

导入向导似乎只处理插入新数据而不是UPDATE现有数据？或者我误解了如何使用向导？

2 个解决方案

#1

First set up a ODBC DSN in Windows. Open Control Panel > Administrative Tools > Data Sources (ODBC). Note that on 64 bit Windows, this might open the 64-bit-administrator. However, you need the 32-bit-administrator (%windir%\SysWOW64\odbcad32.exe).

首先在Windows中设置ODBC DSN。打开“控制面板”>“管理工具”>“数据源（ODBC）”。请注意，在64位Windows上，这可能会打开64位管理员。但是，您需要32位管理员（％windir％\ SysWOW64 \ odbcad32.exe）。

Then you can link the SQL-Server tables to your access db. In the Link Tables dialog, choose "ODBC Databases()" as file type.

然后，您可以将SQL-Server表链接到访问数据库。在“链接表”对话框中，选择“ODBC数据库（）”作为文件类型。

You can then query the linked SQL Server tables as if they were access tables.

然后，您可以查询链接的SQL Server表，就像它们是访问表一样。

See Configure Microsoft Access Linked Tables with a SQL Server Database

请参阅使用SQL Server数据库配置Microsoft Access链接表

#2

Sounds like you want to run that operation from the SQL Server side ... "pull" the Access data into SQL Server. If so, you can set up the Access file as a linked server within SQL Server. I've not done that, but have read cases where other people have. I copied these steps from How can I link a SQL Server database to MS Access using link tables in MS Access? at SQLServerPedia.

听起来你想从SQL Server端运行该操作......将Access数据“拉”到SQL Server中。如果是这样，您可以将Access文件设置为SQL Server中的链接服务器。我没有这样做，但已经阅读过其他人的案例。我复制了这些步骤如何使用MS Access中的链接表将SQL Server数据库链接到MS Access？在SQLServerPedia。

1) Open EM.
2) Goto the Server to which you want to add it as linked server.
3) Then goto security > Linked Servers section from console tree.
4) Right click on the Client area. Then New Linked Server.
5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
6) Provide the location of the MDB file.
7) Click OK.

Alternatively, you could run the operation from the Access side, and push the data to SQL Server. If that could work for you, use Olivier's instructions to set up the ODBC-linked SQL Server table. Or you do it without creating a DSN: Using DSN-Less Connections.

或者，您可以从Access端运行该操作，并将数据推送到SQL Server。如果这对您有用，请使用Olivier的说明设置ODBC链接的SQL Server表。或者，您可以在不创建DSN的情况下执行此操作：使用DSN-Less Connections。

Either way you link the table, the UPDATE statement you run from within Access might then be as simple as:

无论哪种方式链接表，您在Access中运行的UPDATE语句可能就像这样简单：

UPDATE
    linked_table AS dest
    INNER JOIN local_table AS src
    ON dest.pkey_field = src.pkey_field
SET dest.access_data = src.access_data
WHERE
    dest.access_data <> src.access_data
    OR dest.access_data Is Null;

#1