Breaking ACE Out Of The Bubble

[Ebo_Quansah]

Previously, users were required to install the Access Database Engine (ACE) Redistributable (or “redist”) to expose ACE outside of the Office bubble. Upon transferring data between existing Microsoft Office files and those outside of Office, one needed to download a set of components to facilitate the process. 2016, 2019, and O365 consumer versions of Access have not exposed its ACE engine outside of Office, including the ACE OLEDB provider.

Well, the Access team has good news for you. If you have O365, or click-to-run versions of Access 2016/2019 Consumer installed, you will no longer need to install the ACE Redistributable to use the ACE OLEDB provider (Microsoft.ACE.OLEDB.16.0, or Microsoft.ACE.OLEDB.12.0). This will now enable previously unsupported scenarios, including allowing PowerBI to connect to Office data. Custom applications will also be able to connect to Office data without installing the redist.

If you have not installed Office, you can continue using the ACE redist, or you can install the Office 365 Access Runtime, which will include support for anything added after our 2016 MSI version. 

The team is working to expose ODBC and DAO interfaces very soon. Currently With ODBC, users still need to build DSNs to ACE data within an Office app. With DAO, users will still need a complete MSI Office installation to use SQL Server’s migration assistant, which transfers data from various sources into SQL Server. These scenarios are being addressed by the Access team.

Feedback

Please send us your thoughts and feedback on the first step of our ACE Redistributable process! The team is always looking for more ways to improve. You can leave comments here, or use the Send-A-Smile tool in Access to let us know what you think of this experience.  

[gseach]

Hi Ebo,

I just wanted to know whether this initiative will allow creating 32-bit and 64-bit connectors side-by-side on the same machine. The use case is connecting 64-bit SSMA between 32-bit Access C2R and SQL Server 2019 (64-bit)?

Many thanks,

Graham R Seach

Access MVP (2001-2010)

[k-semenenkov]

Hello Ebo,

That's great news! Can you please clarify few questions:
1) How this change will be delivered to those who already have Office installed? Do they have to do Office update, or Windows update, or whatever else?
2) Office x64 COM objects such as Access.Application - they are accessible both for x86 and x64 consumer apps. Will this out-of-bubble ACE have the same feature? I mean if someone has Office x64 installed - will he be able to use Microsoft.ACE.OLEDB from x86 application?
3) As I remember this bubble thing was introduced in Office 2010. Can you please confirm that those who still use Office 2010 & 2013 - they still have to install ACE redistributable to be able to use Microsoft.ACE.OLEDB?

UPDATE and one more question -
4) when these changes will be available? I've just checked with my O365 x64 v 2001 build 12430.20184 which should be the latest for the current date - looks like it does not work yet.

Cheers,
Konstantin

[Philipp Stiefel]

Hi Ebo! this is good news! But please share more detailed information on the technical implementation of this "Bubble Breakout". Best regards, Philipp

[Shane Groff]

@gseach Hi Graham.  No, this does not change the restrictions on having both 32-bit and 64-bit Office installed on a machine (it isn't supported).  If you are using 64-bit SSMA, then you need a 64-bit version of Ace, which means you should be using a 64-bit version of Office.

Shane

[Shane Groff]

@k-semenenkov Hi Konstantin.  

1) This is part of the Office update, version 2001, which has build numbers starting with 16.0.12430.*

2) No, this works the same way that it works for MSI installs, including the Ace redist, if you have x64 Office installed, the COM components are only registered for 64-bit applications. While there are some ways to work around this if you are writing your own custom applications, the situation is exactly the same here as it was before the introduction of C2R.

3) Yes, we are not changing 2010 or 2013 C2R, so if you are using those, you will still need the Ace Redist.

4) That build should have the changes, can you explain what you tried, and what happened?

Shane

[k-semenenkov]

@Shane Groff
Thanks for update!
4) sorry, my mistake, I was so happy to read these news that I forgot that I am using DAO.DBEngine, not Microsoft.ACE.OLEDB.

Microsoft.ACE.OLEDB.16.0 works fine, but DAO.DBEngine.120 - not. As I understand it is still "in the bubble". I didn't check recent OLEDB provider features, but as I remember earlier it was not able to process some pure Access-specific data types such as attachments. Do you have plans to expose DAO.DBEngine.120 out of bubble?

[Shane Groff]

Yes, we do plan to expose the DAO interfaces as well in a future release.

Shane

[Shane Groff]

If you are using SSMA with the Access MSI runtime, and have O365 installed, you may find that SSMA stops working after O365 updates.

If you encounter this issue, the workaround is to reinstall the 2013 Access Runtime.  This problem will go away once O365 supports all of the COM interfaces for non-Office applications, at which point it will no longer be necessary to install an MSI build to use SSMA.

