萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 連接帶密碼的 Access 數據庫:OPENROWSET/OPENDATASOURCE/鏈接服務器三種方式完整參數指南

核心難點

Access 加密後,連接字符串必須攜帶 Database Password,標準 OLE DB 屬性為 Jet OLEDB:Database Password。三種方式參數傳遞位置不同。

方式一:OPENROWSET(臨時、無需建立鏈接服務器)

-- 查詢
SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',           -- Provider (ACE 支持 .accdb/.mdb)
    'Data Source=C:\Data\abc.accdb;Jet OLEDB:Database Password=test;',
    'SELECT * FROM Student WHERE ID = 1'
)

-- 更新
UPDATE OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\abc.accdb;Jet OLEDB:Database Password=test;',
    'SELECT * FROM Student WHERE ID = 1'
)
SET Name = '張三'

關鍵:Provider String(第 2 參數)包含 Jet OLEDB:Database Password=test。

方式二:OPENDATASOURCE(臨時、語法更簡潔)

SELECT * 
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\abc.accdb;Jet OLEDB:Database Password=test'
)...Student

UPDATE OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\abc.accdb;Jet OLEDB:Database Password=test'
)...Student
SET Name = '張三' WHERE ID = 1

... 表示默認 Schema(通常 dbo 或空)。

方式三:鏈接服務器(持久、支持分布式事務、四段式命名)

-- 創建鏈接服務器
EXEC sp_addlinkedserver
    @server = N'AccessLink',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @datasrc = N'C:\Data\abc.accdb',
    @provstr = N'Jet OLEDB:Database Password=test';  -- 關鍵:密碼在 @provstr

-- 創建登錄映射(可選,若 Access 無用戶級安全)
EXEC sp_addlinkedsrvlogin 'AccessLink', 'false', NULL, NULL, NULL;

-- 使用四段式命名
SELECT * FROM AccessLink...Student WHERE ID = 1
UPDATE AccessLink...Student SET Name = '張三' WHERE ID = 1

-- 清理
EXEC sp_dropserver 'AccessLink', 'droplogins'

Provider 選擇建議

  • .accdb (Access 2007+):Microsoft.ACE.OLEDB.12.0 或 16.0(需安裝 Access Database Engine 2010/2016 Redistributable,x64 版匹配 SQL Server 位數)。
  • .mdb (Access 2003-):Microsoft.Jet.OLEDB.4.0(僅 32 位,64 位 SQL Server 需跑 32 位進程或用 ACE 驅動兼容)。

權限與安全

  • SQL Server 服務賬號需有 Access 文件讀寫權限(網絡路徑需域賬號)。
  • Ad Hoc Distributed Queries 選項需開啟(sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE),才能用 OPENROWSET/OPENDATASOURCE。
  • 避免在明文腳本寫密碼,生產環境用鏈接服務器登錄映射存儲憑證。
copyright © 萬盛學電腦網 all rights reserved