Data Link - OleDb

The Data Link Dialog can be loaded from the File Menu, or by picking the small [...] button in the upper left of a grid using the OleDb Agent.

The Data Link holds all the information needed to link to the data source and update the table or query.

Use the data link dialog to set the connection string, define keys and indexes, and indicate how data is to be keyed for update.

Under the [Advanced] button, you may further define how Data Write will perform updates, such as requesting that the table be recreated, or that un-referenced rows are deleted.

OleDb Agent.  Connection String UDL Files.

Updating Data

Data Flask does not stay connected to the data source.  That the spreadsheet cell values are copied and held in the .dfg grid is fairly intuitive.  What is not as visible is the Data Source and Table metadata, which is also copied and held in the .dfg grid.

With apologies for fuchsia just this once, the most important thing to know about writing data from the .dfg spreadsheet to the external database is that:

If you change the name of a table, the database is not immediately modified.  The only thing you are indicating is that the next time the database is updated, a new table will be created or used.  The table with the previous name will not be modified.

If you change the connection string, the grid retains no memory of where the data originally came from.  Removing the connection string from the Data Link removes all reference to a particular data source, and any authorization information that was used to connect to that source.

If you add a data column (using the Column Properties dialog), the column will initially exist only in the grid, not in in the database.  A subsequent Write Data will add the column.

If you delete an index or a key, you are deleting only the definition held in the .dfg grid.  The index or key held in the data remains, unmodified.  If an index definition is removed from the grid, the index will not be removed from the database.  Data Flask will only drop an index as part of rebuilding it.

Any command that must initialize the data source connection string, such as Write Data or Reload Data, immediately un-initializes the data source.  The grid does not hold an open connection.

Simply put: Data Flask allows you to edit the metadata that it holds.  That metadata is held in memory, as part of the spreadsheet, and is not committed to any external database until the Write Data command is issued.

Saving and loading a .dfg file will not affect the data store.  If the .dfg is transported to a location from where the data source cannot be initialized, the grid can be opened and edited.  Both cell values and metadata can be changed.

Row Update Default Behavior
Removing rows from the .dfg grid will not cause corresponding rows to be removed from the data table or query.  You can force this behavior by selecting [x] Delete Unmatched (Outer) Rows (Figure OleDb-5) below.

Column Update Default Behavior
Removing or modifying columns in the .dfg grid will not cause corresponding columns to be removed or modified.  Columns that do not exist will be created, but existing columns will not be modified.  You can force changes to all columns by selecting [x] Recreate Table, or individually by first dropping a column with Admin/Edit Columns from the data window context menu (Figure OleDb-6).

Index Definition Default Behavior
Removing or modifying indexes in the .dfg grid will not cause corresponding indexes to be removed or modified.  You can force definition changes by selecting [x] Create Index, (Figure OleDb-5). 

This default behavior allows you to remove rows, columns, and indexes from the spreadsheet, without affecting the data source, while retaining the ability to update from the subset using File/Write Data.

Table and Query

In addition to the connection string, the OleDb Data Link will includes either a table name, or an Sql SELECT query.

If a table name is used, indexes are supported.

OleDb-4 Data Link Dialog.

To activate the data source using the connection string check [x] Initialized.  When Initialize is checked, table names and indexes from the database will populate the table and index lists.  This gives you the ability to Browse an existing database for index information.  Providers vary greatly in their support of indexes, so it recommended to browse existing tables in order to gather sample index information.  [x] Initialized must be unchecked before you can take information gathered by browsing external data, and assign it in the grid.

Note about the Primary Key:  An index marked as the [x] Primary Key will identify which columns will be matched for update with the next Write Data.  If you have no interest in migrating or redefining indexes, you may delete the definitions.  Removing an index from data flask will never cause the removal of an index from the source.  If you can still choose PrimaryKey as the update type even if you remove the Primary Key Index:  Data Flask will discover the primary key in the target source.

Index

Indexes provide update Keys, constraints, and search optimization.

Indexes are defined by selecting a table, typing in an index name, and using [Add] to add columns.  Once all the columns are in the list, use [Create Index] button to save the index definition into the grid. The index definition will be held in the grid until a future Write Data command is issued, where it will then be created.

As an example, the sequence for adding PrimaryKey to an Access table follows:  Pick File/Data Link to display the OleDb Data Link Dialog.  Type PrimaryKey into the index name and check both Primary Key and Unique.  Select the column that you wish to be the primary key. Press [Add].  Press [Create Index]. The next Write Data command will create the PrimaryKey.  In this example, the index name that Access uses is PrimaryKey by convention.  Other names would work, but that name is recommended for Access.

Editing Note: Changes are applied to an index definition only with [Create Index], [Modify Index], or [Delete Index].  Any changes made to flags or columns of an existing index will not be applied until the modify button is selected, which can feel unintuitive.  The reason for the modify button, as opposed to assuming to make changes immediately, is to allow you to browse into a foreign data source and copy in an index definition, without assuming to commit to it.

Holding Shift with [Delete Index] will remove all index definitions.

As is the case with all values held in the grid, nothing set in the Data Link will affect external data until a subsequent Write Data command.  When the write data command is issued, if the table does not exist, it will be created.  Indexes that do not exist will then be created.  Values held in the grid will then be Updated, Appended and optionally Deleted.

Before exchanging data with an external database, columns must be matched, and a key must be identified (Key Discovery).  Column matching is described, along with assigned ordinals here.  The Update Key type is selected in the Data Link dialog.  Five key types are available.  Primary Key, Unique Column, Unique Index, Bookmark, and Natural.  <Automatic> will select either Primary Key, Unique Column or Unique Index.  Certain providers will be allowed to automatically select bookmarks where it has been determined that the bookmarks survive sessions.

Bookmarks can be used as update keys, although they should be evaluated on a provider specific basis to determine that they do not change between sessions.  Bookmarks are loaded as hidden columns by default.

Natural order means that no key column is used.  This effectively makes the row number the key.

Advanced Update Settings

OleDb-5 Data Link Advanced Update Dialog.

Checking Update Existing Rows Only will stop unmatched rows in the grid from being appended. Append New Rows Only acts opposite to Update Existing Rows Only.  Unmatched rows in the grid will be appended, but no update to existing rows will be made. 

Checking Append Everything will cause all key matching to be ignored.  Every row in the grid will be appended.

Checking Delete Unmatched Rows will cause unmatched rows in the external database to be deleted.

With default settings, columns will be modified with Write Data only if that column is new.  Indexes will be created only if they do not already exist.  Changing a column, for example the width of a STR column from 24 to 32 characters, will not cause the column to be modified at update time (since column definitions are not required to match in the first place, it is difficult to determine if differences should be applied).

The [x] Create Table and [x] Create Index checkboxes can be set if you wish to re-create the table, or re-create the indexes.  Remember to Un-check these settings after the first subsequent Write Data command, unless you desire that the table and index be recreated every update.  Checking create table will drop the table, recreate an empty table, and append grid values.  Checking Create Index will drop and recreate indexes with definitions in the grid's Data Link.  Indexes that are not defined in the grid, will not be dropped.

Data Flask Copyright © 2006 Interscape Corporation