connect to access 2000 database using Excel 2016?

Question

We have an Access 2000 database that cannot be upgraded or changed. For Office, the only version in our organization is Office 2016. I need to write a vba script from Excel that reads data from that Access 2000. I've tried

ConnectionStr = "PROVIDER=Microsoft.Jet.OLEDB4.0;Data Source=theaccess2000.mdb"
connection.Open

and received the error

"Provider cannot be found. It may not be properly installed."

I think the machine is 64bit. Could someone please help as to what need to be installed/configured on the machine so that excel16 can connect to that old access2000? Thank you so much in advance.

Answer 1

Check the access connection strings here: https://www.connectionstrings.com

About 20 are used for Access 2000:

Standard security (mdb file):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
Persist Security Info=False;

With database password (mdb file):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.mdb;
Jet OLEDB:Database Password=MyDbPassword;

DataDirectory functionality (mdb file):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myAccessFile.mdb;
Persist Security Info=False;

Network Location (mdb file):

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=\\serverName\shareName\folder\myAccessFile.mdb;

Answer 2

Looks like you miss a dot and probably the full path as well:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\somefolder\theaccess2000.mdb"