如何在SQL Server中链接Access数据表，实现Access更新同步至SQL Server？

Got it, let's walk through exactly how to link an Access table to your SQL Server database from the SQL Server end, and make sure updates in Access sync back to SQL Server. Most tutorials focus on the Access side, so this will fill that gap.

Prerequisites First #

• Make sure your Access database file (.accdb or .mdb) is stored in a location that the SQL Server service account can access. That means the account (usually NT SERVICE\MSSQLSERVER for default instances) needs read/write permissions on the folder containing the Access file.
• If you're running a 64-bit SQL Server, install the 64-bit version of the Microsoft Access Database Engine (the 32-bit driver won't work with 64-bit SQL Server).

Step 1: Create a Linked Server in SQL Server #

Linked Servers are SQL Server's way of connecting to external data sources like Access. Use the sp_addlinkedserver stored procedure to set this up:

EXEC sp_addlinkedserver 
    @server = N'ACCESS_DB_LINK', -- Name this whatever makes sense for you
    @srvproduct=N'Microsoft Access',
    @provider=N'Microsoft.ACE.OLEDB.12.0',
    @datasrc=N'C:\Your\File\Path\YourAccessDB.accdb'; -- Replace with your actual Access file path

Step 2: Configure Security for the Linked Server #

Next, set up the login mapping so SQL Server can authenticate to the Access database. If your Access database doesn't have a password, use this:

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname=N'ACCESS_DB_LINK',
    @useself=N'False',
    @locallogin=NULL, -- This applies to all local SQL Server logins
    @rmtuser=N'', -- Leave empty if no Access username
    @rmtpassword=N''; -- Leave empty if no Access password

If your Access database is password-protected, fill in the @rmtuser and @rmtpassword fields with the correct credentials.

Step 3: Test the Connection #

Run a quick query to make sure you can read the Access table from SQL Server:

-- Replace [YourAccessTableName] with the name of your Access table
SELECT * FROM ACCESS_DB_LINK...[YourAccessTableName];

If this returns data, the link is working!

Step 4: Ensure Access Updates Sync to SQL Server #

By default, queries against the linked server will pull the latest data from Access every time you run them. But if you want to keep a local SQL Server table in sync with the Access table (so you don't have to query the linked server every time), use a MERGE statement to sync changes automatically.

Example Sync Script #

-- Replace LocalSQLTable with your SQL Server table name
-- Replace [YourAccessTableName] with your Access table name
MERGE INTO dbo.LocalSQLTable AS Target
USING ACCESS_DB_LINK...[YourAccessTableName] AS Source
ON Target.PrimaryKeyColumn = Source.PrimaryKeyColumn -- Match on your primary key
WHEN MATCHED THEN
    UPDATE SET 
        Target.Column1 = Source.Column1,
        Target.Column2 = Source.Column2,
        Target.LastUpdated = GETDATE() -- Optional: track when sync happened
WHEN NOT MATCHED THEN
    INSERT (PrimaryKeyColumn, Column1, Column2, LastUpdated)
    VALUES (Source.PrimaryKeyColumn, Source.Column1, Source.Column2, GETDATE());

Automate the Sync #

To make this sync happen automatically whenever Access data changes, or on a schedule:

1. Save the MERGE script as a stored procedure.
2. Create a SQL Server Agent Job that runs this stored procedure on a regular interval (e.g., every 5 minutes) or trigger it manually when needed.

Key Notes to Avoid Issues #

• Data Type Compatibility: Make sure SQL Server column types match Access types (e.g., Access AutoNumber → SQL Server INT IDENTITY, Access Short Text → SQL Server VARCHAR, Access Date/Time → SQL Server DATETIME2).
• Locking: If multiple users are editing the Access table, be aware that SQL Server queries might lock the Access file temporarily. Keep sync intervals reasonable to avoid conflicts.
• Driver Version: If you get a "provider not found" error, double-check that you installed the correct (32/64-bit) Access Database Engine driver.

内容的提问来源于stack exchange，提问作者techindustry