Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Linked Server to Access mdb (2000 XP)

    Hi everybody:

    I've written an Access/VBA application from which I need to make monthly updates from a few tables to a db on our company's SQL Server for its website. The application contains many complex queries and much VBA code, and for many reasons, I do not want to put the entire back-end onto SQL Server.

    I set up a DSN package and successfully importe the tables I need to SQL Server. Now, I need to figure out how to update them. After consideration, and in light of the ease of using remote queries, I'm trying to set up the .mdb back-end as a linked server to the SQL Server. However, I am stuck on the Provider string property for MS Jet 4.0 OLE DB Provider for MS Access. Does anyone know what it should be? I've tried leaving it blank and putting in the regular connection string, but keep getting an "Authentication failed" error. (one attempt):

    Provider=Microsoft.Jet.OLEDB 4.0;Data Source=H:KJRBackupsTrueA_be.mdb;Jet OLEDB System Database=HBReportsDBRPTSYSTEM.mdw; user=Kriss;pwd=Jesus

    All the "Help" tells me is: Specify the OLE DB provider string property corresponding to the linked server.
    Who pays the guys that write this stuff??

    Thank you, in advance, for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    Try replacing <code>Jet OLE DB System Database=...</code> with <code>Jet OLE DB:System Database=...</code>

    A good way to construct and test a connection string is to create a new text file and change its extension to .udl (the filename is arbitrary). Double click the .udl file, and visit each of the tabs to set the appropriate settings (you specify the Jet OLE DB driver on the first tab, the database, the user and the password on the second tab and the mdw file on the last tab). Finally, go to the second tab to test the connection. It you succeed, click OK. Then open the .udl file in Notepad. You'll see the connection string.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    Thank you for responding, Hans. I tried your suggestion, but get an error message that it is not a valid udl file.
    At this point, I'm thinking of unsecuring the backend and using OPENROWSET instead of a linked server. I hate to unsecure anything, but the users around here are not that savvy, and frankly, I'm getting tired of fighting with Microsoft.

    What would you suggest as the best way to accomplish my purpose?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    Did you try my suggestion of inserting a colon : between 'Jet OLE DB' and 'System Database'?

    Did you create an empty new text file and rename it to .udl? It should open OK then if you double click it in Windows Explorer.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    Yes, I tried the colon. When I put it into Notepad and saved it with a .udl extension, the data link properties dialog didn't open it.
    I tried using a blank Notepad file, saving it with the .udl extension, and now it opens.

    If I select the OLEDB provider for Jet, I can select my database, and with the regedit HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesSy stemDB key changed to my custom system .mdw path, the connection succeeds. I haven't tested it, but I'm guessing that if I click the "All" tab and enter the Jet OLEDB:System database path to my custom system.mdw, it would also work.

    Great! Now, how do I tell SQL Server to use this connection? When I copy and paste the connection string from the .udl file into the Provider string text box in the SQL Server Linked Server Properties General tab, I get the same authentication failed error. I have changed the drive mapping in the connection string to the console root, just in case SQL server can't find our network mappings, but I can't think what else could be wrong with it. When I double-click on the tables after closing the properties dialog, the same authentication failure error 7399 shows up.

    I am guessing that perhaps something is incorrect with my Security tab settings. I have tried selecting my SQL Server login as the Local Login, my system.mdw Userid as the Remote User, with my Access password as the Remote Password, and not checking Impersonate. Is this correct? The Server Options just have Data Access checked, with the connection and query timeout options set to 0.

    Any ideas?

    Thank you for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    I'm afraid I can offer no further help. I don't use SQL Server. I hope someone else will come up with a useful suggestion.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Server to Access mdb (2000 XP)

    It's not exactly a solution, but after my systems guy made the regedit change on the box running SQL Server, I can now use Query Analyzer and OPENROWSET to get the records from my secured Access .mdb file.

    Still don't know why Enterprise Manager can't do it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •