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

 

A complicating factor in the use of .mdb files is that Microsoft has not provided a seamless path from 32-bit to 64-bit applications which use .mdb.  The Microsoft transition to 64-bits has been sufficiently tricky for users of Access files that many users must utilize 32-bit applications to connect to .mdb even when operating 64-bit Windows.   tech_ravi_sm.png

 

Very Important:  In this example we launch 32-bit Manifold to connect to .mdb files.   We do this even if we are operating in 64-bit Windows and would normally launch 64-bit Manifold.  If we are running 32-bit Windows the only Manifold we have available is 32-bit, of course, but even if we are running 64-bit Windows we must choose to run 32-bit Manifold to connect to an MDB file.

 

Using 32-bit Manifold avoids complications arising from incompatibility between Microsoft's 64-bit Access Database Engine and other Microsoft applications that may be installed on the same system.

 

Launch 32-bit Manifold, or, if we are using the free Manifold Viewer, we launch 32-bit Viewer.    Either way the same process below works.   

 

eg_datasrc01_00.png

 

If we have installed Manifold from the .exe installation file that installs using Windows Installer, the 32-bit form of Manifold can be launched by drilling down into the Start button menu, as seen above, a screenshot from Windows 10.

 

If we are using a portable installation, we launch from within the bin hierarchy.

 

dlg_launch_32bit_mfd_portable01_01.png

 

Portable installations for Manifold / Viewer create a hierarchy of files.   The files in the bin folder are 32-bit.   The files in the bin64 folder are... drumroll, please... 64-bit.    To launch the 32-bit version go into the bin folder.

 

dlg_launch_32bit_mfd_portable01_02.png

 

Within the bin folder, double-click on manifold.exe to launch Manifold in 32-bit mode.

 

Whether we launch Manifold from the Windows start button system or from within a portable installation, Manifold looks and runs the same.   We have to look into the Help - About dialog to see if Manifold has been launched in 32-bit mode or 64-bit mode.

 

eg_datasrc01_01.png

 

To create a new data source we choose File - Create - New Data Source.  

 

eg_datasrc01_02.png

 

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

 

We give our new data source a memorable name, Books MDB, we choose File: mdb for the Type and we use the [...] button to navigate to where the books.mdb file is located so we can choose that file in the usual Windows way.    We will not check the Open as read-only box as we might want to edit the contents of this .mdb.   Press Create Data Source.

 

eg_datasrc01_03.png

 

A new data source, indicated by a "database" cylinder icon, appears in the Project pane.   We can expand the Books MDB data source by clicking on the + icon for the data source.

 

eg_datasrc01_04.png

 

When we expand the data source we can see into the hierarchy within.    The Books MDB data source in addition to tables and other items also contains its own System Data folder.   We can click on the + icon by the System Data folder to expand it to see what is inside.

 

eg_datasrc01_05.png

 

Inside the System Data folder within the Books MDB data source are two tables, an mfd_meta table and an mfd_root table just like the two tables within the System Data folder at the top of the project hierarchy.   

 

eg_datasrc01_05a.png

 

If we opened those tables we would see they contain the metadata Manifold uses to manage the various items within the Books MDB  data source, for example, the names of the tables and queries as seen in the mfd_root table for the Books MDB data source.

 

To manage what is in the data source Manifold generates a System Data folder containing the two system tables for every data source that is added to a project.  To make it easier for us to keep track of which System Data folders are associated with which data source, Manifold simply places the System Data folder for a data source within that data source's folder hierarchy in the Project pane.   That's why when we click open the Books MDB data source we see within it a System Data folder that contains the Manifold system files for operating the Books MDB data source.  

 

It is important to understand that the placement of Manifold's System Data folder for the Books MDB data source within the data source's Project pane hierarchy is just an organizational convenience: Manifold does not actually create two new tables within the .mdb file.   We could see that, for example, if we had checked the Open as read-only box in the New Data Source dialog when we created the Books MDB data source using the books.mdb file.   In that case a System Data folder containing the two mfd_ tables would still have appeared within the Books MDB data source hierarchy even though the .mdb file would have been opened read-only with no possibility of actually creating two new tables within the .mdb

 

