MDB Microsoft Access

MDB is a file database format invented by Microsoft many years ago for use in Microsoft's Access database application, the database part of Microsoft's Office product line.  Manifold can read and write to .mdb files.

 

Manifold uses Microsoft facilities to work with Microsoft .mdb files.   If Manifold cannot import from, link to, or export to an .mdb file, that means the Windows system we are using is missing the necessary facilities.  Please see the Microsoft Office Formats - MDB, XLS and Friends topic for a solution.

Import

Important: When importing an MDB file the tables and queries that appear in the Manifold project are Manifold components with no further connection to the MDB file from which they were imported.  

 

To import from MDB format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing the .mdb file.

  3. Double-click the .mdb file desired.

  4. Everything found in that .mdb database will be imported into the project.

 

 

 

Double-clicking on the desired .mdb file in the Import dialog as seen above will import into our project everything found in that .mdb database.    

 

In this example we will use books.mdb file that contains the example tables for Chris Fehily's fine book, SQL: Visual QuickStart Guide, which is recommended by Manifold for anyone new to SQL.  

 

 

If tables from the .mdb are not created as shown above, that means the Windows system we are using is missing facilities necessary for a connection to .mdb. Please see the Microsoft Office Formats - MDB, XLS and Friends topic for a solution.

 

This particular .mdb database contains tables.  If it contained queries those, too, would have been imported.  When queries are imported, they are imported as query components but the text within them is not translated from Microsoft Access SQL into Manifold SQL.   Some slight adjustments to that SQL text, as are almost always required when interchanging SQL between database systems, usually will be required.  

 

 

MDB is a surprisingly rational and useful database format, with a reasonable collection of data types.   Manifold will capture them all.

Linking an MDB

Important: When linking an MDB file the tables and queries that appear in that data source in the Manifold project stay resident in the MDB file.   They are MDB components even though they may appear in many respects, for the convenience of the user, to be Manifold components.   MDB files linked into a Manifold project are fully read/write.

 

To link an MDB format file:

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing the .mdb file.

  3. Click the .mdb file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables and queries it contains.

 

 

 

The Save cache  button allows setting cache options.   Most often when linking to a format like MDB, we will ensure the Save cached box is not checked.  Working with a linked MDB database will be faster if we check the box, but if we are going to cache data within the project we may as well simply import the MDB and use full Manifold speed.   We uncheck the box and then we press Link.

 

 

That creates a data source called books that contains all of the tables and any other contents of the .mdb file's database.   We can click on the + icon by the data source cylinder to expand the books hierarchy.

 

 

Opening the titles table we see it is the same table that was imported earlier in this topic.    Since it is linked the table is now resident in the .mdb file.

Writing to MDB

We can write to MDB by using File - Export or by copying and pasting tables into a linked MDB data source.   

 

When writing to MDB, the MDB dataport automatically adjusts the names of newly inserted tables to fit into the limits imposed by MDB, for example, no leading spaces, no double quotations, no periods, and so on.

 

Notes

Read a Fehily book  -  Chris Fehily has written a series of useful books on SQL.   Get your hands on one and read it, a great way to learn SQL.

 

Autosimplification - The MDB dataport automatically corrects names of newly inserted tables to fit into the limits imposed by MDB, for example, no leading spaces, no double quotations, no periods, and so on.

 

Batch transactions -  The MDB dataport wraps batch insert, batch delete, and batch update operations into transactions for robustness and performance.  

 

Automatic sub-batching - The MDB dataport automatically breaks big transactions for batch operations into manageable portions.  Without this feature, large operations will fail with an overflow error when the amount of data to be committed exceeds MDB limits.  The internal MDB limit is too low for GIS work so Manifold protects against overflows.

 

Linking Manifold tables into Microsoft Access - We can use the Manifold ODBC driver to enable Microsoft Access to connect to data in a Manifold .map file.  See notes in the Manifold ODBC driver  topic.

 

See Also

File - Create - New Data Source

 

DBMS Data Sources - Notes

 

Example: Closing without Saving - An example that shows how File - Close without saving the project can affect local tables and components differently from those saved already into a data source, such as an .mdb file database.

 

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: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by a data source.

 

Microsoft Office Formats - MDB, XLS and Friends