明辉站/办公软件/内容

sqlserver连接带密码的access_mssql数据库

办公软件2023-08-06 阅读
[摘要]sql server 导入带密码的access数据如何实现将Access的数据的导入到SQL Server呢,相信网络上有很多教程来帮助大家了。但是,大家发现如果access文档使用了密码加密的话,就会发现网上所介绍的方法失灵了。下面将举例来说明。以下有数据库abc.mdb,表结构如下:stud...

   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在很多地方得到广泛使用,例如小型企业,大公司的部门。

……

相关阅读