none
Linking Microsoft Access databases to SQL 2016

    Question

  • Hey guys,

    I'm in new territory and I can use all the help that I can get.

    Back in January of this year, our new SQL 2016 server came online for our school district and is meant as a replacement for our older SQL 2005 server.

    One of our project requires us to link to an older Microsoft Access databases (looks like it was created in 2003). In fact some of our upcoming projects will require us to link to older MS-Access databases.

    I had attempted to establish a link in my ASP.NET application to the MS Access database using Visual Studio 2015. That became frustrating, so I felt the easier way would be to link using SQL 2016 in SMS:

    I went to Server Objects --> Linked Servers --> New Linked Server...

    In our older SQL 2005 server, in the Provider drop down, we were given the "Microsoft Jet 4.0 OLE DB Provider" option, add the network directory location of the .MDB Access Database in the "Data Source" input box, added "Access" to the "Product Name" input box and all tables from the Access Database shows up with no problem.

    In our SQL 2016 server, I do not see the "Microsoft Jet 4.0 OLE DB Provider" option and I have no idea where to go from here.

    Please help.

    Friday, April 21, 2017 2:21 PM

Answers

  • After three days, I found my answer:


    Note: This works with both Access MDB and ACCDB databases

    1.        Create an Administrator account with “Windows Authentication”. Please note that you are logging on to [network server] with a Network Account. When logging into SQL 2016, if you are logging on using the ‘sa’ account, then this is a “SQL Server Authentication” account. This is not a “Windows Authentication” account. If you try to link an Access database using the ‘sa’ account from another computer on the network, it will not recognize the ‘sa’ account and will throw out an error. Therefore you will need to create a “Windows Authentication” account:
      1. Make note of what Administrator account you use to remote into the [network server]
      2.        Connect to the SQL SMS using the ‘Sa’ account using SQL Server Authentication
      3.        Click on connect.
      4.        In the Object Explorer go to Security -> Logins, and right click on Logins. Select New Login…
      5.        Make sure that Windows authentication is checked.
      6.         At the top beside the Login name: input box, click on the Search… button.
      7.        Click on the Locations… button
      8.        Click on Entire Directory.
      9.          Type in the Administrator account name into the box labelled “Enter the object name to select (examples)” (such as ‘IUSR’)
      10.          Select the network account located in the [DOMAIN].local folder.
      11.        On the top left box, select Server Roles, and check every box.
      12.          Click on OK to save all changes.
    2.        Close SQL SMS completely and log back in as “Administrator”
      1.     Run SQL SMS as Administrator (IE: right click and select as Run as administrator)
      2.        Under Authentication select Windows Authentication
      3.        The account that you had just created above should be greyed out and showing up. Click on Connect
    3.        Connect any Microsoft Database (old or new, doesn’t matter)
      1.        Under Object Explorer, click on Server Objects
      2.        Right click on Linked Servers
      3.        Select New Linked Server…
        1. i.      Linked Server: [This can be anything, e.g.: TEST]
        2. ii.      Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider (the friendly name for Microsoft.ACE.12.0)
        3. iii.      Product name: [this can also be anything. E.g.: ‘Access’]
        4. iv.      Data source: [this is the network location of the Access Database. E.g.: ‘\\server\share\PHONE.MDB’]
        5. v.      Provider string: [leave this blank]
      4.        If all of the above steps have been correctly, you should receive no warning messages.
      5.        Test the connection:
        1. i.      Expand Linked Servers
        2. ii.      Right click on the linked server that you just created and select Test Connection
        3. iii.      You want to see “The test connection to the linked server succeeded”. This is good!
        4. iv.      Expand the linked server that you just created; expand Catalogs; expand default; expand Tables
          • You will see all of the tables included from this database. This is good!
          • Linked tables will not be included

    And that’s it! Access database is now linked to SQL Server 2016

    To test if this works, open a New Query in SQL SMS and type in the following:

    SELECT * FROM [(whatever value you used for Linked Server]...[(Access Table Name)]

    For example:

    SELECT * FROM [PHONES]...[BCDeptof Ed]

    The result should be the entire contents of that table.


    • Edited by Redant J 20 hours 31 minutes ago addition (testing)
    • Marked as answer by Redant J 20 hours 31 minutes ago
    20 hours 31 minutes ago

All replies

  • Hello,

    You can install below redistributable, then create a 32-bit DSN and use it to then create a linked server.

    https://www.microsoft.com/en-us/download/details.aspx?id=13255

    The steps described on below article may help you despite being intended for Posgres.

    http://knowledgebase.progress.com/articles/Article/5297



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Friday, April 21, 2017 3:09 PM
    Moderator
  • It looks like someone had already installed the Redistributable on the SQL 2016 server.

    I am seeing the "Microsoft Office 12.0 Access Database Engine OLE DB Provider". It looked like that was as far as they got with that.

    I will have a look on the Progress knowledge base and report back if it helped.

    Thank you, Alberto!

    Friday, April 21, 2017 4:37 PM
  • Hey Alberto,

    The article did give me some ideas. One of those ideas led me to the following article:

    https://www.experts-exchange.com/questions/28941413/Linked-Servers-in-SQL-Server-Standard-2014-to-MS-Access-2007-Database.html

    (Reference: Brian Crowe  ID: 4156245420  *  16-04-22)

    I have done the following:

    * In SQL SLS, I went to Server Objects --> Linked Servers --> Providers --> Microsoft.ACE.OLEDB.12.0

    * Under Provider Options, I made sure the box marked "Allow inprocess" was checked.

    * Then I attempted to add a new Linked Server, using the instructions from Brian Crowe

    It gave me the following error (edited):

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    The linked server has been created but failed a connection test. Do you want to keep the linked server?

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES" returned message "The Microsoft Access database engine cannot open or write to the file '\\10.10.10.10\c$\[directory]\Phone_Link.accdb'. It is already opened exclusively by another user, or you need permission to view and write its data.". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1708&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    ------------------------------
    BUTTONS:

    &Yes
    &No
    ------------------------------

    I looked in the directory and I did not find any lock file.

    Any ideas?

    Friday, April 21, 2017 6:10 PM
  • Give the full control permission on ‘\\10.10.10.10\c$\[directory]’ folder. In addition, you can copy test.mdb to your local file path instead of UNC network path and test the issue.

    The message indicates that the file is opened by another user. Could you please check if test.mdb was opened in exclusive mode?


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Friday, April 21, 2017 7:53 PM
  • Update:

    Me and a co-worker were going through this.

    We determined the Microsoft.ACE.12.0 Provider was working just fine by copying an Access database with tables (containing no Linked Tables) directly to the SQL Server. We were able to connect with that Access database without any problem.

    At some point, we had determined there is a network rights issue.

    We were logging into SQL Server 2016 using the SQL Server Authentication account 'sa'. We can remote (mstsc) into the server that contains SQL Server 2016 using our administrative accounts.

    From that server, we can navigate to the directory that contains the Microsoft Access file, open a text file, alter that text file and then remove that text file.

    I had figured the next step was to create a Windows Authentication account to match our administrative accounts. 

    From the Object Explorer, going to Security -> Logins -> "New Login...". Starting at 'Login name:' at the top and selecting the 'Search...' button. Clicking on 'Locations...' button. We were given the option of either selecting the current server or 'Entire Directory'. After selecting "xxx.local", we were able to find our accounts by putting our account name in the "Enter the object name to select". That gave us the Login name of "[some domain]\username".

    While still in Login Properties, we selected 'Server Roles' and just selected everything. We left all of the other tabs alone.

    Then we disconnected the server, and reconnected using Windows Authentication. Voila, so that worked.

    Logged into SQL Server in our Windows Authentication account, we tried the Linked Server and this is where it gets interesting:

    In SQL SMS, going to Server Objects -> Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0 -> "Properties"

    If nothing is enabled, or if anything other than "Allow inprocess" is enabled:

    • Create a new linked server using "Microsoft Office 12.0 Access Database Engine..."
    • Test Connection responds with "The test connection to the linked server succeeded" (hooray!)
    • Expand the new linked server -> Catalogs -> default -> Tables responds with the following error (d'oh!):

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES" reported an error. Access denied.
    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES". (Microsoft SQL Server, Error: 7399)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1708&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

    If "Allow Inprocess" is enabled at any time:

    • Create a new linked server using "Microsoft Office 12.0 Access Database Engine..."
    • Test Connection responds with the following error (d'oh!):

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    The test connection to the linked server failed.
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES".
    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1708&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    • Expand the new linked server -> Catalogs -> default -> Tables responds with the following error (d'oh!):
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PHONES". (Microsoft SQL Server, Error: 7303)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1708&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    Please help.

    Thanks!

    23 hours 21 minutes ago
  • After three days, I found my answer:


    Note: This works with both Access MDB and ACCDB databases

    1.        Create an Administrator account with “Windows Authentication”. Please note that you are logging on to [network server] with a Network Account. When logging into SQL 2016, if you are logging on using the ‘sa’ account, then this is a “SQL Server Authentication” account. This is not a “Windows Authentication” account. If you try to link an Access database using the ‘sa’ account from another computer on the network, it will not recognize the ‘sa’ account and will throw out an error. Therefore you will need to create a “Windows Authentication” account:
      1. Make note of what Administrator account you use to remote into the [network server]
      2.        Connect to the SQL SMS using the ‘Sa’ account using SQL Server Authentication
      3.        Click on connect.
      4.        In the Object Explorer go to Security -> Logins, and right click on Logins. Select New Login…
      5.        Make sure that Windows authentication is checked.
      6.         At the top beside the Login name: input box, click on the Search… button.
      7.        Click on the Locations… button
      8.        Click on Entire Directory.
      9.          Type in the Administrator account name into the box labelled “Enter the object name to select (examples)” (such as ‘IUSR’)
      10.          Select the network account located in the [DOMAIN].local folder.
      11.        On the top left box, select Server Roles, and check every box.
      12.          Click on OK to save all changes.
    2.        Close SQL SMS completely and log back in as “Administrator”
      1.     Run SQL SMS as Administrator (IE: right click and select as Run as administrator)
      2.        Under Authentication select Windows Authentication
      3.        The account that you had just created above should be greyed out and showing up. Click on Connect
    3.        Connect any Microsoft Database (old or new, doesn’t matter)
      1.        Under Object Explorer, click on Server Objects
      2.        Right click on Linked Servers
      3.        Select New Linked Server…
        1. i.      Linked Server: [This can be anything, e.g.: TEST]
        2. ii.      Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider (the friendly name for Microsoft.ACE.12.0)
        3. iii.      Product name: [this can also be anything. E.g.: ‘Access’]
        4. iv.      Data source: [this is the network location of the Access Database. E.g.: ‘\\server\share\PHONE.MDB’]
        5. v.      Provider string: [leave this blank]
      4.        If all of the above steps have been correctly, you should receive no warning messages.
      5.        Test the connection:
        1. i.      Expand Linked Servers
        2. ii.      Right click on the linked server that you just created and select Test Connection
        3. iii.      You want to see “The test connection to the linked server succeeded”. This is good!
        4. iv.      Expand the linked server that you just created; expand Catalogs; expand default; expand Tables
          • You will see all of the tables included from this database. This is good!
          • Linked tables will not be included

    And that’s it! Access database is now linked to SQL Server 2016

    To test if this works, open a New Query in SQL SMS and type in the following:

    SELECT * FROM [(whatever value you used for Linked Server]...[(Access Table Name)]

    For example:

    SELECT * FROM [PHONES]...[BCDeptof Ed]

    The result should be the entire contents of that table.


    • Edited by Redant J 20 hours 31 minutes ago addition (testing)
    • Marked as answer by Redant J 20 hours 31 minutes ago
    20 hours 31 minutes ago