Tables

Tables show data organized into rows and columns. Every row in a table is a record. Every column in a table is a field. We will use the words "row" and "record" interchangeably. We will also use the words "column" and "field" interchangeably.

 

images\tbl_tables_eg01.gif

 

Tables and Drawings

 

Every drawing in Manifold has a table associated with it. Each object in the drawing (that is, every point, line or area object in the drawing) is linked to a row in the table. Drawings have a table even if there is no data saved for each object. In that case, the drawing's table is mostly empty. It has only one field, the object ID field, for each row. The object ID field is used to link each row to its associated object and cannot be edited. It is indicated as the key field with a small key icon in the column name.

 

Deleting an object in a drawing will delete the object's record in the drawing's table. Deleting a record in a drawing's table will delete the associated object in the drawing.

 

Other Tables

 

Tables may also be database tables that are imported into a Manifold project or linked into the project from an external database source. New tables may also be created with Manifold.

 

Tables may be brought into projects from almost any database, even from those that have nothing to do with drawings or maps. We will often work with tables that have no geographic context. We may wish to prepare data for later use together with drawings and maps, or we might simply wish to use Manifold as a general-purpose means of viewing, analyzing, exploring and managing databases.

 

images\sc_tables_nwind_01.gif

 

The illustration above, for example, shows a project with tables imported from the Microsoft Northwind Traders example database shipped with Microsoft Access.

 

images\sc_tables_nwind_02.gif

 

If we open the Customers table we see the same data familiar to many Access users who have worked with this sample database.

 

Tables and Queries

 

Both tables and queries appear as tables when opened in a table window. Manifold has several types of tables that may appear in a project and that are created in different ways.

 

 

Type

How Created

images\icon_table.gif

Ordinary Tables

Created using File - Create - Table.

images\icon_table.gif

 

Tables linked to drawings.

Created whenever the drawing is imported or created. Each object in the drawing will be automatically linked to one row in the table.

images\icon_table.gif

Tables that have been imported into the project.

Created using File - Import - Table from a file in one of the database formats supported by Microsoft Data Access Components (CSV, DBF, MDB, XLS, etc) or an arbitrary data source using the Data Sources dialog. Importing a table brings the entire content of the table into the project.

images\icon_table_linked.gif

Tables that are linked to external files or database providers.

Created using File - Link - Table from a file in one of the database formats supported by Microsoft Data Access Components (CSV, DBF, MDB, XLS, etc) or an arbitrary data source using the Data Sources dialog. Linking a table leaves the data outside of the project where other programs can continue to work with it.

images\icon_sql.gif

Queries

Created using File - Create - Query. Right clicking the query in the Project pane and selecting Open or double clicking the query edits its text.

Right clicking the query and selecting Run runs it and displays the resulting records as a table. Action queries do not return any records.

Queries can reference any table or query in the project, including tables linked from external data sources.

 

Note that all of the above tables can be opened in table windows and manipulated using essentially identical methods. Tables that are linked to drawings can be opened in their own windows just like any other table, and often are. The only difference between tables linked to drawings and other tables is the dynamic tie between drawings and their tables. Every object in a drawing is represented by a record in the table. A selection made in the drawing appears in the table and vice versa. Deleting an object or a record in the table will immediately take effect in the linked component as well. See Drawings and Tables for examples.

 

Importing or Linking Tables

 

Once a table is imported into a Manifold project the table and the data it contains are managed by the Manifold database engine. However, the process of opening external database files or providers and getting data from them into Manifold is managed with connection technologies embedded within Manifold. When external tables are linked into a project (instead of being imported) the desired connection technologies will be used to manage access to those tables.

 

All data access technologies are included even though their capabilities overlap when working with simple files. By providing all methods Manifold provides users with convenience, reliability and flexibility. OLE DB for read/write connections or ADO .NET for read-only connections are recommended for maximum speed.

 

Jet is the database engine used by Microsoft within Microsoft's own Access database products. The File - Import - Table menu choice uses Jet to open simple file types and to import their tables and data into the Manifold project. Providing Jet within Manifold allows users to open simple database file formats that are frequently encountered in Microsoft Office installations.

 

images\dlg_import_table_filestype.gif

 

With the Import dialog, different formats may be opened by simply choosing the desired database type in the Files of type list as shown above. Using Jet also provides high reliability since this is Microsoft's own code used to open simple Microsoft file types that are widely used throughout Office and similar applications.

 

Choosing Data Sources () in the Files of type list will allow us to connect to a data source using the Data Source dialog, which can create a data source with whatever other drivers that are on our system, such as ODBC or OLE DB drivers. Most databases, especially older types such as Btrieve, may have ODBC drivers even if they have not been updated with OLE DB drivers.

 

OLE DB is a newer, faster, more flexible technology that allows general connection to any database system for which there is an OLE DB provider. OLE DB has now superseded the older ODBC methodology.

 

OLE DB or ODBC provide great flexibility, since they are methods of connecting to any database file or provider for which we have an OLE DB or ODBC driver on our system. If we install Microsoft upgrades or new Microsoft products on our computer that install newer OLE DB providers, Manifold automatically will be able to take advantage of the new providers.

 