The organization convenience of showing a data source's System Data folder within the data source greatly simplifies finding and using the system tables for that data source should we want to use them.  Instead of some other possible scheme, such as having all of the system tables for all the various data sources located all together in some special folder somewhere in the Project pane, the system tables for a data source can always be found within the data source's hierarchy in the Project pane.  That makes it easy to find the system tables for a particular data source even in projects where there are hundreds of data sources.

 

Showing the Manifold system tables for a data source within that data source's hierarchy is also consistent with how Manifold uses the same naming scheme for referring both to a data source's own tables as well as to the Manifold system tables for that data source.   For example, we can refer to the mfd_root table for the Books MDB data source the same way we would any table in that data source, by prefixing [Books MDB]:: to the name of the table as in...

 

SELECT * FROM [Books MDB]::[mfd_root];

 

To emphasize: the two mfd_ system tables inside the System Data folder which appears within the Books MDB data source have not been created by Manifold inside the .mdb file.  They are virtual tables created inside the Manifold project, but for ease of use Manifold displays them within the Books MDB data source hierarchy.

 

eg_datasrc01_05b.png

We will close the mfd_root  table by clicking on the X to the right of its tab.   We also will close the System Data folder by clicking on the small - box icon.

 

eg_datasrc01_05c.png

Next we will double-click on the authors table to open it.   

 

eg_datasrc01_06.png

 

We can see that it contains the data just like in the .mdb and as discussed in Chris Fehily's fine book.  The columns seen in the illustration above have been adjusted by right-clicking on the column head and clicking Best Fit Title.   We will use the authors table for an example of a query.

 

Now that we have had a look at it, we will close the authors table by clicking the X to the right of the tab strip.   This will free up room in the small illustrations we are using to show the Command window.   

 

eg_datasrc01_07.png

 

We choose View - New Command Window - SQL to open a Command window for queries, and then we drag and drop the authors table into the tables pane of the Command window.

 

eg_datasrc01_08.png

 

After we drop the authors table into the tables pane we can double-click on items in the table, such as the name of the table or the name of a field, to add them to a query without repetitively keyboarding names.  That also reduces the risk of typographical errors.   

 

The name of the table is given in the style used to refer to items in data sources, that is, the name of the data source in square brackets [ ]  followed by two colons :: and then the name of the item, in square brackets as usual for a query.  

 

eg_datasrc01_09.png

 

We can then write a query using the authors table.

 

SELECT [au_fname], [au_lname], [phone]

FROM [Books MDB]::[authors]

WHERE StringStartsWith([au_lname], 'K');

 

We run the query by pressing the ! button.

 

eg_datasrc01_10.png

 

The Command window automatically switches to the Results tab to show us the result of the query, two records from the authors table which both have an author's last name starting with a capital letter K.

 

Important: The result of a query is not a new table within the project.   If we want to do that we can use a SELECT INTO query such as

 

SELECT [au_fname], [au_lname], [phone]

  into [PHONES]

    FROM [Books MDB]::[authors]

    WHERE StringStartsWith([au_lname], 'K');

 

The above query, formatted to fit into the available space in the illustration, will create a new table called PHONES which contains the two records selected by the query.

 

eg_datasrc01_11.png

 

Running that query will show a result of 2, meaning two records were selected into the new table.

 

eg_datasrc01_12.png

 

To see that new table in the Project pane we can scroll all the way down to the bottom.  

 

We can see that the SELECT INTO query we ran created a new table called PHONES in the project.  Since we did not specify the new table to be created in the Books MDB data source it was created in the main project hierarchy.

 

eg_datasrc01_13.png

 

To make it easier to see the new table, we can close the Books MDB hierarchy.   We can then double-click the PHONES table to open it.

 

eg_datasrc01_14.png

 

The new table does, indeed, contain the two records selected by our query.  The opened table appears in the desktop as a docked tab to the right of the (still open) Command window tab.

Notes

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.

See Also

Getting Started

 

File - Create - New Data Source

 

MDB Microsoft Access

 

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

 

MDB Files in 64-bit Windows  

 

Launch in 32-bit Mode