Microsoft Access and Manifold ODBC

This topic provides a few notes on issues that may arise when connecting from Microsoft Access to a Manifold ODBC Data Source.   

 

That is a different subject than connecting from Manifold to Access.   See  the discussion in the Example: Create and Use New Data Source using an MDB Database example topic.

64-bit integer fields and Access

Many Manifold tables use 64-bit integer fields.  The mfd_id field, for example, is created by Manifold as a 64-bit integer field.  Access only recently has started supporting 64-bit integer fields.  As of this writing, Access supports 64-bit integer fields on an opt-in basis,.    Choosing to opt in support for 64-bit integer fields makes Access format incompatible with older versions of Access and incompatible with other applications that use Access format.   The need to opt-in requires extra work to link Manifold tables with 64-bit Integer fields from Manifold into Access.

 

How to link tables with 64-bit integer fields from Manifold to Access:

 

  1. In the latest version of Access, create a new blank database.

  2. Choose File - Options, Current Database.

  3. Scroll all the way down and check Support BigInt data Type for Linked/Imported Tables.

  4. We can now link the tables from the Manifold data source.

 

How to repair existing links in Access to Manifold tables:

 

  1. Choose File - Options, Current Database.

  2. Scroll all the way down and check Support BigInt data Type for Linked/Imported Tables.

  3. Invoke External Data - Linked Table Manager.

  4. Select the tables linked from Manifold.

  5. Click OK to refresh them.

 

Primary Keys on Text Fields and Access

Access can run into trouble Linking to Manifold tables that have primary keys which are text types if the text type is Unicode, that is, NVARCHAR.  This results from a well-known issue in Access:  

 

 

What causes the problem is that Access asks Manifold to return data for the field in its native format.   Manifold returns Unicode, since that is the native format, but because Access, despite asking for native format, thinks the data should be ANSI anyway the interchange fails and field values will be shown by Access as '#deleted'.  The problem only happens with key fields.  When accessing non-key fields Access either asks specifically for Unicode, which Manifold returns and which Access treats correctly as Unicode, or Access asks specifically for ANSI, in which case Manifold converts the data to ANSI, returns ANSI and Access then correctly treats it as ANSI.

 

This particular problem with Access is not something that can be patched around by Manifold, since Manifold should reply with Unicode when a field is Unicode and Access asks it to be returned in its native format.   Other products also experience the issue with Access, with various ways of dealing with it.  Some products, for example, expose user-level options to force data in key fields to ANSI, either always or when Access asks to return their values in the format native to the field.   Such hacks may cause more problems than they purport to solve:  the forced conversion in such circumstances loses data and ruins uniqueness, because the conversion from Unicode to ANSI is lossy.

 

If our Manifold tables contain a primary key that uses an NVARCHAR text field, one possible approach, besides moving from Access to SQL Server or some other more advanced DBMS, would be in Manifold to create a computed VARCHAR field, building an index on that field and dropping the index on the NVARCHAR field.   Other approaches might be easier, given the specific nature of the interoperability project between Manifold and Access.   

 

This issue in Access has been known for a long time.  It has been reported to be there because there is an external component involved which is difficult for Microsoft to update.

See Also

Collations

 

Schema

 

Project Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

DBMS Data Sources - Notes

 

Connect to MySQL

 

Connect to SQL Server

 

Connect to PostgreSQL

 

Connect to Oracle

 

Connect to Db2

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines

 

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.  

 

Example: Create an ODBC Data Source with Windows - How to create an ODBC data source (a DSN) using dialogs built into Windows 10.

 

Example: Connect to Manifold from Release 8  - Step by step procedure to connect from Manifold System Release 8 to a Manifold .map file using Manifold's ODBC driver.

 

Example: Connect to an ESRI GDB File Geodatabase - Connect Manifold to an ESRI GDB file geodatabase, display the contents, make a selection in the GDB and overlay in a map.

 

Example: Connect to an ESRI GDB usng GDAL/OGR  -  Instead of using Manifold's built-in ability to connect to modern ESRI GDB file geodatabases, use the Manifold GDAL/OGR dataport to take advantage of the GDAL library's ability to connect to deprecated GDB formats.

 

Example: Connect Through Manifold ODBC to a Third Party  - With Release 8, use an ODBC connection to a Manifold .map to connect through the .map project to a third party, external data source, an ESRI GDB file geodatabase.  We use Manifold facilities as an intermediary to give Release 8 capabilities it does not have on its own, to link into data stored within an ESRI file geodatabase.

 

Example: Connect LibreOffice Through Manifold to an ESRI GDB - A companion example topic to the Example: Connect Through Manifold ODBC to a Third Party  topic.  Shows how to connect LibreOffice Base, the database part of LIbreOffice, through Manifold to link an ESRI GDB file geodatabase table into LibreOffice.

 

Example: Connect to Manifold from QGIS - Step by step procedure to connect from QGIS 2.8.9 to a Manifold .map file using Manifold's ODBC driver.