VBScript and DAO.DbEngibe.120 fails

Question

Hi, all!

I want to access accdb databases through VBScript in various environments.

Win 7 64 Bit (Pro or Home) with Office 2010, 2013, 2016, 365 32 Bit should work as well as the same Offices on Windows 10 64 Bit Pro or Home. So any combination is important to me, because customers could use all of that.

I know, that I have to use

• CreateObject("DAO.DBEngine.120") to access an accdb file and
• CreateObject("DAO.DBEngine.36") for mdb files

I know, that I must call the vbs file via

• %WINDIR%\SysWOW64\wscript.exe "Path\MyScript.vbs"

to use the 32 Bit WScript.exe for 32 Bit Office on 64 Bit Windows.

With Win 7 and Office 2010 everything works fine as expected.

With Office 2010 on Windows 10 it works as expected.

With Office 365 (=Office 2016) on Windows 10 it doesn't (what makes me think it's a problem of Office 365):

• My call CreateObject("DAO.DBEngine.120") fails with "ActiveX component can't create object".
  
• A call CreateObject("DAO.DBEngine.36") succeeds(?!?), but of cause doesn't help to access an accdb file.

Searching the registry on the Windows 10/Office 365 machine I find entrys "DBEngine.36" as well as "DBEngine.120".

I have that behaviour on different machines with Win 10/Office 365.

Are there any ideas how to get things to work with Office 365?

Thanks in advance

Mike

Answer 1

Hi Mike,

I made a test based on your description, and I could reproduce your issue under Office 2016.

Based on your description, it seems you have installed Office in end user, if so, I would suggest you try to use Access.applicaiton by VBScript for a workaround.

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = true
accessApp.UserControl = true
accessApp.OpenCurrentDataBase("E:\Edward.accdb")

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Edward, hi all!

Edward, many thanks for your answer and taking care of my problem, though your workaround isn't a solution I would like to employ in general in case I can avoid it.

I use VBScript to access databases in very different scenarios.

For instance, on the machines, where I do my developing work, I have enhanced the context menus of accdb and mdb via scripts with things like Decompile, Compact/Repair, Open exclusive, Switch bypass key and more. Some of them won't work, if the db file is already opened.

On the other hand I use scripts to access backend files, for example to read or set Db.Properties as well as values in tables. Some of them should just work silent without a visible Access instance. (Okay, I could handle that by visible = False)

However, sometimes I use Jet databases with Word or Excel as a frontend on machines where Access isn't even installed.

So if anybody would come around with a solution that works with DBengine without an Access instance, I would prefer that.

Maybe somebody who has Office 2013 installed could do a little test if the problem occurs there, too? I have no opportunity at the moment to access a 2013 installation.

Would you consider the behaviour as a bug of Office 2016, which probably is solved with the next service pack or is it a misunderstood feature?  :-)

Maybe there's just a different magic number instead of 120 that does the job?

Kind regards
Mike

Answer 3

Hi Mike,

>> Maybe somebody who has Office 2013 installed could do a little test if the problem occurs there, too?

As my test, this issue does not exist in Office 2013.

For this issue in Office 2016, I would suggest you submit a feedback in the link below:

http://access.uservoice.com/

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

The problem (and issue) is that installing office 2016 does not install an “exposed” version of the .120 engine.

The simple solution here is to install the data connectivity components for office, or a version of the Access runtime.

I also agree that you are FAR better off to avoid automation a copy of Access. First is you often can’t control start-up code (and forms), and WORSE is you cannot create an automated copy of the Access runtime. So in terms of choices, creating an instance of the database engine as opposed to Access as you are doing is a FAR better choice. (so much agree with avoiding an automation of a copy of Access when possible).

Ok, so back to your issue. There are several solutions, but the simple matter is at the end of the day you need to install a .120 version of the database engine but MORE important a registered version that works via “Create Object”. (it is may be installed - but not registered on your computer).

