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 is usually very easy, but some thought may be required sometimes 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:  In 2022, after 20 years of supporting both 64-bit and 32-bit versions of Manifold products, Manifold dropped support for 32-bit versions.  The last base build for Manifold Release 9 that includes a 32-bit version is build 177.   The last base build for Manifold Release 8 that includes a 32-bit version is build 8.0.33.   Users who require 32-bit versions should use a 32-bit version of build 177 for Release 9 and a 32-bit version of build 8.0.33 for Release 8.  Base builds do not expire and are expected to be available as legacy downloads for a reasonable period of time.   See the product downloads pages for Release 9, Release 8, and Manifold Viewer for links to legacy downloads.

 

The following topic assumes you are using a current, 64-bit Manifold build in 64-bit Windows.   If you are using a 32-bit legacy Manifold build, see the discussion in the Legacy Builds and Microsoft Office Formats topic.

Summary

To link to or import from a Microsoft Office file, launch Manifold in the usual way and try linking to or importing from a Microsoft Office format file, like an .mdb or .xlsx.  In most systems it will just work.   If it does not work, the following tips will make connecting to Microsoft Office formats easy, depending on what version of Microsoft Office, if any, has been installed.

 

 

 

 

There are tricky ways of installing Microsoft's 64-bit Access Database Engine to enable access to Office formats in 64-bit Windows systems even if 32-bit Office is installed.  But those methods are such a hassle it is much easier to just upgrade Office from the 32-bit version to 64-bit Microsoft Office.

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 either of the two data access systems Microsoft has used over the years to support use of Microsoft Office formats in 64-bit Windows.  Only the second of the two, Access Database Engine, is recommended:

 

 

 

When linking to or importing from Microsoft Office formats, Manifold will automatically try connecting using either of the above, and will also try more technologies, such as OLEDB and ODBC drivers, to use whatever Microsoft code is installed on the computer in use to achieve a connection.  The Log Window will report what was tried, with reports such as "tried ACE16, ACE15, ACE14, ACE12, MSDASQL.1" in the case of failure to find any technology that will work or such as "(import) Connected using ACE16" in the case of success.   A good strategy for connections is to simply launch 64-bit Manifold and to try to import data from the desired format.  If that fails, note the messages in the Log Window.   The Log Window messages will indicate what Microsoft component should be installed.

 

If 64-bit Microsoft Office has not been installed, 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.

Installing 64-bit Access Database Engine

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 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:

 

 

There are ways around the above problem, but they are too intricate to be discussed in this topic.  When running 64-bit Windows, it is much easier to simply run 64-bit Microsoft Office, and not 32-bit Microsoft Office.

Microsoft Library Issue

Access Database Engine depends upon a Microsoft library called MSVCR100.DLL.   However, as of this writing (2022) the Access Database Engine installation package might not 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 will 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 the 64-bit version of the DLL and install that.  The current names of the missing DLL in 64-bit form is:

 

 

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.

 

It may be impossible to find the above .exe files on the Microsoft site.   Another approach is to search for and to install the Visual C++ Redistributable for Visual Studio 2012 Update 4, which will install the MSVCR100.DLL file.

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, one option in case of difficulty 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.

Enabling Connections to Office Formats

Access Database Engine already may be installed on our 64-bit Windows system.  The easiest way to find out is to launch Manifold and then to try linking an .xlsx or .accdb file.   

 

Launch Manifold and use File - Link to try linking an .xlsx or .accdb file.  If we can link an .xlsx or a .accdb file, that means we have 64-bit Microsoft Office or 64-bit Access Database Engine installed.  Done!   

 

If that does not work, download the 64-bit Access Database Engine from the Microsoft web site and install it.    If an error message appears saying 64-bit Access Database Engine cannot be installed due to the presence of 32-bit software like Microsoft Office, upgrade Office to 64-bit.

 

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

DBF is an exception - DBF format is normally a Microsoft Office format.  However, Export to DBF within Manifold is handled by a special driver within Manifold and does not rely on Microsoft facilities.  The special driver is in Manifold because DBF is used in shapefiles, and there are many wild and crazy variations on DBF found in shapefiles, too many variations to rely on Microsoft facilities.

 

Can't install 32-bit Access Database Engine - In 64-bit Windows systems that have 32-bit Office installed, Microsoft may not allow installation of 64-bit Access Database Engine.   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.

 

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.  

 

UUID fields - DB, WKx, XLS, and ADO.NET databases support UUID fields as a native UUID type.

 

Booleans - XLS databases support boolean fields.

 

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.