Prerequisites:
MSDTC (Distributed Transaction Coordinator) service has been started,
Exec sp_configure 'ad hoc distributed queries ', 1
Run the OpenRowSet function in Dev 2005 Dev to access the data in the student table in the ACCESS database:
Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'd: \ TT \ school. mdb', student );
The following error occurs:
Ole db provider "Microsoft. Jet. oledb.4.0" for linked server "(null)" Returned message "cannot find the installable isam. ". MSG 7303, level 16, state 1, line 1 Cannot initialize the data source object of ole db provider" Microsoft. Jet. oledb.4.0 "for linked server" (null )".
How does Microsoft ensure correct installation of jet 3.5 and reinstall the jet engine?
Set the c: \ document & Settings \ administrator. domain \ Local Settings \ Temp directory permission
[SQL Server Agent service] is completely controlled and useless, but the results can be found by running the OpenDataSource function.
Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "D: \ TT \ school. mdb"')... student;
It can be properly registered and displayed as a linked server,
EXEC sp_addlinkedserver@server = N'CCCCC', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'd:\tt\school.mdb'
Select * from [CCCCC]... [Student]
In the end, the user ID and password are added by accident to display them normally,
Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'd: \ TT \ school. mdb'; 'admin'; '', student );
The OpenDataSource function can be properly displayed without adding the user ID and password.
Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "D: \ TT \ school. mdb"')... student;
Select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "D: \ TT \ School. mdb "; user id =" admin "; Password = ""')... students;
Summary:
- OpenDataSource and OpenRowset are designed as remote oledb data sources that are infrequently accessed. If frequent access is required, the linked server is used.
2. The link database and opendatasouce are accessed in the form of four-part naming, such as [CCCC]… [Student], table name should not be enclosed in single quotes
3. opendatasouce is more convenient to use. You do not need to add a user name or password.
References
How can I import Excel Data to sql2005?
Ole db provider "Microsoft. Jet. oledb.4.0" for linked server "(null)" Returned message "unspecified error"