Tips for Successful Access Imports

Before attempting to import an Access database, it is important to read the following tips to ensure that your database imports into the DataWeb platform the way you intend it to.

�

Valid data table names

Make sure that all of your Access tables have valid data table names. Valid data table names begin with a letter, contain 64 or fewer characters, and contain only letters, numbers, or underscore characters. Any imported tables that do not conform to these data table naming requirements will be imported with modified names.

�

Single-field primary keys

DataWeb data tables support only single-field primary keys. If your Access database includes a table whose primary key consists of two or more fields, a new single-field key will be created in the corresponding data table.

You may want to modify your database schema and relationships in Access and move data as necessary before you import the database into a DataWeb web.

�

No Jet security support

The import wizard does not support importing databases that are secured with Microsoft Jet security. If your database is secured other than by a database password, you should remove security before importing.

�

No binary field support

The DataWeb engine does not support binary fields in data tables. Binary fields (OLE objects) in your Access database will be converted to text fields, and no data will be imported.

A workaround solution is to ftp your relevant binary objects, such as bitmap files, into your Web on the file server and add a text path and filename to your table for every binary object entry. You can then use DataWeb substitution syntax and script in views to display your binary object in the view.

�

Only one-to-many relationships

Note that the import wizard supports only one-to-many relationships. Also, only relationships that include a primary key in one of the two related tables will be imported as lookups.

�

Importing lookups

If you have lookups in your Access database that you want to be imported into your Web, make sure that a one-to-many relationship between the table containing the lookup field and the lookup table is explicitly defined. If there's no relationship defined, your lookup won't be imported.

However, if you have a one-to-many relationship defined but no lookup, a lookup will still be created for you in the resulting data table.

�

Importing AddressBook table

If your Access database contains a table named AddressBook, and you want to import this table into your Web with the same name, the Access table must include two fields of type Text: a field named UserName and a field named Email.

If your AddressBook table doesn't contain these two fields, it will be imported with a modified name.

�

Schema changes

When importing an access database into an existing Web where data tables are overwritten and the schema has changed, check your views and your scripts to make sure they are still working properly. Schema changes can break both your scripts and your views.

�

Overwriting existing tables

When you import an Access database, you can choose to overwrite existing data tables that have the same names as tables in the Access database.

If you choose to overwrite, existing data tables will be overwritten; however, any scripts and views associated with those data tables will be preserved. If the table's schema has changed, verify that your scripts and views still work properly.

If you choose not to overwrite, tables with the same name will be imported with modified names, and your original data tables will be preserved.

�

Fields

Every DataWeb data table includes six system fields: RowId, RowVersion, OwnedBy, Created, ModifiedBy, and Modified. The DataWeb engine uses these fields to keep track of changes to the records in a table.

The RowId field contains an auto-incrementing integer value and serves as the default primary key for a data table. If you import a table that has a single-field primary key, the import wizard will make that field the data table's primary key. If that field is also an auto-incrementing integer, it will replace the RowId field entirely. Otherwise, the RowId field will be created but not designated as the primary key.

If your Access database includes fields with the same names as the Database system fields, and you want to import your data into the DataWeb system fields, make sure that your fields are of the types described in the table below. If they are not, the import wizard will create the default system fields and import your fields with modified names.

If your Access database contains fields of type Currency, or Number fields with the field size set to Single or Double, those fields will consume more storage space in your DataWeb application.

Column Name	Access Type	DataWeb Type	Description
any name	Autonumber	Integer	An auto-incrementing integer field, named RowId by default
RowVersion	Number (field size Integer or Long Integer)	Integer	Indicates the number of times a record has been updated
OwnedBy	Text	User	The user ID of the member who created the record
Created	Date/Time	Date	The date the record was created
ModifiedBy	Text	User	The user ID of the member who last modified the record
Modified	Date/Time	Date	The date the record was last modified