none
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server

    Question

  • Okay, this is what's happening.

    My drive: H

    Webserver: W

    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!

    Wednesday, October 18, 2006 9:37 PM

All replies

  • I'm going to move this to the Engine forum, I think they'll have more info on Linked Servers.

    Mike

    Thursday, October 19, 2006 2:05 AM
    Moderator
  • 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:

    <add key="ConnectionString"
     value="Data Source = YourWEBSERVER; User Id=Admin; Password=yourPassword; Database=master; Timeout=60" />
    </appSettings>

    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.

    TaDa!   :)

    Monday, October 23, 2006 9:15 PM
  • 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.

     

     

    Thanks

     

    Monday, March 03, 2008 2:46 PM
  • I had the same problem.

    I installed Microsoft OFFICE on the SQL Server, then reboot, and the issue was solved.


    Friday, November 28, 2008 9:11 PM
  • Had the same problem where this was related to NTSC permissions on the source file...

    Thursday, April 30, 2009 2:28 PM
  • Hey dataarch

    Restart your sql service.

    Wednesday, July 15, 2009 11:56 PM
  • I have the same problem.  I restarted the sql service and it solves the problem, however the next morning the problem is back.  Any ideas?
    Thanks
    Friday, November 06, 2009 4:44 PM
  • Open Management studio as an administrator and run the query.
    Thursday, May 31, 2012 12:16 AM
  • Hello Every One

    Hi have the Fix for this problem, really is easy, you only must to install el SP1 y SP2 of SQL Server 2008 R2 and that's off
    Thursday, October 17, 2013 10:38 PM