Need connection string for Office 2019

I wrote a VB .NET program using VS17 that opens Excel files and loads the data into an Access database. For .xls files, I use the connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ...;Extended Properties Excel 8.0;
for .xlsx files, I use
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ...;Extended Properties Excel 12.0 Xml;

All works great on Win 7 PCs and Windows 10 with Office 2016. HOWEVER, I installed my app on my new Windows 10 PC with Office 2019 and the app fails with the error

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine.

My internet search told me to try installing Microsoft Access database engine 2010 but that did not work. Since I had Office 2019 installed, why not change my connection string to

Provider=Microsoft.ACE.OLEDB.16.0;Data Source= ...; Extended Properties Excel 16.0 Xml;

but that did not work either. Error was

The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local
machine.

Is there a connection string for Office 2019 that does work? Any insight would be appreciated.

0
edit delete
are you sure?
asked Mark Cykowski

2 answers

Hello Mark,

i had similar problem this morning, something was changed with xlsx files. To resolve that problem i had change type of file to xls and had use:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xPath + ";Extended Properties=""Excel 8.0;HDR=YES"";" as connection string and it works.

I aways use:

Dim RS as Object
Dim Conn as Object
Set RS = CreateObject("ADODB.RecordSet")
Set Conn = CreateObject("ADODB.connection")

to avoid problems with libraries.

You can use also standard excell driver:

connection_string="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ= path/your_file.xls;" 

Look at https://www.connectionstrings.com/excel/

1
edit delete
are you sure?
answered Krysztof Spaliński

Thank you Krysztof. Your answer is a good one, but I don't think it will work for me. The users of my program select the Excel file and they could select an .xls or .xlsx. I also wrote the program using OLEDB and it works great so far, so I am not inclined to change to ADODB.
My program works fine on the client target Windows 10 PC and I just found out that the target PC has Office 2016 32-bit and my Windows 10 PC has Office 2019 64-bit. Some other responses indicate that my problem is that I targeted Office 32-bit instead of Office 64-bit in VS17. So I will experiment with seeing if changing my target platform is the issue. Thank you again.

Mark

0
edit delete
are you sure?
answered Mark Cykowski

Post an answer but please log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions