Data Flask

Microsoft Access (Jet SQL)

Data Flask uses the Microsoft Jet OleDb provider to support Access .MDB files. This provider is included with windows.

Jet SQL (Microsoft Access SQL Reference) is used to CREATE and ALTER tables, indexes and keys.

Access 2000 default location for the Microsoft Jet SQL Reference is: JETSQL40.CHM.

Column Type

When creating or modifying a table using OleDb Admin, you can specify a specific Access column type from a list.

access column types
Access provider types

This will be the same text (up to the left paren) that will be provided to the CREATE TABLE command. Additional qualifiers can be added. For example,

DateTime DEFAULT DATE()

will specify that a field defaults to the time the record was appended. The Access provider type will be passed directly to the CREATE TABLE command and retained in the .dfg grid file.

Sample Migration

This sample uses MS SQL Express, Spatial.mdf. The Spatial database can be installed with SqlServerSamples.msi here (External).

Load Spatial.mdf using File/Open Data if the catalog has not already been loaded into Sql Express. Open table Place from the Spatial database. Replace the Data Link with a link to flask.mdb, which was created in the crash course tutorial, for example

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Flask.mdb.

A Write Data command would create a table with:

CREATE TABLE [Place] (
   [HtmID] varchar(25) NOT NULL,
   [PlaceName] VarChar(100) NOT NULL,
   [State] VarChar(2) NOT NULL,
   [Population] Long NOT NULL,
   [Households] Long NOT NULL,
   [LandAreaKm] Long NOT NULL,
   [WaterAreaKm] Long NOT NULL,
   [Lat] Double NOT NULL,
   [Lon] Double NOT NULL
)

and create keys and indexes using:

CREATE INDEX [PK__Place__060DEAE8] ON [Place] ([HtmID]) WITH PRIMARY
CREATE INDEX [Place_Name] ON [Place] ([PlaceName])

After creating the table, Write Data Preview (SQL) would produce

-- updating Place (HtmID, PlaceName, State, Population, Households, LandAreaKm, WaterAreaKm, Lat, Lon)
UPDATE [Place] SET [Population]=20714 WHERE [HtmID]=13538191965195
-- update complete (1 of 22993 rows had changes)

if the population of Marquette, MI is changed to 20714.