From SQL Server, how did I read the UPDATE data in one or more columns of the table from the MS Access database?

Question

There is a column in my SQL Server database table that needs to be updated with the data from the MS Access file. How can I request MS Access data to perform such an update?

The import wizard seems to only handle inserting new data, rather than UPDATING existing data? Or I do not understand how to use the wizard?

Answer 1

First configure ODBC DSN on Windows. Go to Control Panel> Administrative Tools> Data Sources (ODBC). Please note that on 64-bit Windows this can be opened by a 64-bit administrator. However, you need a 32-bit administrator (% windir% \ SysWOW64 \ odbcad32.exe).

You can then link the SQL server tables to your access database. In the Link Tables dialog box, select ODBC Databases () as the file type.

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

See Configuring linked Microsoft Access tables with a SQL Server database.

Answer 2

It looks like you want to start this operation from the side of SQL Server ... "pull" the Access data in SQL Server. If so, you can configure the Access file as a linked server in SQL Server. I did not do this, but I read cases when other people. I copied these steps from How can I link a SQL Server database to MS Access using the communication tables in MS Access? on 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. 

In addition, you can start the operation from the access side and send data to SQL Server. If this may work, use the Olivier instructions to configure the SQL Server table associated with ODBC. Or you do this without creating a DSN: using connections without a DSN.

In any case, when you bind the table, you run the UPDATE from Access, it may be so simple:

 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;