Since the office 365 2016 version of Access/office does not seem to install a “registered” version of .120, then you could try a regsvr32 of the ACE data engine (but I not sure of the required regsvr32 and path to the ACE engine. So just install the access 2013 runtime). It less hassle to test try different scenarios in terms of registerting ACE then just re-installing a copy. (and it likely you have to anyway - office 2016 is a virtual-app based on CTR (click to run - so not all office bits and parts are registered).

After the 2013 install, then creating an instance of the ACE engine should work.

In the past you could download + install the office “connectivity” components or before that name what was often called MDAC (and this included JET and now what is called ACE data engine).

However, I can’t find a version later then office 2007 of the connectivity components (which may well work).

So just install the access 2013 runtime, and this should fix this issue.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Answer 5

Hi Albert,

Thanks for your solution, I made a test, it works correctly.

@Mike Carpenter 314, I suggest you check the suggestion from Albert D. Kallal, and you could download the runtime from Microsoft Access 2013 Runtime.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi all!

Albert, thank you very much, this is what I have been waiting for.

I will try the registering, because that would be - if it works - the easiest solution.

My second favourite would be installing the MDAC 2007, which should work, since the accdb format hasn't changed since then, as far as I know.

Installing the 2013 runtime might be a problem. In the past I had bad experience with different Access versions on the same machine.

The next thing is, as I said in the initial post, it's not about getting things to work just on my computer, it's about lots of computers of customers, where sometimes you have to convince administrators or IT departments before you can make changes.

I suppose, a line of registering already installed librarys or an MDAC package is easier to put through than the installation of an access runtime.


Edward, as you wrote, you did a test, which of the three suggestions have you done?

I you tried successfully the regserver, could you please drop the line of code, that did it?

If you tried the MDAC/connectivity package, could you please post the link, whre you got it from?

Furthermore, as you seem to be a member of the Microsoft staff, would you consider the issue as something that will be fixed in an office update in an early future or doesn't that seem likely to?

Anyway, thanks to all you guys, who busied yourself with my problem.

Kind regards
Mike

Answer 7

Hi Mike,

I just download the Microsoft Access 2013 Runtime from my above reply and install it. Kindly make a test.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Actually, if I had a “choice” here, I will first try and see if you can do a regsvr32 on the ACE data engine. I unfortunately not tried or tested this idea with CTR Access 2016.

As I noted, CTR is virtualized apps (app-v), and you have this setup:

So the “dotted” box the bottom right is the “sandbox” that office 2016 runs under, and it is “separate” from regular registered COM applications.

However, when 2016 is installed, a “box” of COM objects is installed OUTSIDE of the CTR system that can interface BETWEEN regular applications and CTR (app-v). The “setup” allows programs to automate office programs despite them being app-v. That "com" interface is new, and looks like this:

So I am “assuming” that in the "COM object Automation" (the non-dotted box above) that an ACE object is provided, but just not registered.

If you could register ACE, then you would not need to install runtime, or the “office connective components”.

I unfortunately don’t have time in the next day or so to track down the path name to ACE, but it would be far less work to regsvr32 ACE as opposed to installing a whole runtime on client computers.

In other words, the VBS script could try the create Object, and if it fails, then the VBS can call a sub that attempted to register the ACE engine. This approach could “well” solve the issue in place of installing a whole messy version of the runtime.

I just don’t know if the above is possible (it should be)– and when I find out, the all of the community will have this “knowledge” and a means to avoid installing a whole version of Access to use the ACE engine which is already installed on the computer anyway.

The path names are the same as regular office with one extra folder name in the path name (again, I busy today - don't have the folder name handy).

And no - I am not a Microsoft employee - but just a volunteer sharing my knowledge here.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Answer 9

Hi
I'm aware this thread is from 2017, but I am experiencing this exact problem (dao.dbengine.120.dll not beeing exposed in CTR Office installations) and my research online did not produce good results. This discussion was the most promising, so I hope maybe Albert found how to solve this without to have to install legacy packages?
Regards,
Gianluca

Answer 10

Hum, no, I never did find a regsvr32 combination that would fix this issue. (And you have to run the syswow64 regsvr32, NOT the default one that is actually windows x64 version).

The ONLY practical suggestion I have is quite much what was previous suggested:

Install the connectivity library. This is STILL far better than a whole messy runtime Access install.

So, installing this library is still your best bet at this point in time:

Microsoft Access Database Engine 2010 Redistributable

https://www.microsoft.com/en-ca/download/details.aspx?id=13255

Because the later CTR (click to run) don’t register the ACE data engine, then the above should install without interference or complaining from the installers. And it still a better choice than installing the whole large Access 2010 runtime.

I did hunt around, but I simply never could get the office bits and parts to register. I may still be possible, but I never did find the correct path, or a means to regsvr32 the ACEDAO.dll.

Anyway, I suppose due to the above connectivity install NOT being a virtualized or so called "app-v" install (like the sandboxed office CTR programs are), then the above 2010 connective library is the best bet, and likely the "least" messing around with a existing machine with a existing office install. 

Certainly better then a runtime install, but NOT as good as a simple regsvr32 (that we can't find right now).

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 11

Thank you, Albert for writing back! Seems as there are really no alternatives and yes, I will install the connectivity library, not the runtime - less messy at least.

Regards,
Gianluca