Shane

[TwoCF]

Hi Shane, Any chance you could provide an approximation as to when the other COM interfaces will be included?

[Bruce_H390]

"2016, 2019, and O365 consumer versions of Access have not exposed its ACE engine outside of Office..." If I am reading this correctly then ALL versions of Office 2016 and 2019, both C2R and MSI, also do not expose ACE outside of Office.  Is this correct?  If so are there plans to expose ACE for these MSI Office installations as well?

[Shane Groff]

Only C2R installations have a bubble. For MSI installs, everything is exposed already.

If you have Office 2016 MSI, for example, you can already use the version of Ace installed with that version of Office in non-Office applications, there is no need to install the Access Database Engine Redistributable in that case.

[GarethJHorton]

@Shane Groff Do you have any idea when ACEDAO support will be introduced for C2R Office installs? Thanks.

[Shane Groff]

@GarethJHorton Yes, it is enabled in version 2009 and after, so it is available now in Current Channel builds.  For Semi-Annual Channel, it will be available in July 2021.

[GarethJHorton]

Thanks for the quick response @Shane Groff

After the simple life of deploying ACE 2010, it looks a lot more complicated building an installer to deal with the matrix outlined in https://docs.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb.

It's a shame there is no lightweight ACE 2013, the Runtime is pretty large and performing silent installation also appears to be somewhat challenging too.

Do you have a link for the canonical way to detect the channel/version of a C2R Office, so we can deploy/skip the A2013 Runtime appropriately?

Thanks

Gareth

[kimpan]

Hi,

We got problem in our product with the "built-in" ACE in Office 365 version 2010, the 10th November release (Build 13328.20356).

It has been working with ACE2010 and ACE2016 in older Office-versions.

Any suggestions?

Best regards,

Kim

[denniwil]

@kimpan Kim,

Try uninstalling any previous Access Database Engine Redistributable / Runtime installations from the machine and then perform a repair of Office 365. 

If the issue persists, please provide reproduction steps that describe how you're interacting with ACE when you receive this message. Additionally, what happens if you click No to this prompt? Are any events recorded in the Event Viewer for Application Events?

Regards,

Dennis

[kimpan]

Hi denniwil,

Thanks for your reply.

However it has nothing to do with older MSI- nor older C2R-versions of Office being preinstalled. It has been confirmed on clean Windows OS with Office C2R-versions only installed (Office 2016, Office 2019, Office 365 and Microsoft Apps 365). The versions 2010 release starting from 10th November 2020 and also the following and record-replacing version 2011.

What I notice in the code is that what worked in older Office-versions dating even back to Office 2010 (MSI) with ACE2010, Access 2013 Runtime and ACE2016, stopped working. When the AceCore warning appears, I see that a previously ODBC handle value that was closed using SQLDisconnect is being returned in the afterfollowing SQLDriverConnect which was called with different connection string (another MS Access db-file).

I have not been able to reproduce it in a demo solution to send it in to Microsoft Premier, but this occurs when MFC application A starts MFC Application B and both are accessing several MS Access files using ODBC functionality residing in a MFC DLL. 

Best regards,

Kim

[Philipp Stiefel]

@kimpan, ODBC connectivity is not affected by this change yet. It is still "in the bubble". If I understand correctly, it is planned for early 2021 to also break the ODBC connectivity out of the bubble.

[kimpan]

Hi Philipp,

