1

I have a working code that integrates with Access runtime 2013 (32bit) (reading an Excel file).

I removed the 2013 version, and installed the Access runtime 2016 version instead.

This is the connection string I'm using (that worked with 2013): 

Provider=Microsoft.ACE.OLEDB.15.0;Data Source=C:\\Users\\myuser\\Desktop//District.xls;Extended Properties=\"Excel 12.0;HDR=YES;\";

Also, using Microsoft.Office.Interop.Excel version 15.0.0.0, Runtime Version v2.0.50727

When I'm running the code I'm getting an error:

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

what needs to be done in order to fix this?

EDIT: I have office 2016 x64, Access 2016 Runtime x64, Access Database Engine 2016 Redistributable x64, all installed. Also, changed connection string to be Microsoft.ACE.OLEDB.16.0. Getting error message

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

Frank Robinson
  • 21
  • 1
  • 1
  • 5
  • ACE comes with Microsoft Office. You need a license for ACE or download the runtime library for ACE. Ace is adodb.dll which can be in different places in the c:\Program File folder on the machine. I've have never gotten the runtime to work but others have. It should be part of the Interop. I found it on my machine : C:\Program Files (x86)\Microsoft.NET\Primary Interop Assemblies. It may be in a different folder if you have a different version of office loaded. – jdweng Jan 01 '20 at 14:59
  • And Office 2016 ships`Microsoft.ACE.OLEDB.16.0`,And if just updated, you explicit installed the x86 version,as x64 is default now! – ComputerVersteher Jan 01 '20 at 19:46
  • 1. I have adodb.dll at "C:\Program Files (x86)\Microsoft.NET\Primary Interop Assemblies" 2. I installed office 2016 x64, and AccessDatabaseEngine 2016 redistribute X64, and changed the connection string to be 16 instead of 15, still gets same error message, only now with 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. – Frank Robinson Jan 02 '20 at 14:17

3 Answers3

1

Due to the changes in the Access runtime and office Click to run installs? Well all of office now typical runs as a virtualized application. What this means is that now when you install the Access 2016 runtime, it does not expose an instance of the ACE database engine.

As a result, you actually don't need (or even want) to install the whole big run-time to JUST get use of the ACE database engine. Prior to 2016 (and in fact for some 2013 Access installs), installing access run-time = ACE data engine installed and registered on your computer.

As a result of the above, if you don't really need to launch and automate Access, then ONLY install the ACE data engine. And as noted for C2R installs of Access (or the run-time), you will find that ACE is not exposed and registered anyway.

