SQL Server 2005 連結伺服器存取遠端 Access 資料庫完整指南 (含 C# DAL 範例)

利用 SQL Server 2005 連結伺服器 技術，透過 Microsoft.Jet.OLEDB.4.0 Provider 直接存取遠端/本地 Access (.mdb) 資料庫，支援四部份命名與 OPENQUERY 兩種查詢語法，並可直接 INSERT/UPDATE/DELETE。本文提供建立連結伺服器腳本、常用 DML 範例、C# 資料存取層 (DAL) 完整實作，以及關鍵權限設定提醒。

一、建立連結伺服器 (一次性設定)

-- 1. 建立連結伺服器
EXEC sp_addlinkedserver
    @server = 'AccessLink',                    -- 連結伺服器名稱
    @provider = 'Microsoft.Jet.OLEDB.4.0',     -- Access 2000-2003 格式
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'D:\Data\Test.mdb';             -- .mdb 完整實體路徑
GO

-- 2. 設定登入對應 (這裡用目前登入認證)
EXEC sp_addlinkedsrvlogin 'AccessLink', FALSE, NULL, NULL, NULL;
GO

-- 3. 測試列舉資料表
EXEC sp_tables_ex 'AccessLink';
GO

注意：Access 2007+ (.accdb) 需改用 Microsoft.ACE.OLEDB.12.0 Provider，並安裝對應 ACE 驅動程式。

二、四大查詢/操作語法範例

1. 四部份命名 (直接當資料表用)

SELECT * FROM AccessLink...[Department];      -- 查詢
SELECT * FROM AccessLink...[TestTable];

INSERT INTO AccessLink...[TestTable] VALUES ('Remote1', 'Data1');
UPDATE AccessLink...[TestTable] SET Col1 = 'New' WHERE Col2 = 'Old';
DELETE FROM AccessLink...[TestTable] WHERE Col1 = 'Del';

2. OPENQUERY (在遠端引擎執行，適合複雜查詢/效能)

SELECT * FROM OPENQUERY(AccessLink, 'SELECT * FROM TestTable');

INSERT INTO OPENQUERY(AccessLink, 'SELECT Col1, Col2 FROM TestTable')
VALUES ('Remote2', 'Data2');

UPDATE OPENQUERY(AccessLink, 'SELECT Col2 FROM TestTable WHERE Col1 = ''Key''')
SET Col2 = 'Updated';

DELETE FROM OPENQUERY(AccessLink, 'SELECT Col1 FROM TestTable WHERE Col1 = ''Del''');

三、移除連結伺服器

EXEC sp_droplinkedsrvlogin 'AccessLink', NULL;
EXEC sp_dropserver 'AccessLink', 'droplogins';
GO

四、C# 資料存取層 (DAL) 完整實作

1. 實體類別

[Serializable]
public class TestTable
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }
}

2. DAL 類別 (使用通用 DbHelperSQL 執行四部份命名 SQL)

public class RemoteTestTableDAL
{
    // 新增
    public void Add(TestTable model)
    {
        string sql = string.Format(
            "INSERT INTO AccessLink...[TestTable] VALUES ('{0}', '{1}')",
            model.Col1, model.Col2);
        DbHelperSQL.ExecuteSql(sql);
    }

    // 更新
    public void Update(TestTable model)
    {
        string sql = string.Format(
            "UPDATE AccessLink...[TestTable] SET Col1 = '{0}' WHERE Col2 = '{1}'",
            model.Col1, model.Col2);
        DbHelperSQL.ExecuteSql(sql);
    }

    // 刪除
    public void Delete(string col1Value)
    {
        string sql = string.Format(
            "DELETE FROM AccessLink...[TestTable] WHERE Col1 = '{0}'", col1Value);
        DbHelperSQL.ExecuteSql(sql);
    }

    // 單筆查詢
    public TestTable GetModel(string col1Value)
    {
        string sql = string.Format(
            "SELECT * FROM AccessLink...[TestTable] WHERE Col1 = '{0}'", col1Value);
        DataSet ds = DbHelperSQL.Query(sql);
        if (ds.Tables[0].Rows.Count == 0) return null;
        DataRow r = ds.Tables[0].Rows[0];
        return new TestTable { Col1 = r["Col1"].ToString(), Col2 = r["Col2"].ToString() };
    }

    // 列表查詢
    public List GetList(string where)
    {
        string sql = "SELECT * FROM AccessLink...[TestTable]";
        if (!string.IsNullOrEmpty(where)) sql += " WHERE " + where;
        DataSet ds = DbHelperSQL.Query(sql);
        var list = new List();
        foreach (DataRow r in ds.Tables[0].Rows)
            list.Add(new TestTable { Col1 = r["Col1"].ToString(), Col2 = r["Col2"].ToString() });
        return list;
    }
}

五、關鍵權限設定 (必讀，否則唯讀)

Access .mdb 檔案所在資料夾，必須給 Everyone (或 SQL Server 服務帳戶) 讀取 + 寫入 權限。

• 路徑範例：D:\Projects\MyApp\App_Data\
• 右鍵資料夾 → 安全性 → 編輯 → 加入 Everyone → 勾選「寫入」。

缺少寫入權限會導致 INSERT/UPDATE/DELETE 失敗 (僅能 SELECT)，錯誤訊息常誤導為語法或連結伺服器問題。

六、適用場景與限制

• ✅ 適合：舊系統 Access 資料遷移/同步、輕量級異構資料整合、開發測試環境。
• ❌ 不適合：高併發生產環境 (Jet/ACE 引擎連線數限制、鎖定升級問題)、大資料量 ETL (建議用 SSIS/BCP/Linked Server to SQL Server)。
• 🔒 安全性：連結伺服器登入對應建議最小權限，避免使用 sa 映射。