From the 10th November 2020 releases of Office C2R (2016/(2019/365) technology based Office versions 2010 and 2011,  the ACE is "built-in" and "external/redist" ACE such as ACE2010, Access 2013 Runtime, ACE2016 should not be installed at all as it can cause malfunction.

This is the official statement we got from Microsoft Premier Support and if it's incorrect we would like to know.

If the "external/redist" ACE I mentioned above was installed prior to the Office update to version 2010 and 2011, then ACE must be uninstalled and Office needs to be repaired before ODBC works. This has been confirmed by our QA team.

Best regards,

Kim

[Philipp Stiefel]

@kimpan , please read the post you are commenting under.

---

@Ebo_Quansah wrote:

The team is working to expose ODBC and DAO interfaces very soon. 

This is still not completed/released. Exposing ODBC is on the Access Roadmap for January 2021.

Best regards,

Philipp

[kimpan]

@Philipp Stiefel 

According our contact at Microsoft Premier, it was already officially released from version 2010 released 10th November last year (2020).

You can try out yourself by downloading the officially released Click-to-run Office versions as I stated in my previous post using SqlDriverConnect (ODBC) or OleDispatchDriver (OLE).

Version 2012 has been been released since I wrote last time and the warning message still prevails. In one use case it also actually crashes in 

mso20win32client.dll.

What leads me to think it's a bug is:
  * Works with ACE2010, Access 2013 Runtime and ACE2016
  * If the Access DB file is opened first with SqlDriverConnect and then opened with OleDispatchDriver, it doesn't display any AceCore warning.  Switch the opening order then the message shows up. The warning also shows up in some combination of calls SqlDriverConnect and CDatabase::OpenEx.

Updated 2021-01-26:

It is confirmed by Microsoft as being a bug in Office with built-in ACE and planned to be fixed in May/June.
Support case [Case #:23694309]  -  ACE access using both ODBC and ACE OLEDB drivers stopped working after upgrading Office 2016

We are discussing internally if it is required with a hotfix prior to the plan.

Kim

[JamsSx1]

How is it that nearly a year since it was made available in all other versions this fix still hasn't been applied to the volume license version of 2019???

[Shane Groff]

@sjamm This is a significant feature update, and perpetual/volume license versions don't get significant feature updates (that is one of their features).

This will be fixed in the 2021 volume license version.

Shane Groff

Access Engineering

[k-semenenkov]

@Shane Groff 
Can you please check if there was something broken with ACE accessibility in the recent updates. My application tries to use Access DB engine and fails with error "The operating system is not presently configured". Office 365 version is "16.0.14228.20204". Repair does not help. I am pretty sure it was working few days or may be weeks ago. And it is not the issue of my application - here is the sample *.vbs code which was working fine previously and now fails with the same error:

Dim DBEngine
Set DBEngine = CreateObject("DAO.DBEngine.120")
Call MsgBox("Access DB Engine - OK")
Set DBEngine = Nothing

[isladogs]

Hi Konstantin

I'm using exactly the same version of Office 365 as you.

Perhaps I'm missing the point here but when I tested your vbs sample, I got this error:

Of course it works perfectly if I replace line 2 with:

Set DBEngine = CreateObject("Access.Application")

[k-semenenkov]

@isladogsHi, thank you for your reply

I am getting the same error as you in two cases:
1) when I run this vbs as x64 on machine with office x32 (basically to run these 4 lines as is)
2) when I force to run this code as x32 on machine with office x64. Here I use this trick to force to run it as x32. Full vbs text below.

In both cases that's the expected behaviour. x32 office should expose x32 dao which is not available from x64 and vice versa.

Error I am talking about - "The operating system is not presently configured" - happens when I try to connect DAO using the same x32/x64 bitness as Office installation. All this thread is dedicated to the breakthru that DAO/OLEDB connectiity should become available. And now instead of that connectivity I am getting endless occurence of that error.

CreateObject("Access.Application") - yes, it works fine, but that's not the subject of this discussion.

And here is the full vbs I use to foce x32 execution on x64 machine (sorry some formatting may be broken):

Call Force32bit

Dim DBEngine
Set DBEngine = CreateObject("DAO.DBEngine.120")
Call MsgBox("Access DB Engine - OK")
Set DBEngine = Nothing

Sub Force32bit()
Dim sWinDir, sSys64, sSys32, oShell
Set oShell = CreateObject("WScript.Shell")
sWinDir = oShell.ExpandEnvironmentStrings("%WinDir%")
With CreateObject("Scripting.FileSystemObject")
sSys64 = .BuildPath(sWinDir, "SysWOW64")
If Not .FolderExists(sSys64) Then Exit Sub
sSys32 = .BuildPath(sWinDir, "System32")
If sSys32 = WScript.Path Then
oShell.CurrentDirectory = sSys64
oShell.Run "wscript.exe " & Chr(34) & _
WScript.ScriptFullName & Chr(34), 1, False
WScript.Quit
End If
End With
End Sub

[isladogs]

OK thanks. I'll study that code prperly later.

Out of interest are you aware that, contrary to widely expressed opinions, you can install 32-bit and 64-bit Office on the same machine in certain circumstances (without using a VM).

If interested, see https://www.utteraccess.com/topics/2061676/posts/2784938 

[Shane Groff]

Information about the regression for using DAO from non-Office applications is posted here:
https://support.microsoft.com/office/c551294a-6f4e-4af5-a89f-e79e232b9898