So, you have to install the ACE data engine separate now. You find a copy here:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Make sure you grab the x32 bit version if you are forcing your .net project to x32 (and if you ARE intending to use x32 bits, then you MUST MUST force your .net project to x86, since if you choose "any" or x64, then you in most cases result in your .net project running as x64, and that will not work with Access (or ACE) x32.

Also, last but not least, while your ACE.15 reference worked, to my knowledge for all the installs (Access 2010, 2013, 2016 and now 2019) ? You as a general rule should find that

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test3\test44.accdb

So the later versions of office (staring around 2013) will not install + expose ACE anymore. For 2016, it quite much always the case (ACE not exposed).

So, install the ACE. It also much smaller then the whole run-time, and thus that's a bonus if you actually don't need Access run-time, but just need ACE.

In addition to having to install ACE separate now (in most cases), you find that using .12 for the ACE version should work from versions 2010 all the way up to 2019 without having to change the provider string.

Edit

I would test if ACE is installed and working. I have a sample .net .exe (one is x32 and one is x64) that you can try and see if ACE is working. FIRST test if you can browse and open a accDB file. If that works THEN try connecting to Excel. You need to first test + check if ACE is working.

The working x32 and x64 with a file browse to test ACE is here:

https://onedrive.live.com/embed?cid=B18A57CB5F6AF0FA&resid=B18A57CB5F6AF0FA%21101313&authkey=AB-I3mHVZhLmd2Q

You don't need (nor does it help) to use the office interop assemblies, since you using the ACE provider for this. The inter-op does not provide use of office, only a set of references for your convenience. If ACE works, then you can try your connection string to Excel, but you need to ensure that ACE x64 is working. The above zip file has two samples - run them both and you know what version of ACE you have installed and working.

Edit 2

To open 2007 and later Excel files? Well keep in mind that Excel files save their data as xml (so do all offce documents - if you rename the xlsx extension as zip, you can now open any office document as a xml file)

Anyway, the connection string will look like this for REAL 2007 format files (and later - note the use of xml tag)

    Dim rstData As New DataTable
    Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                           "Data Source=c:\test\tblHotels.xlsx;" &
                           "Extended Properties='Excel 12.0 Xml;HDR=YES'  "

    Using MyCon As New OleDbConnection(strCon)
        Dim daRead As New OleDbDataAdapter("select * from [tblHotels]", MyCon)
        daRead.Fill(rstData)
        With rstData
            If rstData.Rows.Count = 0 Then
                MsgBox("No data found")
            Else
                MsgBox("Rows of data found = " & .Rows.Count & vbCrLf &
                   "First row of data: HotelName = " & .Rows(0).Item("HotelName") & vbCrLf &
                   "Pk ID (first row) = " & .Rows(0).Item("ID"))
            End If
        End With
    End Using


End Sub

So, for xlsx format, you have to include the "xml" part:

Extended Properties='Excel 12.0 Xml;HDR=YES'

It it is a pre xml format, then it is binary such as xls (as you have), then

Extended Properties='Excel 12.0;HDR=YES'

However, I find this works for either xls, and xlsx

    Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                           "Data Source=c:\test\tblHotels.xlsx;" &
                           "Extended Properties='Excel 12.0;HDR=YES'  "

I used single quotes around the extended properties - so single or double will work.

I also don't know why in your connection string, I see a "/" forward slash? (why is this??). Best guess is that / forward slash - is should be \

I don't believe you need Excel installed for this to work - but I will test this issue.

Community
  • 1
  • 1
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thanks for the detailed answer. Unfortunately it did not solve my problem. When I'm runnign the code with: Microsoft.ACE.OLEDB.12.0;Data Source=" + objPath + ";Extended Properties=\"Excel 12.0;HDR=YES;\" the app crashes,and I can't catch an exception. In EventViewer I get an error saying: Faulting module name: KERNELBASE.dll, version: 10.0.14393.3383 Exception code: 0xc06d007f I also tried with connection string of Microsoft.ACE.OLEDB.16.0, and also one with ;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";" or Excel 8.0, but same result - app crash with KERNELBASE.dll – Frank Robinson Jan 05 '20 at 16:16
  • Also, I tried both x86 and x64 versions of office2016 and the Redistributable you gave a link to. but same result. I have a feeling there's something wrong with the connection string, Do you have any suggestions on how to write it properly? – Frank Robinson Jan 05 '20 at 16:18
  • My app is running in 32 bit, I checked it now. at the moment I have the x86 versions installed, so that part should be fine. – Frank Robinson Jan 05 '20 at 16:28
  • If you running x64 office and x64 Access, then you need + want + must use ACE x64 then. And that means you must set your .net project as x64. Since the very first Atari computer came out, you NEVER been able to mix and match x8 bit software with say x16 bit software. Same goes here, you never could mix and match the bit size of your executable code libraries you are running with different size bit systems. If you are in fact running office x64, then without question you have to install + use ACE x64, and without question you have to force and set your project to x64. – Albert D. Kallal Jan 05 '20 at 18:37
  • if you use "any cpu" or x86, then due to the fact that VS is a x32 bit application, then any time you launch your application, it will run as x32 (x86). So working with office x64, then attempting to use x32 bit software to launch + consume the Excel x64 bit systems will never work. You have force and set your project to run as x64 then. Keep in mind that anytime you use the VS connection builders they will fail on the "test connection" since VS is x32 bits. However when running your project (even debug), then it should work. Of course tossing in Excel further complicates this issue. – Albert D. Kallal Jan 05 '20 at 18:41
  • At the moment, my app & office & redistributable are all x86. When I'm running the code with the connection string: @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + objPath + ";Extended Properties=\"Excel 12.0;HDR=YES;\";"; I get the crash that doesn't event stop at catch block, and on EventViewer I get the KERNELBASE.dll error I wrote in the comment above. – Frank Robinson Jan 06 '20 at 09:56
  • Also, I created now a simple .accdb file with a small table, and I was able to open it using the tool you provided (32BIT VERSION). How do I check with Excel now? – Frank Robinson Jan 06 '20 at 10:07
  • Great - I created that utility because it makes clear that your .net project is running as x32, or x64, and then it uses the existing installed ACE (or JET). I can post the source to that little utility if asked. It is a super tiny .exe file - great for testing on un-known computers - have it on my USB jump drive. See my edit for opening Excel files - it will take me 5 min to post. – Albert D. Kallal Jan 07 '20 at 03:33
1

The reason why everybody has trouble using the 64 Bit version of the ACE Oledb driver is because, you have to explicitly put the outout to 64Bit when you compile your app! If AnyCPU is selected it uses the 32 Bit version by default.

Greetz Andy

andy huggan
  • 11
  • 1
0

We found out that the problem is a known issue in a multi-threaded environment, and currently there is no solution.

https://social.technet.microsoft.com/Forums/office/en-US/be010270-bf19-4763-8c4a-a46b8866de83/microsoftaceoledb120-crashed-in-multithread-scenario?forum=Office2016ITPro

It's probably best to just move on to a newer solution, since this is an old way of accessing Excel files.

Frank Robinson
  • 21
  • 1
  • 1
  • 5