MS Access to PostgreSQL conversion of ICIS databases

From ICISWiki

Recommended process:

1. Create an empty destination PostgreSQL database first. E.g. gms

2. Run the .SQL file on the PostgreSQL database to create tables & indices. E.g. GMS-5.5.sql (The .SQL file is available from ICIS Communication project on http://cropforge.org)

3. Use any of the following tools to transfer data from MS Access:

eva/3 Universal Database Converter:

Pros: 1) Converts a whole database after a few button clicks.

Cons: 1) Requires Java Runtime Environment (you would have to install if you don't have it on your machine).

2) Overwrites the lowercase column names defined in the .SQL file (mentioned in item #2).

AnySQL Maestro (released Feb 2008):

Pros: 1) The character case of column & table names are left unaltered.

2) Not only a conversion tool but also an admin tool for several database engines.

3) When creating database profiles, there's a helpful setting: you have control over the character case of new object names (including table columns).

Cons: Has import wizard, but can process one table at a time only.

4. Make sure that all table names and column names are set to lowercase. Based on Postgres testing done at CRIL, ICIS Windows applications (SetGen, Workbook,etc.) only work if table names and column names are in lowercase letters (regardless of the operating system of the machine on which the ICIS database (whether Central or Local) resides).

5. Using ICIS Applications: To successfully connect to a local ICIS database in Postgres, GRANTs must be given to ICIS users. For example, if the ICIS User/Password is "RBREED"/"RBREED", the administrator must setup a Postgres user with username "RBREED" and password "RBREED", and run a GRANT command for all tables in the database.

Example:

-- 
-- GRANTs for user RBREED on local IRIS training database (Postgresql)
--
GRANT ALL PRIVILEGES ON atributs to "RBREED";
GRANT ALL PRIVILEGES ON bibrefs to "RBREED";
GRANT ALL PRIVILEGES ON changes to "RBREED";
GRANT ALL PRIVILEGES ON data_c to "RBREED";
GRANT ALL PRIVILEGES ON data_n to "RBREED";
GRANT ALL PRIVILEGES ON data_t to "RBREED";
GRANT ALL PRIVILEGES ON datattr to "RBREED";
GRANT ALL PRIVILEGES ON dmsattr to "RBREED";
GRANT ALL PRIVILEGES ON dudflds to "RBREED";
GRANT ALL PRIVILEGES ON effect to "RBREED";
GRANT ALL PRIVILEGES ON factor to "RBREED";
GRANT ALL PRIVILEGES ON georef to "RBREED";
GRANT ALL PRIVILEGES ON germplsm to "RBREED";
GRANT ALL PRIVILEGES ON i_accession to "RBREED";
GRANT ALL PRIVILEGES ON i_bchist to "RBREED";
GRANT ALL PRIVILEGES ON i_cultlist to "RBREED";
GRANT ALL PRIVILEGES ON ims_lot to "RBREED";
GRANT ALL PRIVILEGES ON ims_transaction to "RBREED";
GRANT ALL PRIVILEGES ON institut to "RBREED";
GRANT ALL PRIVILEGES ON instln to "RBREED";
GRANT ALL PRIVILEGES ON level_c to "RBREED";
GRANT ALL PRIVILEGES ON level_n to "RBREED";
GRANT ALL PRIVILEGES ON level_t to "RBREED";
GRANT ALL PRIVILEGES ON listdata to "RBREED";
GRANT ALL PRIVILEGES ON listnms to "RBREED";
GRANT ALL PRIVILEGES ON location to "RBREED";
GRANT ALL PRIVILEGES ON locdes to "RBREED";
GRANT ALL PRIVILEGES ON methods to "RBREED";
GRANT ALL PRIVILEGES ON names to "RBREED";
GRANT ALL PRIVILEGES ON oindex to "RBREED";
GRANT ALL PRIVILEGES ON persons to "RBREED";
GRANT ALL PRIVILEGES ON progntrs to "RBREED";
GRANT ALL PRIVILEGES ON reflinks to "RBREED";
GRANT ALL PRIVILEGES ON scale to "RBREED";
GRANT ALL PRIVILEGES ON scalecon to "RBREED";
GRANT ALL PRIVILEGES ON scaledis to "RBREED";
GRANT ALL PRIVILEGES ON study to "RBREED";
GRANT ALL PRIVILEGES ON tmethod to "RBREED";
GRANT ALL PRIVILEGES ON trait to "RBREED";
GRANT ALL PRIVILEGES ON udflds to "RBREED";
GRANT ALL PRIVILEGES ON users to "RBREED";
GRANT ALL PRIVILEGES ON variate to "RBREED";
GRANT ALL PRIVILEGES ON veffect to "RBREED";