CODEWITHSUNDEEP
excelvbams-access-2000
Christine
Christine

Reputation: 3

connect to access 2000 database using Excel 2016?

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.

Upvotes: 0

Views: 1014

Answers (2)

Gustav
Gustav

Reputation: 56016

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"

Upvotes: 0

Vityata
Vityata

Reputation: 43595

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;

Upvotes: 0

Related Questions