Driver: SQLite ODBC Driver (sqliteodbc.exe)

Language: SQL As Understood By SQLite

Column Types

SQLite Column Type Information.

sqlite column types
sqlite provider types

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

Unique Indexes

If a table that has has a unique index is read, the KEYCOLUMN flag is set on affected columns in the offline file. This can cause problems if the table has multiple unique indexes and the Automatic update key is used. To work around this problem, you can specify a unique column to use as the update key in the Data Link dialog.

Default Values

Default values (including AUTOINCREMENT) are not available immediately on insert. You can Read data to obtain newly inserted default values.

Bookmark and SQLite rowid

If you set the update key to Bookmark, updates will use the SQLite rowid. SQLite restricts an INTEGER PRIMARY KEY to match the row number (even if AUTOINCREMENT is not set). This allows you to use the bookmark as the update key.

Sample Migration SQL

Load Customers from Northwind.accdb, as in the Crash Course Tutorial. Replace the connection string with a link to a sqlite server database, for example Driver={SQLite3 ODBC Driver};Database=C:\data\flask.db. The .db file will be created upon initialization.

To create the primary key with CREATE TABLE, use the Data Link dialog to remove the primary key index. Then use the Grid/Column/Properties dialog to set KEYCOLUMN flag for the CustomerID column.

A Write Data command would create a table with:

CREATE TABLE [Categories] (
[CategoryName] varchar(15) NOT NULL,
[Description] longvarchar(536870910), [Picture] longvarbinary

and create keys and indexes using:

CREATE UNIQUE INDEX [CategoryName] ON [Categories] ([CategoryName])

SQL Offline © 2014 Interscape Corporation