Using DAO from Excel VBA VBS Without MS Access installed

Using DAO from Excel VBA VBS Without MS Access installed

Despite having been shutdown for good some years ago, the Microsoft Jet Data Access Objects interface (better known as DAO) is still up and running, as it's still being used by many old (and new) macro-based Excel spreadsheets, VB6/VB2008 software clients, custom scripts and so on. If you're using a recent Windows machine (8, 8.1, 10 and so on) and you try to run one of these, you'll most likely end up seeing something like this:

DAO.DLL not found.
The file DAO.DLL is missing.

Or this:

This application failed to start because DAO.DLL was not found. Re-installing the application may fix this problem.

Or this:

'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)

In earlier versions of Windows, where DAO was shipped by default, you rarely had the chance to see something like this. On modern Windows, where DAO isn't available by default, you can still avoid this problem to occur as long as you have any version of MS Access installed. If you don't have Access, you can still solve the issue by downloading the Microsoft Access Database Engine 2010 Redistributable on the official MS site. Be sure to choose the right build for your system, though: if your app is 32 bits, you'll need to download and install the 32 bits variant because the 32 and 64 bit variant (sadly) cannot coexist.

Installing the MS Access Engine Redistributable will be enough in most cases. However, there's a good chance that you'll be immediately struck by another obscure error, this time related to an ActiveX component:

Runtime Error 429 - ActiveX Component Can't Create Object

This is usually the case when you're using some VBA script (for example, a macro/script within an Excel file) which internally connects to a MS Access or SQL Server database using DAO/ODBC. If you're facing that and you're running a 64 bit environment, it most likely means that there are two news for you: one bad, one good.

Bad news first: the problem is the 64-bit version of MS Office, which seems to have serious compatibility issues even if you use the 64-bit version of the aforementioned Microsoft Access Database Engine 2010 Redistributable. Is there a fix for that? None that I know of.

Well, the good news is that there's a good chance you can work around it in the following way: uninstall the 64-bit version of MS Office and (if you also installed it) the 64-bit version of the aforementioned Microsoft Access Database Engine 2010 Redistributable, then re-install the 32-bit version of both of them. If you don't want to lose your x64 status, you can also setup a 32-bit virtual machine (mounting XP, Vista, Win 8 or Win10) and use it instead. This is precisely what I did in my office when my fellow colleagues had to open a rather antique Excel file with some old DAO/ODBC-based connections hard-coded within some scripts: it took a while, but everything went out well in the end.

That's it for now: happy (un)installing!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.