The fix will be available in Current Channel on Aug 10, and is available now by updating to Version 2108 which you can get by switching to Current Channel (Preview) (this isn't yet indicated in the link above, but it will be updated soon).

[k-semenenkov]

@Shane Groffthis seems to be broken again. Today I've got an update v 2209 and now I am getting "Operating system not configured for the application" error on the code trying to access DAO. This can be reproduced with the same vbs I posted above.

[Shane Groff]

This is a new issue in Version 2209.  The problem occurs when Ace is loaded as a DAO provider.

We will publish a page with information on the issue and the fix when it is available here: Fixes or workarounds for recent issues in Access (microsoft.com)

In the meantime, if it works for you, you can work around this problem by loading a different Ace provider, such as the OLEDB provider before loading the DAO provider.

For example, if you put these lines in your VB script example (and have a database at the specified location), then problem will not occur:

Dim objConnection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.open "Provider=Microsoft.Ace.OLEDB.16.0;Data Source=c:\temp\Database1.accdb"

objConnection.close

Shane

[k-semenenkov]

@Shane Groff  thank you for suggestion, but as I remember many years ago OLEDB did not support some specific data types like multivalue and attachments. Does Microsoft.Ace.OLEDB.16.0 support all Access data types and all protection methods (password/mdw)?

[Philipp Stiefel]

@k-semenenkov, if I understand Shane correctly, you don't have to actually use the OLEDB provider.  You just need to load the ACE-OLEDB provider first to prevent the error, after that you can just load the DAO provider and continue as you originally intended.

[k-semenenkov]

@Philipp Stiefelthanks a lot for clarification - it works.

@Shane Groff  sorry to misread you answer and please ignore my question about OLEDB provider.. the next question is how much time do you think it will take to deliver the fix?

I already have a workaround to use DB engine from Access.Application but it is really slow.

[Shane Groff]

@k-semenenkov I can't commit to a specific date for a fix.  However, there will be an Office update on October 11 (Patch Tuesday), and it is likely that we will be able to have a fix in that release.

[rgdelank]

Hello Ebo,

I have today 2023 installed the following Office Version:"Microsoft® Access® LTSC MSO (16.0.14332.20102) 64-Bit"

Running a Vbscript bdb00.vbs try to instantiate the provider "Microsoft.ACE.OLEDB.16.0" but the Apache 2.4 CGI Engine reports the vbscript error:"-2147221164 Srce: Provider Desc: Klasse nicht registriert" class not registered.

running on cmd.exe "cscript bdb00.vbs" run as expected but not from Apache CGI engine.

Question as i understand the problem should not appear on this version of Ms Office or ?

I tryed installing the version 10 of the redistributable and version 12 but error still happen on

"Microsoft.ACE.OLEDB.16.0"

"Microsoft.ACE.OLEDB.12.0"

"Microsoft.Access.OLEDB.10.0"

Any remarks ?

Thank you in advance

Roberto

[rgdelank]

@Shane Groff 

Hello Shane,

i have 2023 the Office Version:"Microsoft® Access® LTSC MSO (16.0.14332.20102) 64-Bit" runing on Windows 10 Home 10.0.19045 64-Bit

I could not instantiate the ACE Engine:"Microsoft.ACE.OLEDB.16.0" nor "Microsoft.ACE.OLEDB.12.0" from Apache CGI engine for vbscript

Running the cmd.exe "cscript.exe bdb00.vbs" run as expected from command line but not from Apache CGI Engine that give the following message:"-2147221164 Srce: Provider Desc: Klasse nicht registriert"

As i understand this error should not appear today...

I tryed installing the redistributables but the error still the same .

Some remarks ?

thank you in advance

Roberto

[rgdelank]

@Shane Groff
Hello Shane,

I have 2023 installed the Office:"Microsoft® Access® LTSC MSO (16.0.14332.20102) 64-Bit" version on Windows 10 Home 10.0.19045 64-Bit
When i run a vbscript bdb00.vbs over the Apache CGI engine the provider "Microsoft.ACE.OLEDB.16.0" nor "Microsoft.ACE.OLEDB.12.0" could be instantiated and receive the message:"-2147221164 Srce: Provider Desc: Klasse nicht registriert"
As i understand that was a problem of the past and should not appear today on this version of office.
Runing the script from command line cmd.exe "cscript.exe bdb00.vbs" run as expected no problem with it.

Somer remarks ?

thank you in advance

Roberto

[rgdelank]

@Shane Groff
Hello Shane,

I have 2023 installed the Office:"Microsoft® Access® LTSC MSO (16.0.14332.20102) 64-Bit" version on Windows 10 Home 10.0.19045 64-Bit
When i run a vbscript bdb00.vbs over the Apache CGI engine the provider "Microsoft.ACE.OLEDB.16.0" nor "Microsoft.ACE.OLEDB.12.0" could be instantiated and receive the message:"-2147221164 Srce: Provider Desc: Klasse nicht registriert"
As i understand that was a problem of the past and should not appear today on this version of office.
Runing the script from command line cmd.exe "cscript.exe bdb00.vbs" run as expected no problem with it.

Somer remarks ?

thank you in advance

Roberto

Microsoft Certified Profesional