Use the OpenDataSource function to obtain data from access and insert data into the sqlserver2000 table.

Insert into [Table name] (field column)
Select field Column
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "C: \ test. mdb"')... [Table name]

The usage of OpenDataSource () is as follows:
Provides special connection information without using the server name of the link and uses it as part of the four-part object name. Syntax

OpenDataSource( Provider_name,Init_string)

Parameters

Provider_name

Register as the ole db for accessing the data source to provideProgramThe name of progid.Provider_nameThe data type of isChar, No default value.

Init_string

Connection string, which will be passed to the target providerIdatainitializeInterface. The provider string syntax is based on key value pairs. These key value pairs are separated by semicolons, for example, "keyword1 = value; keyword2 = value ."

The basic syntax is defined in Microsoft Data Access SDK. For information about supported specific keyword value pairs, see the documentation in the provider. The following table listsInit_stringThe most common keywords in a parameter.

Keywords	Ole db attributes	Valid value and description
Data Source	Dbprop_init_datasource	Name of the data source to be connected. Different providers use different methods to explain this. For the SQL Server OLE DB provider, this indicates the name of the server. For the jet ole db provider, This specifies the full path of the. MDB file or. xls file.
Location	Dbprop_init_location	The location of the database to be connected.
Extended attributes	Dbprop_init_providerstring	The connection string specified by the provider.
Connection timeout	Dbprop_init_timeout	Timeout value. After the timeout value, the connection attempt will fail.
User ID	Dbprop_auth_userid	The User ID used for the connection.
Password	Dbprop_auth_password	The password used for the connection.
Directory	Dbprop_init_catalog	The initial or default directory name when you connect to the data source.

Note

The OpenDataSource function can be used in the same Transact-SQL syntax location that can use the linked server name. Therefore, OpenDataSource can be used as the first part of the four-part name, which refers to the name of the table or view in the select, insert, update, or delete statement; or the remote stored procedure in the execute statement. When executing a remote stored procedure, OpenDataSource should refer to another SQL Server. OpenDataSource does not accept parameter variables.

Similar to the OpenRowSet function, OpenDataSource should only reference the infrequently accessed ole db data sources. For any data sources with a slight Access frequency, define the linked server for them. Both OpenDataSource and OpenRowset cannot provide all the functions defined by the linked server, such as security management and directory information query. All connection information (including passwords) must be provided each time OpenDataSource is called ).

Example

The following example accesses data from a table in another instance of SQL Server.

 
Select * From OpenDataSource ('sqloledb', 'Data source = servername; user id = myuid; Password = mypass'). northwind. DBO. Categories
 

The following is an example of a query. It queries an Excel spreadsheet through the ole db provider for jet.

 
Select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
 

 
"Special access to the ole db Provider 'Microsoft. Jet. oledb.4.0 'has been denied. The provider must be accessed through the linked server. "
 

 
The OpenDataSource function can be used in the query analyzer, but the query is written to the program. An error occurs during execution: special access to the ole db Provider 'Microsoft. Jet. oledb.4.0 'has been denied. The provider must be accessed through the linked server. "
 

 
The test result shows that the SQL Server account used has insufficient permissions. to perform this operation successfully, the SQL Server user must use systemadministrators "server role ", after the user role is assigned, the operation is successful.