OleDb Tutorial

Other tutorials and samples that use OleDb are:

This tutorial uses the C:\data\ as a working folder.

If you are following tutorials in order, you will notice that this tutorial does not present click-by-click.

1) Create Tables and Indexes (Access)

Preparation: This tutorial assumes that C:\Data\Offline.mdb, which was created in the Crash Course tutorial, is available. Similar steps can be followed for most Providers, here we use Access.

Activate the OleDb tab of the data window. Select Access (OleDb) and use the context menu to select Open. Press [OK] to skip the MS JET OLE DB dialog.

Pick [...] to select C:\Data\Offline.mdb.

Press [OK] to create a new Source item in the data window. The item should appear bold to indicate that the source is initialized.

Create Table

From Source - C:\Data\Offline.mdb choose Admin/Add Table . Change the table name to Guild. Use the Data Column button [] to add five columns. Assign each column Data properties as follows:

into the Column Properties dialog. Use the Type [...] to select the data type (figure 3-5). Pick [Advanced] , [...] to assign the ISAUTOINCRIMENT flag. Press [OK] to close the Column Properties dialog after all five columns are added.

Pick Open from the new TABLE - Guild item's context menu.

Pick Edit/Properties and set the number of Rows to 10. [OK].

Copy the contents of the following tab delimited text file into the clipboard.

Select Cell 2,1 (Name, Row 1) and Edit/Paste.

Pick File/Write Data. Notice that nKey is populated automatically.

PrimaryKey

To make nKey a primary key. Pick File/Data Link.

Type into Index Name: PrimaryKey. Check [x] Primary Key . Check [x] Unique. Pick nKey from Columns. Pick [Add], [Create Index] , [OK].

Pick File/Write Data. The Guild Table now has a PrimaryKey. Notice the message Creating index "PrimaryKey" in the Results tab of the History window.

Notice also the message that Update complete (10 of 10 rows had changes) , even though no values changed. The SQL connector attempts to avoid updating records where there are no changes. Further, if no records have changes, the table would not be opened with Update permission at all.

Pick File/Write Data again. Notice that the message is the same, 10 of 10 rows had changes. Say to yourself, "That's Odd". Pick File/Reload Data/Reload . Note that the date changed format. Pick File/Write Data again. 0 of 10 rows had changes.

Complete.

2) Excel, Looking for Trouble

Introduction: This tutorial is very specific to working with .xls files. It shows how to deal with common issues involving columns and tables, and illustrates how OleDb infers name and type, from an array of free text.

By using OleDb, the data contained in an .xls is accessible to SQL Offline. Excel places no restrictions on cells in terms of enforcing that they belong to a named column. Any application that would use OleDb to read .xls sheets, expects named and typed^* columns. Assumptions are made by the OleDb Provider when generating column name and type. They can easily be wrong. This tutorial make a point to illustrate such problems, and shows how to avoid them.

As a sample, download the Florida 5th Grade FCAT scores:

to C:\Data\ to create the file C:\Data\senate_assignments_103-109.xls .

Attempt one: Pick File/Open Data and choose C:\Data\senate_assignments_103-109.xls. Notice that the column names didn't load as desired. The names we would like are in the first row, but much of the first row is blank. Pick Grid/Column/Properties and notice that the Data Type of the columns. Only column names of type WSTR loaded names into the first row. Close the grid file without saving changes.

Attempt two: Pick File/Open Data with Properties and choose C:\Data\senate_assignments_103-109.xls. From the Data Link Properties Dialog, pick the All tab, Pick Extended Properties, and pick [Edit Value...] . Add ;IMEX=1 into the Property Value following Excel 8.0.

Pick [OK], [OK] to close both dialogs and load the data. Notice that

the column names appear, but in the first row. Rows are scanned for type, IMEX=1 caused only one row to be scanned, no numbers were found, only the column names. All columns are now type WSTR.

The best way to gather column names in this case is to introduce a range name using Excel, which will then appear as a table name to SQL Offline.

If you don't have Excel, see the trick below. Load the file C:\Data\senate_assignments_103-109.xls into Excel and define a name for a range. Pick Insert\Name\Define. Type Senate, [Add], into Refers to:

Close the dialog, and save the .xls, and exit Excel.

Note on selecting ranges in Excel: Use keyboard Ctrl+End, followed by Shift+Ctrl+Home to select a full range. Shift+Arrow can then be used to tune first row before defining the range name.

Define Name dialog in Excel.

Pick File/Open Data and choose C:\Data\senate_assignments_103-109.xls. Pick Senate from the Choose Table dialog that now appears.

Without using Excel (trick): You can move the first row to a column name by saving to a temporary table (new sheet) without a header, then reloading with a header. This jogs rows up one at a time. Use File/Data Link to replace IMEX=1 with HDR=NO in [Properties]/Extended Properties , change the table name from Sheet1$ to Temp. Use the column properties dialog to change the first column data name to F1 . Pick File/Write Data to create a sheet with the first row now removed. Remove ;HDR=NO and pick File/Reload All.

Complete. Additional discussion about Excel is discussed in Provider Specific Issues . Additional tutorials.

SQL Offline © 2014 Interscape Corporation