Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
Okay, this is what's happening.
My drive: H
Access Database: G
Created a linked server on W, which points to the Access DB on G. Can run queries from W to see the data, no problem. Trying to run a simple select statement from an asp page, the error: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "blah". What is that all about? I see this error everywhere on the 'net, but there doesn't seem to be a solution that works!
Now get this - if I move the DB to W, create another Linked Server pointing to it (the same box as SQLExpress), it displays the data, no problem. What is that?
Thanks to whomever can help!
Here is the solution, after HOURS on the phone and more on the 'net:
To create a Linked Server:
sp_addLinkedServer 'DatabaseName', 'Access', 'Microsoft.Jet.OLEDB.4.0', '\\PathToDB\vol2\Directory\someDB.mdb'
*** The directory in which the .mdb resides MUST HAVE Everyone with FullControl at the share level.
Right-click the linked Server, Properties. In Security, click 'Be made using this security Context', and type in 'Admin' for the remote login, with no password. (This is a default to access the DB).
In MS SQL Server Management Studio, Right-click the webserver where the server is, Properties. Click 'Security'. It should be the SQL Server and Windows Authentication Mode.
Under 'Security', 'Login', make a new Login. Remember this password. 'Server Roles' should be sysadmin. 'User Mappings', select the databases required. 'Status', make sure it is enabled. This User Id and Password will be used in the connection string in .NET's Web.Config:
value="Data Source = YourWEBSERVER; User Id=Admin; Password=yourPassword; Database=master; Timeout=60" />
A reference must be added and imports: Imports System.Collections.Specialized.NameValueCollection
Dim sConnectionString As String
In the code, add in: sConnectionString = ConfigurationSettings.AppSettings("ConnectionString").
When launching MS SQL Server Management Studio, select the SQL Server and Windows Authentication Mode, a user that was created (as above), and their password. Everything should be good in accessing the data from the linked Server across the network.
I have faced the same issue on the SQL server 2005 when I try to establish link to access database and read and write in to it, It was working before all of sudden it stopped working and it is giving an error "
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server ",
Can anyone help with this issue.