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.  

 

ico_nb_arrow_blue.pngManifold 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.

 

 

dlg_import_mdb.png

 

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.  

 

dlg_import_mdb_project.png

 

ico_nb_arrow_blue.pngIf 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.  

 

dlg_import_mdb_table.png

 

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.

 

 

dlg_link_mdb.png

 

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.

 

dlg_link_mdb_project.png

 

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.

 

dlg_link_mdb_table.png

 

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.

 

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.

 

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

 

Launch in 32-bit Mode