Converting Data to a Device

When you import an .mdb file to the device, it is converted to a format that ADOCE can read and manipulated. Because the internal Windows CE database system is less robust than Microsoft Access, this conversion sometimes results in a loss of information. This topic describes the conversion process in detail, including the types of information that are not converted to the ADOCE database format. The following table shows how data types are mapped from Microsoft Access to ADOCE.

Access type ODBC type Device type Sent to device
Text sql_varchar varchar Yes
Memo sql_longvarchar text Yes
LongInt sql_integer Integer Yes
  sql_bigint Integer Yes
Byte sql_tinyint Smallint Yes
Int sql_smallint Smallint Yes
Single sql_real Double Yes
Double sql_double Double Yes
  sql_float Double Yes
ReplID sql_varbinary Varbinary Yes
Date/Time sql_timestamp Datetime Yes
Currency sql_numeric Double Yes
AutoNumber sql_integer Integer Yes
YesNo sql_bit Boolean Yes
OleObject sql_longvarbinary Varbinary Yes, if field is selected by user; default is unselected.
HyperLink sql_longvarchar text Yes
Lookup sql_varchar varchar Yes

Table Conversion

A table is not converted or copied to the device if any of the following are true:

During conversion, table names are truncated to 31 characters. If you choose not to overwrite tables, and if a table already exists with the truncated name, the last character is deleted and replaced with the numeric character “0.” Values from 1 through 9 are attempted in turn. If all ten tables already exist, then the table is not copied.

Field Conversion

Field names map directly between Access and ADOCE databases because the supported length of 64 characters in .mdb files is the same on a Windows CE–based device.

Index Conversion

All index names are truncated to 64 characters. The only index attribute that is copied is Ascending or Descending. For example, extra Access index information, such as Primary, Unique, and IgnoreNulls is omitted. Index names are not case-sensitive. Only the first field of a multicolumn index is indexed on a device. Only three indexes are allowed. When you reach the limit of three indexes, the rest are skipped.

Indexes are created on a device in three stages. The following table describes the three stages:

Stage Event
Stage 1 Any unique index called PrimaryKey is processed first. If PrimaryKey is a single-field index and the field is being copied, then it is recreated on the device. If PrimaryKey consists of multiple fields, then an index is created for each field that exists on the device. These have 0, 1, and 2 appended to their names, replacing the 64th character of the name, if necessary. If this causes a naming conflict—unlikely because there should be only one PrimaryKey index—then that index is skipped. Boolean fields are not indexed.
Stage 2 Unique indexes are created next. Unique indexes are not created if any of the following are true:
  • The indexed field is not being copied to the device.

  • The index name, when truncated to 64 characters, matches an index name that is already present.

  • The field already has an index on the device.

  • The indexed field is Boolean.
Stage 3 Nonunique indexes are created next. Nonunique indexes are not created if any of the following are true:
  • The indexed field contains more than one field.

  • The indexed field is not being copied to the device.

  • The index name, when truncated to 64 characters, matches an index name that is already present.

  • The field already has an index on the device.

  • The indexed field is Boolean.

These rules are designed to create the most useful indexes on a device. However, because device use differs from desktop computer use, you should examine the resulting indexes and adjust them to suit a particular application.

Conversion Options

Windows CE filters may have user options. The following table shows the option that is provided for converting .mdb data.

Option Default Comment
Overwrite existing tables True If False, then tables are renamed. By default, they are replaced.