Connect SQL Server with access with a password

SQL server imports access data with a password

How to import Access data to SQL Server? I believe there are many tutorials on the network to help you. However, if the access document uses password encryption, you will find that the methods described on the Internet fail. The following is an example.

The following database is abc. mdb. The table structure is as follows: student (id int, name char (10), and the password is test. First, import it to SQL server. If abc. mdb is not encrypted, you can use the following methods:

SQL server imports and updates access data (password-free version)

?

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 = 'zhang san' 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 = 'zhang san' WHERE id = 1 3. EXEC sp_addmediaserver @ server = n' sertest', @ provider = n' Microsoft. jet. OLEDB.4.0 ', @ srvproduct = n'ole DB Provider for Jet', @ datasrc = n'c: \ abc. mdb '; EXEC sp_add1_srvlogin 'sertest' EXEC sp_helpserver -- EXEC sp_dropserver N 'sertest' go UPDATE serTest... student SET name = 'zhang san' WHERE id = 1 SELECT * FROM serTest... student WHERE id = 1

Most of the time, our mdb database is encrypted. If we still use the above method, we will find SQL Execution errors. What should we do?

First, let's look at the definitions of related functions in 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 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] parameter 'provider _ name' string, indicates the friendly name (or PROGID) of the ole db access interface specified in the registry ). Provider_name has no default value. 'Datasource 'is a String constant corresponding to a specific ole db data source. Datasource is the IDBProperties interface that will be passed to the access interface to initialize the DBPROP_INIT_DATASOURCE attribute of the access interface. Generally, this string contains the name of the database file, the name of the database server, or the name that can be understood by the access interface for locating the database. 'User _ id' String constant, which is the user name passed to the specified ole db access interface. User_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID attribute to initialize the access interface. User_id cannot be the Microsoft Windows logon name. 'Password' String constant, which is the user password passed to the ole db access interface. When the access interface is initialized, the password is passed in as the DBPROP_AUTH_PASSWORD attribute. The password cannot be a Microsoft Windows password. The connection string specified by the 'provider _ string' access interface is passed in as the DBPROP_INIT_PROVIDERSTRING attribute to initialize the ole db access interface. Generally, provider_string encapsulates all the connection information required to initialize the access interface. For a list of keywords that can be recognized by the ole db access interface of the SQL local client, see 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 is registered as the name of the PROGID of the ole db access interface used to access the data source. The data type of provider_name is char, with no default value. Init_string connection string, which will be passed to the IDataInitialize interface of the target provider. The string syntax of the provider is based on key value pairs. These key value pairs are separated by semicolons, for example, "keyword1 = value; keyword2 = value ". For specific keyword value pairs supported by the provider, see Microsoft Data Access SDK. This document defines the basic syntax. The following table lists the most common keywords in the init_string parameter. Keyword: ole db attribute valid value and description extended attribute DBPROP_INIT_PROVIDERSTRING provides a specific connection string.

?

1 2 3 4 5 6 7 8 9 10 11 12

Sp_addmediaserver [@ server =] 'server' [, [@ srvproduct =] 'product _ name'] [, [@ provider =] 'provider _ name'] [, [@ datasrc =] 'data _ source'] [, [@ location =] 'location'] [, [@ provstr =] 'provider _ string'] [, [@ catalog =] 'catalog '] [@ provstr =] 'provider _ string' ole db access interface-specific connection string that identifies a unique data source. The data type of provider_string is nvarchar (4000), and the default value is NULL. Provstr can be passed to IDataInitialize or set to DBPROP_INIT_PROVIDERSTRING to initialize the ole db access interface. After creating a linked SERVER for the ole db access interface of the SQL local client, you can use the SERVER keyword as Server = servername \ instancename to specify an instance to specify a specific SQL SERVER instance. Servername is the name of the computer that runs SQL Server, and instancename is the name of the specific SQL Server instance that the user will connect.

Query Microsoft ole db Provider for Microsoft Jet on the Internet: It is found that the access database with a password should be marked in the corresponding connection string parameters, which also describes: the mdb data password is actually DBPROP_JETOLEDB_DATABASEPASSWORD in the DBPROPSET_JETOLEDB_DBINIT attribute. We can modify it by initializing DBPROP_INIT_PROVIDERSTRING, which is what we call the connection string, now, the question is about the keyword used to define the Database Password attribute. We found that the keyword is Jet OLEDB: Database Password. However, in the OpenRowSet function, for the sake of compatibility, the keyword pwd seems to adopt the odbc naming mode?

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

The final solution will be published below, with the focus on the red part.

Read and modify data from the access File (with a password)

?

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 = 'zhang san' 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 = 'zhang san' WHERE id = 1 3. EXEC sp_addmediaserver @ 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_add1_srvlogin 'sertest' EXEC sp_helpserver -- EXEC sp_dropserver N 'sertest' go UPDATE serTest... student SET name = 'zhang san' WHERE id = 1 SELECT * FROM serTest... student WHERE id = 1