Microsoft Data Access Components includes the standard suite of Microsoft OLE DB providers, as can be seen in the dialogs used to create a new data source within the Data Source dialog.

 

images\dlg_tbl_oledb_providers.gif

 

There is one OLE DB provider of special interest for legacy database access. The Jet 4.0 OLE DB Provider essentially duplicates the capabilities of Manifold's built-in copy of Jet. Using it is equivalent to using Jet via the File - Import - Table menu choice.

 

ADO .NET is an even newer database access technology than OLE DB and is a standard means of accessing data for applications using the Microsoft .NET Framework. ADO .NET is known for its simplicity and performance; however, it does have the limitation that ADO .NET is generally read-only.

 

Importing vs. Linking Tables

 

Any of the supported connection methods can be used to import tables and their data into projects or to link to tables for which the data remains in external files or providers. Once a table is imported into a project the Manifold database engine takes over all management of the data. Therefore, once a table is imported there is no difference between tables regardless of what connection methodology was used for the import.

 

However, if tables are linked into a project and their data remains in external files or providers, then whichever method was used for the linkage will continue to be used to access the tables and the data they contain. In addition, performance and other characteristics will be subject to the advantages or limitations of whatever file format or provider is being used.

 

Linking to External Tables and Multi-User Applications

 

When linking to an external file or provider used by multi-user applications Manifold will participate correctly in any multi-user accesses that might go on simultaneously from other applications. This is true for all types of connections.

 

For example, if we link to a table in an external SQL Server database some other person or process could simultaneously open that table with SQL Server over our corporate network as well. They could access the table from a different machine and work on it from that other machine even as we continue to work on the same table with Manifold. Any updates or changes done by Manifold will be consistent with any locking or other protocols designed to maintain order in multi-user applications. This allows Manifold users to work with external databases from within Manifold even as they are being used by different applications.

 

Choose the data access method carefully when embarking on demanding multi-user applications. For example, although Jet may be used for multi-user operations with .mdb Access files it is not as robust and reliable a system for multi-user work as is SQL Server. Manifold users can always use the SQL Server Express installation available on the Manifold CD to create highly robust multi-user capable database tables.

 

Linking Drawing Data Attributes to External Tables

 

So far we've seen that every drawing is linked to a table and that the drawing's table is created within the project at the same time the drawing is created. All such tables exist entirely within the project. Some times we would like to link our drawing to an external table that is linked into the project.

 

Perhaps, for example, we have a drawing of European countries and we would like to color those countries with a thematic format that uses our company's sales for each country. If the company's sales are saved in a table in some corporate server it would be nice to link that external table to the drawing so that every time we open the drawing window the colors in the thematic format are taken from the actual sales recorded on that moment in the corporate server.

 

To do this we use a relation to link the drawing's table with an external table. Fields from the external table may then appear within the drawing's table. See the Attaching External Tables to Drawings topic.

 

Tips

 

·      The fastest performance for smaller tables is achieved by importing a table into a Manifold project so the data it contains may be managed directly by Manifold's own database engine. Compared to big-time DBMS packages like Oracle or SQL Server there is no advantage for medium or large tables, and in the case of very large tables it will often be faster to use an external, enterprise-class DBMS like Oracle or SQL Server.

·      Tables that must be shared with other applications should be linked into the project so that their actual data remains outside Manifold where it is easy for other applications to reach the data without needing to know anything about Manifold.

·      Use the most direct access method. For example, if the data is in Excel .xls files, open them directly using Jet via File - Import - Table. Don’t use the OLE DB Provider for ODBC Drivers to open an Access ODBC driver that is then used to open the .xls file!

·      Tables in Manifold are limited to 4 gigabytes of size. Tables linked from external DBMS packages can be unlimited in size.

·      Learn to use the Transform toolbar for fast edits and management of tables.

·      Read the Queries topics as well as all the topics in the tables Help book.

·      Modern Windows edition (2000 and subsequent) users can drag and drop columns in tables to move them left / right.

·      By default, table windows do not use horizontal or vertical gridlines between rows and columns. Gridlines may be turned on in Tools - Options .

·      From the context menu for table columns (right click onto a table column head) choose Best Fit, Best Fit All or Best Fit Titles to set width of table columns automatically.

·      Manifold tables stored within a project do not support NULL values; however, tables or queries that are linked into a project will support NULL values if their originating data provider supports NULL values. Such columns are said to be NULL-able.

 

See Also

 

·      See the Working with Tables topic for an introduction to table controls, highlighting methods and other user interface issues.

·      ViewBots are important analytic instruments often used with tables. See the ViewBots topic for examples of ViewBot usage.

·      Forms and their associated scripts are important tools when customizing operations involving tables.

·      Hyperlinks for examples of using URL columns in tables to launch browser sessions when an object in a drawing is double-clicked.

·      See the Data Source dialog topic for information on connecting to data sources more sophisticated than simple file types.

 

Examples Used

 

Many of the illustrations for tables are screen shots made using the Nwind.mdb sample database provided by Microsoft. This is a version of the Northwind.mdb sample database distributed with Access and thus familiar to many Microsoft Office users. Nwind.mdb is provided on the Manifold System CD as a sample.