|
Microsoft Office Access是由微軟發布的關系數據庫管理系統。它結合了 MicrosoftJet Database Engine 和 圖形用戶界面兩項特點，是 Microsoft Office 的系統程序之一。Microsoft Office Access是微軟把數據庫引擎的圖形用戶界面和軟件開發工具結合在一起的一個數據庫管理系統。它是微軟OFFICE的一個成員, 在包括專業版和更高版本的office版本里面被單獨出售。2018年9月25日,最新的微軟Office Access 2019在微軟Office 2019里發布。 sql server 導入帶密碼的access數據
如何實現將Access的數據的導入到SQL Server呢，相信網絡上有很多教程來幫助大家了。但是，大家發現如果access文檔使用了密碼加密的話，就會發現網上所介紹的方法失靈了。下面將舉例來說明。
以下有數據庫abc.mdb，表結構如下：student(id int,name char(10))，密碼為test。先要將其導入到sql server當中，假如abc.mdb沒有加密，則可以采用下面幾種方法：
sql server 導入、更新access的數據(無密碼版)
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
1.OpenRowSet方式
SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0',';database=C:abc.mdb','select * from student where id=1')
update OpenRowSet('microsoft.jet.oledb.4.0',';database=C:abc.mdb','select * from student where id=1') set name='張三'
2.OpenDataSource方式
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:abc.mdb')...student
update OpenDataSource('microsoft.jet.oledb.4.0','Data Source=C:abc.mdb')...student set name='張三' WHERE id=1
3.鏈接服務器方式
EXEC sp_addlinkedserver
@server = N'serTest',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:abc.mdb';
EXEC sp_addlinkedsrvlogin 'serTest'
EXEC sp_helpserver
--EXEC sp_dropserver N'serTest'
go
UPDATE serTest...student SET name='張三' WHERE id=1
SELECT * FROM serTest...student WHERE id=1
|
很多時候我們的mdb數據庫是加密的，那么如果我們仍采用上面的方法，會發現sql執行出錯，該怎么辦呢?
我們首先查看下msdn中相關函數的定義：
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
參數
'provider_name'
字符串，表示在注冊表中指定的 OLE DB 訪問接口的友好名稱（或 PROGID）。provider_name 沒有默認值。
'datasource'
與某個特定 OLE DB 數據源相對應的字符串常量。datasource 是將傳遞給訪問接口的 IDBProperties 接口以初始化訪問接口的 DBPROP_INIT_DATASOURCE 屬性。通常，該字符串包含數據庫文件的名稱、數據庫服務器的名稱，或者訪問接口能理解的用于定位數據庫的名稱。
'user_id'
字符串常量，它是傳遞給指定 OLE DB 訪問接口的用戶名。user_id 為連接指定安全上下文，并作為 DBPROP_AUTH_USERID 屬性傳入以初始化訪問接口。user_id 不能是 Microsoft Windows 登錄名稱。
'password'
字符串常量，它是傳遞給 OLE DB 訪問接口的用戶密碼。在初始化訪問接口時，password 作為 DBPROP_AUTH_PASSWORD 屬性傳入。password 不能是 Microsoft Windows 密碼。
'provider_string'
訪問接口特定的連接字符串，作為 DBPROP_INIT_PROVIDERSTRING 屬性傳入以初始化 OLE DB 訪問接口。通常 provider_string 封裝初始化訪問接口所需的所有連接信息。有關 SQL 本機客戶端 OLE DB 訪問接口可識別的關鍵字列表，請參閱Initialization and Authorization Properties。
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
OPENDATASOURCE ( provider_name, init_string )
provider_name
注冊為用于訪問數據源的 OLE DB 訪問接口的 PROGID 的名稱。provider_name 的數據類型為 char，無默認值。
init_string
連接字符串，該字符串將要傳遞給目標提供程序的 IDataInitialize 接口。提供程序字符串語法是以關鍵字值對為基礎的，這些關鍵字值對由分號隔開，例如：“keyword1=value; keyword2=value”。
若要了解提供程序上支持的特定關鍵字值對，請參閱 Microsoft Data Access SDK。該文檔定義了基本語法。下表列出了 init_string 參數中最常用的關鍵字。
關鍵字 OLE DB 屬性 有效值和說明
擴展屬性
DBPROP_INIT_PROVIDERSTRING
提供程序特定的連接字符串。
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
|
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
[ @provstr = ] 'provider_string'
OLE DB 訪問接口特定的連接字符串，它可標識唯一的數據源。provider_string 的數據類型為 nvarchar(4000)，默認值為 NULL。provstr 或傳遞給 IDataInitialize 或設置為 DBPROP_INIT_PROVIDERSTRING 屬性以初始化 OLE DB 訪問接口。
在針對 SQL 本機客戶端 OLE DB 訪問接口創建鏈接服務器后，可將 SERVER 關鍵字用作 SERVER=servernameinstancename 來指定實例，以指定特定的 SQL Server 實例。servername 是運行 SQL Server 的計算機名稱，instancename 是用戶將連接到的特定 SQL Server 實例的名稱。
|
在網上查詢了Microsoft OLE DB Provider for Microsoft Jet的資料：發現對于帶有密碼的access數據庫，要在相應的連接字符串參數中進行標注，文章里面還說明了：mdb數據的密碼其實是DBPROPSET_JETOLEDB_DBINIT屬性里面的DBPROP_JETOLEDB_DATABASEPASSWORD ，我們可以通過對初始化DBPROP_INIT_PROVIDERSTRING對其進行修改，也就是我們平時所說的連接字符串，這下問題就剩下尋找定義數據庫密碼屬性的關鍵字了。我們發現，其關鍵字為Jet OLEDB:Database Password，然而在OpenRowSet函數中，由于微軟為了兼容性的考慮吧，其關鍵字為pwd，貌似采用的是odbc的命名模式?
|
DBPROP_INIT_PROVIDERSTRING
Description: Extended Properties
|
|
| DBPROP_JETOLEDB_DATABASEPASSWORD |
Type: VT_BSTR
Typical R/W: R/W
Description: Jet OLEDB:Database Password
Password used to open the database. This differs from the user password in that the database password is per file, while a user password is per user.
|
Microsoft OLE DB Provider for Microsoft Jet
Appendix A: Properties
IDBDataSourceAdmin for Microsoft Jet
Initialization Properties
Provider-Defined Properties in DBPROPSET_JETOLEDB_DBINIT
OLE DB Initialization Properties: Quick Reference
Initialization Property Group
下面公布最終解決方案，紅色部分為重點。
從access文件中讀取、修改數據(有密碼版)
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
1.OpenRowSet方式
SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0',';database=C:abc.mdb;<strong>pwd=test</strong>','select * from student where id=1')
update OpenRowSet('microsoft.jet.oledb.4.0',';database=C:abc.mdb;pwd=test','select * from student where id=1') set name='張三'
2.OpenDataSource方式
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:abc.mdb;<strong>Jet OLEDB:Database Password=test</strong>')...student
update OpenDataSource('microsoft.jet.oledb.4.0','Data Source=C:abc.mdb;Jet OLEDB:Database Password=test')...student set name='張三' WHERE id=1
3.鏈接服務器方式
EXEC sp_addlinkedserver
@server = N'serTest',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:abc.mdb',
<strong>@provstr =N';pwd=test'</strong>;
EXEC sp_addlinkedsrvlogin 'serTest'
EXEC sp_helpserver
--EXEC sp_dropserver N'serTest'
go
UPDATE serTest...student SET name='張三' WHERE id=1
SELECT * FROM serTest...student WHERE id=1
|
Microsoft Access在很多地方得到廣泛使用，例如小型企業，大公司的部門。
|
