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.
In this example, we use a 64-bit Windows
10 system that has had Microsoft's 64-bit Access Database Engine installed.
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.
To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side. In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer. Right-click a pane's tab to change where it is docked. Manifold will remember that new arrangement for our next session.
Launch Manifold, and then choose File - Create - New Data Source. The dropdown menu provides a list of favorites to choose from as well as a More... option.
We choose More... to launch the New Data Source dialog.
In this example we will use a books.mdb file. The .mdb file is a Microsoft Access .mdb file database that contains tables, queries and examples for Chris Fehily's fine books on SQL, which are 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.
If a new data source for the .mdb is
not created as shown below,
that means the Windows system we are using is missing the facilities necessary
for a connection to .mdb. Please
see the Microsoft
Office Formats - MDB, XLS and Friends topic for a solution.
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.
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.
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.
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.
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.
Next we will double-click on the authors table to open it.
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 using the Layers pane to Best Fit Title for all fields. 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.
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.
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.
In real life, of course, we would use a much larger desktop than the small illustration above. We would probably work with an undocked Command Window to make it even larger, leaving plenty of room for the panes in the Query Builder to show SQL elements we can double-click to add to our query.
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.
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.
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.
Running that query will show a result of 2, meaning two records were selected into the new table.
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.
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.
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.
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.
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: 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