PowerShell Script to Update MDB Database Using ADODB Object

Due to lack of command line tools to query/update the .MDB databases, one needs to opt to the ActiveX Data Object (ADO) automation to script the requirement.

The much of the resources out on the web including MSFT TechNet Scripting Blogs, exemplifies this automation using “Microsoft.Jet.OLEDB.4.0” provider.

Yet there are many articles reporting issues using this provider and specially resulting in "Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))" error in PowerShell as I reported here.

I figured out a better option to deal with this automation requirement on Windows x64-bit systems. It’s to use the Access DB DSN to connect to the .MDB database.

Further to that, it’s also a difficult task to perform a string search and replace in .MDB Database since it (Access DB) doesn’t support REPLACE function out of the Access RunTime. You are left with no readily available queries to update the data in the DB.

I’ve authored a PowerShell script that does all these tasks and it can be found here.

Below is a screenshot of the script:

Hope this will come handy for others needing the same/similar automation in PowerShell.

I strongly recommend going through the below resources before you being on Access automation:

• Architecture of the Microsoft Access Engine
• Overview of Data Access Technologies
• Performance Data
• Using 32-bit and 64-bit ACE Providers
• Considerations when Choosing Data Access Technology
• Deprecated Data Access Methods
• Conclusion
• Additional Resources