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

Question

Post reply

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

Weegee71

SSChasing Mays

Points: 604
More actions
May 11, 2023 at 7:52 pm

#4188191

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:
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?

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

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

Weegee71 SSChasing Mays Points: 604 More actions · Answer 3

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

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 4

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

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.

Weegee71 SSChasing Mays Points: 604 More actions · Answer 6

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

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
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:
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