0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
Christine
  • 3
  • 3
  • 1
    Try with the `Microsoft.ACE.OLEDB.12.0` provider. For further informations: https://msdn.microsoft.com/en-us/library/office/ff965871(v=office.14).aspx – Florent B. Dec 11 '17 at 16:28
  • Thanks Florent, but it doesn't like the Microsoft.ACE.OLEDB.12.0 . it says "Cannot open a database created with a previous version of your application" – Christine Dec 11 '17 at 16:59
  • Looks like there's no 64 bits version of the `Microsoft.Jet.OLEDB4.0` provider. So your best chance is probably to export the file as CSV on a 32bits Office or with an external tool like https://gallery.technet.microsoft.com/office/Convert-Access-to-CSV-File-0345a8a3#content – Florent B. Dec 11 '17 at 17:28

2 Answers2

0

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;
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thanks Vityata, but when I used Microsfot.ACE.OLEDB.12.0, it says "Cannot open a database created with a previous version of your application" – Christine Dec 11 '17 at 16:56
0

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"
Gustav
  • 53,498
  • 7
  • 29
  • 55