Cannot create Linked Server from MS Access 97 to SQL Server Express 2019

  • I want to create a linked server to a MS Access 97 database to a SQL Server Express 2019 instance.

    Now I used the following command:

    sp_addlinkedserver 'SerialNumberDB', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'C:\ChrisDev\Access DB Upgrade\SerialNumberDB.mdb'

    followed by

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE;

    GO

    However when i tried to access the newly created Linked Server, i got the following error message:

    Linked Server1

    Linked Server2

    I then try to run the following commands:

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 0

    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 0

    GO

    Then got the following error:

    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

    Msg 22001, Level 1, State 1

    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

    Msg 22001, Level 1, State 1

    I am running MS Access 97 on my local machine which is Windows 10 64 bit and running 64 Bit SQL Server Express 2019

    What am I doing wrong here?

     

     

     

  • Jet is a 32 bit technology - won't work with newer versions of SQL (which are all 64 bit)

    you need to use Microsoft ACE instead https://www.microsoft.com/pt-pt/download/details.aspx?id=50040

  • You would need the x64 driver for Access - which isn't available with Access 97.  I am not even sure how you are using Access 97 on a Windows 10 machine or why you are still using such an old version.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I downloaded and ran ACE - 64 bit version but now get the following error when trying to browse a linked server to MS Access that i have created

    Linked Server3

    FYI

    My registry settings for the ACE.OLEDB.12.0 are like this

    Linked Server4

     

  • you may need to enable SSL 2.0 on the SQL server as well - if it is possible

  • have a look at https://social.msdn.microsoft.com/Forums/en-US/24364f85-6b3d-45d1-b54d-a739b3b64b80/add-linked-server-access-mdb-access-2007-accdb-file-in-sql-server-2008?forum=aspdatasourcecontrols

    you do need to have those 2 props to 1, and you also need a login setup if not mistaken.

    and you need to ensure that the Account that the SQL Server runs under has access to the folder where you have the access file.

    and... most of the times its easier to use openrowset to read excel/access than to use a linked server.

  • OK guys I have found the solution to do this.. and now I will explain in steps what to do:

     

    1. Download and run the 64 bit versiond of the Microsoft Access 2010 runtime from this site

      https://www.microsoft.com/en-gb/download/details.aspx?id=10910

    2. Stop your SQL Server service running and change the logon properties from the specified account to a built in account Local System this as Frederico said has access to the folder where you have the access file:Linked Server5
    3.  run the following commands in SSMS

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE;

    GO

    followed by

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0

    GO

    you should see this now in your registry:

    Linked Server6

     

    5) run the following command to create the linked server to MS Access

    EXEC sp_addlinkedserver

    @server = N'SerialNumberDB',

    @provider = N'Microsoft.ACE.OLEDB.12.0',

    @srvproduct = N'OLE DB Provider for Jet',

    @datasrc = N'C:\ChrisDev\Access DB Upgrade\SerialNumberDB.mdb';

    GO

    And boom , i have a Linked Server to Access 97 which i can query in SQL Server Express 2019

    Linked Server7

    Thanks all for your help with this

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply