Microsoft Office Formats - MDB, XLS and Friends

When our Windows system has the required Microsoft components installed, reading and writing Microsoft Office formats like Access MDB or Excel XLS will be effortless in Manifold.   The trick is sorting out the Microsoft installation.  That can be difficult because of conflicts between different Microsoft packages that have evolved over time in incompatible ways.  If we sort out our Microsoft installation, everything works perfectly, all of the time.   

 

 Important: This topic applies to all Office formats managed by Microsoft facilities, including legacy Office formats such as .db, .html. .mdb, .xls, and .wkx, together with newer Office formats such as .xlsx and .accdb.  This topic also applies to any other use of .mdb format, for example, ESRI's personal geodatabase format using .mdb or legacy Manifold mfd/mdb format as used in Manifold Release 4.50.   Manifold Viewer is a read-only version of Manifold, so everything in this topic applies not just to Manifold System but also to the free Viewer product from Manifold.

Manifold Uses Microsoft Facilities

Manifold utilizes Microsoft data access software to connect to Microsoft Office file formats.  Using Microsoft's own code ensures that data will be read from and written to those formats as Microsoft intends, and it automatically takes advantage of Microsoft bug fixes and improvements in Microsoft updates.  However, when using Microsoft's code we are subject to Microsoft's requirements for compatibility between different Microsoft products.  

 

For connections to Office formats, Manifold can use any of the three data access systems Microsoft has used over the years to support Microsoft Office.  Only the last of the three, Access Database Engine, is recommended:

 

 

 

 

Our objective: In an ideal world, when running 64-bit Manifold on a 64-bit Windows system we should install the latest version of Microsoft's 64-bit Access Database Engine. That will allow 64-bit Manifold to connect to Office formats.   When running 32-bit Manifold on a 32-bit Windows system, we should install the latest version of Microsoft's 32-bit Access Database Engine.  That will allow 32-bit Manifold to connect to Office formats.  However, due to flaws in Access Database Engine installation and compatibility with other Microsoft applications, we may be unable to install or use Access Database Engine.

 

If we cannot get Access Database Engine to work, or if we find the procedures to deal with Access Database Engine installation flaws and operational bugs too inconvenient, we can launch and use a 32-bit instance of Manifold.  That always works for the formats JET supports, but does not provide connectivity to newer formats like .xlsx or .accdb.

Installing 64-bit Access Database Engine

There is no sugar-coating it:  installation of 64-bit Access Database Engine can be an inconvenient mess.   It is not compatible with 32-bit Microsoft Office, and it depends upon Microsoft libraries which might not be on the Windows system and which might not be installed by the Access Database Engine installation.  In such cases the installation will not fail in a clean way, but instead operation of the Access Database Engine will be faulty, with failed operations.   Other bugs in Access Database Engine may cause crashes in matters entirely unrelated to reading and writing Microsoft formats like .mdb and .xls.   It really is a mess, but if we are persistent and go step by step we can usually get it running.

 

A Microsoft link to download the Microsoft Access Database Engine 2016 Redistributable package is:  https://www.microsoft.com/en-us/download/details.aspx?id=54920.  Microsoft links may change.  If so, use your Internet search engine to search for "Microsoft Access Database Engine 2016 Redistributable" to find a current download link.   The above download provides downloads for an x64 (64-bit) Access Database Engine as well as a 32-bit version.   Most people only install the x64 version since 64-bit Manifold is where it is used.

 

In 32-bit Windows systems our task is simple: download and install 32-bit Access Database Engine.   In 64-bit Windows system we may encounter a complication:  64-bit Access Database Engine cannot be installed on a 64-bit Windows system if 32-bit Microsoft Office has been installed.  That can be a problem because many people use 32-bit Microsoft Office installations within 64-bit Windows.   If we try to install 64-bit Access Database Engine on such a system, we get an error message:

 

 

We can fix the above by downloading and installing 32-bit Access Database Engine.  We can then launch Manifold in 32-bit mode whenever we want to import from or export to .mdb or other Office formats, like .xls,  .xlsx, or .accdb.    We can import an .mdb in a 32-bit Manifold session and save the .map project.   We can then open that .map project in a 64-bit Manifold session to get the benefits of 64-bit power.

 

If we must leave data in a linked .mdb file instead of importing it into Manifold, it is no problem to use 32-bit Manifold, because .mdb files are limited to only 2 GB, a small size that can be easily handled by 32-bit Manifold.

Microsoft Library Issue

In both 64-bit and 32-bit versions, Access Database Engine depends upon a Microsoft library called MSVCR100.DLL.   However, as of this writing (2020) the Access Database Engine installation package fails to install that library if it is not present on our computer.  

 

If the library is not present, Access Database Engine will not warn us.  Instead, it will try to load another DLL that depends on the missing library, which fill fail.  The operation that called that DLL, which is usually a reading operation, will abort.  In export cases, it will keep trying to load the DLL that depends upon the missing MSVCR100.DLL and that will fail repeatedly.  That may abort the process or the export might work but only very slowly.  

 

To solve that problem we can search the Microsoft site for the missing DLL and install it.   Search for both the 64-bit and 32-bit versions of the DLL and install both.  The current names of the missing DLL in 64-bit and 32-bit versions are:

 

 

Microsoft's site changes frequently so reliable links to the above cannot be published in this documentation.  Instead, use the search feature of the Microsoft site to find them, or use a search engine such as Bing or Google to find them.

Registry Fix for Access Database Engine Graphics Bug

Crazy as it sounds, the Access Database Engine uses graphics services, and if hardware-acceleration for graphics is turned on within the Access Database Engine it can crash in some versions.  This bug has been acknowledged by Microsoft and has been said to be fixed on past occasions but without being fixed in all circumstances.   A Microsoft link that discusses the issue:  https://docs.microsoft.com/en-us/office/troubleshoot/access/odbc-administrator-crashes

 

Although the bug  may be fixed in whatever is the current version of the Access Database Engine, the safe plan is to turn off hardware graphics acceleration within Access Database Engine dialogs, just in case.   That must be done by editing the Windows registry.   Edit the registry to alter this key:

 

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Graphics

 

... to this value:

 

(DWORD) DisableHardwareAcceleration = 1

 

Editing the Windows registry can cause system problems if done incompetently.  We should not edit the registry if we do not know how to do so competently.

Launch Manifold in 32-bit Mode

In a 64-bit Windows system it would be more convenient to simply launch 64-bit Manifold to connect.  But if that is not possible due to the presence of 32-bit Office, we can live with having to use 32-bit Manifold when importing from or exporting to .mdb, .accdb, .xls, .xlsx,  and other Office formats.  Yes, that is a hassle, but it is less of a hassle than a choice between abandoning Office or buying 64-bit Office.   Since a 64-bit Manifold license includes both 64-bit and 32-bit Manifold, we can use 32-bit Manifold to get around the conflict between Microsoft's 32-bit Office and Microsoft's 64-bit Access Database Engine.

 

To launch Manifold in 32-bit mode in a 64-bit Manifold system, see the Launch in 32-bit Mode  topic.

Enabling Connections to Office Formats

Access Database Engine already may be installed on our Windows system.  The easiest way to find out is to launch Manifold and then to try linking an .xlsx or .accdb file.   We try linking an .xlsx or .accdb file, because if we try linking an .mdb the result does not tell us if we have JET or if we have Access Database Engine.  

 

If we have a 64-bit Manifold installation, we try linking an .xlsx or .accdb file twice, first launching Manifold in 64-bit mode to try linking, and then again launching Manifold in 32-bit mode to try linking.  That will tell us if we have 64-bit or 32-bit Access Database Engine installed.   If we are curious to see if we have JET installed, we can launch Manifold in 32-bit mode and also try linking an .mdb file, to see what happens.   

 

 

 

 

It must be emphasized that the above integration issues arise from conflicts various Microsoft packages have with other Microsoft packages.  Manifold is happy to use whatever Microsoft facilities we have installed.  

Legacy Manifold MFD/MDB Format

Early Manifold releases, such as Manifold System Release 4.50, utilized Microsoft .mdb format as an integral part of Manifold's vector storage format.   Manifold mfd/mdb format utilizes two files, a Manifold .mfd file to store geometry and a Microsoft .mdb file to store attributes.   Early Manifold releases that utilized mfd/mdb format automatically installed a copy of Microsoft JET to provide data access to the .mdb file.  Modern Manifold releases do not install JET, and depend on the installation of Microsoft data access facilities as discussed in this topic.

 

If we do not have .mdb capability installed in our Windows system as discussed in this topic, for example, by installing Access Database Engine, Manifold System will not be able to import the .mdb portion of legacy Manifold mfd/mdb format.  Drawings in mfd/mdb format will be imported with only object geometry, but without attributes, that is, without the data fields for each object.  

 

Notes

Can't install 32-bit Access Database Engine - In 64-bit Windows systems that have 32-bit Office installed, Microsoft will not allow installation of 64-bit Access Database Engine.  The usual workaround is to install 32-bit Access Database Engine.   However, that sometimes fails, with mysterious error messages about "64-bit Office products" being installed. A diligent Internet search will usually produce a solution.  For an example, see this thread on Microsoft's site.

 

Does Manifold include JET?  - No.  Early versions of Manifold, such as Release 4.50, included JET to support Manifold's own mfd/mdb format.   Manifold later dropped JET and shifted to Manifold's own 64-bit DBMS format, relying on Microsoft installations of JET to support data access to .mdb and other Office formats.  

 

Why doesn't Manifold automatically install Access Database Engine? - Three reasons: First,  Access Database Engine would increase the size of the Manifold installation by over 160 MB, which would be unfair to people who do not intend to connect to Office formats or who already have Access Database Engine installed.   Second, integration issues between Access Database Engine and other Microsoft software are potentially too complicated to be handled by a Manifold installation script.   If Access Database Engine is not already installed in a Windows system, it is a very simple matter to get it from the Microsoft web site and install it, for those users who want to connect to Office formats.  Finally, despite Manifold's advice to switch from JET to Access Database Engine, many people already have JET installed on their systems and are happy using JET for Manifold connections to .mdb and similar.

 

Access connecting to Manifold - When linking Manifold tables into Access using the Manifold ODBC driver, please review the notes on Access in the DBMS Data Sources - Notes topic.

 

Microsoft is not Manifold - As convenient as it is to be able to use Microsoft's own code to connect to Microsoft's products, when using facilities like Access Database Engine we should keep in mind we are running MIcrosoft code.   Although Microsoft code has a good reputation, in general it is not as bulletproof as the Radian technology used within Manifold.   Connections using Access Database Engine fall outside of Manifold's reputation for never crashing.  

See Also

MDB Microsoft Access

 

Example: Create and Use New Data Source using an MDB Database - This example Illustrates the step-by-step creation of a new data source using an .mdb file database, followed by use of SQL.  Although now deprecated in favor of the more current Access Database Engine formats, .mdb files are ubiquitous in the Microsoft world, one of the more popular file formats in which file databases are encountered.  

 

Launch in 32-bit Mode