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.

 

eg_datasrc01_00.png

 

Launch 32-bit Manifold.   

 

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

 

To provide more room for writing queries in the command pane within the size of illustrations that are convenient for documentation, we will click the X close symbol for the Project pane to close the Project pane.

 

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');

 

To 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 we use View - Panes - Project to open up the Project pane again.   We also collapse the Books MDB hierarchy.

 

eg_datasrc01_13.png

 

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

 

If we double-click open the PHONES table we see that it 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.

 

See Also

Getting Started

 

File - Create - New Data Source

 

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 a New Data Source from a Manifold Image Server - Manifold image server modules are snippets of code which use the Manifold Image Server Interface (ISI) to automatically fetch image tiles from popular image servers like Virtual Earth, Wikimapia, Yahoo!, Google Maps, Yandex and many others. Image servers can provide street maps, overhead satellite imagery, combinations of streets and satellite imagery and other data as well.  Using Manifold Image Servers is one of the most popular Manifold features.

 

Example: Create a New Data Source from a MAP File - Create a new data source from an existing Manifold .map project file.   This is the classic way to nest projects, one calling another, to create libraries of data and projects.   Access to nested projects has effectively zero performance loss and the links within projects take up effectively zero space so we can create huge constellations of data at our fingertips.

 

Example: Create a Data Source within an Existing Data Source - When a data source is writable, for example, if the data source is a Manifold .map file, we can work within that data source as if it were at the top level of our project.   For example, we can create a new data source that is nested within the existing data source.   This example shows how.

 

Example: Modify GPKG Geometry with SQL then Add Drawing - This topic provides a "Hello, World" example that shows a simple, but typical, task involving spatial data.  We will take a country-sized data set in GeoPackage (GPKG) format and change all areas in the data to the boundary lines for those areas and then save those boundary lines as a new table.  We add a spatial index to the table and create a new drawing to visualize the new table.