Move an Access Database to Mimer SQL Category:
Migration
Introduction
To move a Microsoft Access database to Mimer is a fairly straightforward task. Due to some non-standard data types and identifiers in Access, there may be some problems in the migration of the data. This article discusses how to circumvent these problems.
Description
Below is a comparison between datatypes in Access and Mimer.
Identifiers
SQL identifiers begin with a letter or one of the special characters $ or #, and may only contain letters, digits and the special characters $, # and _. The definition of letters is restricted to the English standard alphabet. National characters are treated as special characters.
There are several reserved words in SQL that cannot be used as identifiers. Note that identifiers are not case sensitive. If you do not follow this standard, you always have to quote the identifiers when you use them in SQL. Access does not follow this standard.
It is possible to export a table from Access to another database, e.g. Mimer SQL. Access sends a CREATE TABLE statement to the data source and copies the data. One problem with this method is that Access quotes the identifiers. Access has to quote them because it does not follow the SQL standard. In addition, Access does not create any primary keys!
Other methods of creating tables and copying data
odbc2mimer
The Windows program odbc2mimer can move a table definition and data from an Access database to Mimer SQL.
Create then Move
Another method is to first create the tables and then move the data.
The desctab program creates CREATE TABLE statements from an ODBC data source and translates special characters into identifiers. The CREATE TABLE statements will contain comments if the data type in Mimer differs from that in Access or if an identifier is changed from non-standard to standard.
When you have created a table in Mimer SQL, you can link to this table in Access and then copy data from the Access table to the Mimer SQL table.
Export
If you export delimited data from Access to a file, you can load the data with the qload program.
Example
Create CREATE TABLE statements from data source access types:
C:\tmp> desctab -daccesstypes -odesctypes.sql
A line in the created file can look as follows:
pm VARCHAR(10000) -- datatype LONGTEXT
See the comparison of data types above. The field ”pm” is an Access memo field and therefore you get a comment in the output file.
Modify the statements and run them to create the Mimer SQL tables.
If you have exported delimited data from Access, load the data as follows:
Mpm Home
Printer friendly
