Data Migration from MS-Access to PostgreSQL - Problems and Solution

I faced tons of problems while migrating my database from Access-97 to PostgreSQL. After trying with few data migration tools, I found that it's good to use PostgreSQL and Access ODBC driver for data migration.

To check with how to setup and use this ODBC driver please go through,
Setup Access ODBC Driver
Install & Setup PostgreSQL ODBC Driver
Migrate Data from Access 97 to PostgreSQL

Using the steps mentioned in Migrate Data from Access 97 to PostgreSQL post, I was able to migrate almost all the tables, still with few tables PostgreSQL ODBC driver fails to transfer the data. Here are some of those errors, which makes PostgreSQL ODBC driver fail,

• "ERROR : character 0x9d of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0xb7 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0xbc of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0x92 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0x96 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR: syntax error at or near :,";
  Error while execution the query (#7)
• The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time.

So to get rid of all this error, I developed a PHP script, which will read data from Access table using Access-ODBC driver, and insert it into PostgreSQL server using connection string.This script will solve all character encoding related issue. But for the error like "The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time." which means the database is corrupted or having some bad sector, it will not allow to copy those records, but for that you can use some debug variable in this script to find out id of those record, then open your access database table, and go to that particular record, move your cursor to each column of that record, and you will come to know which field is creating problem. Once you know the corrupted data, you can move all the data except those problem data
____________________________________________________
 <?php
set_time_limit (0);
$conn=odbc_connect("AccessCon", "" , "");
$pgConn = @pg_connect("host=localhost user=ekta password=ekta dbname=Test");
$rslt = pg_query($pgConn, 'SET CLIENT_ENCODING TO \'WIN1252\';');
if($conn){
  $sql="SELECT * FROM myAccessTable";
  $row=odbc_exec($conn, $sql);
  while(odbc_fetch_row($row)) {
     $field1 = odbc_result($row,1);
     $field2 = odbc_result($row,2);
     $field3 = odbc_result($row,3);
     $strQry = "INSERT INTO myPostgresTable (field1, field2, field3)
                     VALUES('" . addslashes($field1) . "',
                                     '" . addslashes($field2) . "',
                                     '" . addslashes($field3) . "')";
     $rslt1 = pg_query($pgConn, $strQry);
  }
}
$rslt2 = pg_query($pgConn, 'RESET CLIENT_ENCODING;');
odbc_close_all();
pg_close($pgConn);
?>
____________________________________________________

Setup Access ODBC Driver

Perform following steps to setup Access ODBC Driver

• Click Start, Settings, Control Panel
• Switch the control panel to "Classical View", if it's already not in classical view
• In Windows 2000, XP, Vista ODBC located inside Administrative Tools folder. Double click Data Sources (ODBC)
• ODBC Data Source Administrator window displays
• Select System DSN tab and click Add button
• Select "Driver do Microsoft Access (*.mdb)" from the popup opened.
• Then "ODBC Microsoft Access Setup" window displays.
• Type name (We will enter name AccessCon) for Data Source Name and click Select button.
• Select Database window displays. Find your database and click OK button.
• Click OK on Microsoft Access Setup window and OK on ODBC Data Source Administrator window.

How to check if a javascript global variable is defined or not in a javascript function?

We can do it with following code,

if (variablename == undefined){
        variablename = 'XYZ';
}

But for some reason, firefox will throws error with above code. So the solution to that problem is,
if(window.variablename == undefined){
       window.variablename = 'XYZ';
}

How to insert NULL value in PostgreSQL date column?

The simple query is like,
INSERT INTO table (date_field) VALUES (NULL);

But when we try to run this insert query through PHP application, where we don't know whether the variable is NULL or contain some value, it starts giving error. Here we need to do some programming and query manipulation as shown below,

<?
if(empty($dbDate)) $dbDate = '0000-00-00';
$myQry = "INSERT INTO table (date_field) VALUES (CASE $dbDate WHEN 0 THEN NULL ELSE TO_DATE('{$dbDate}', 'YYYY-MM-DD') END)";

/*Remaining code comes here */
?>

How to Enable Macro in Microsoft Office Excel 2007?

• Open Microsoft Office Excel 2007 Click on Office logo at the very Left hand Top
• Then select "Excel Options", select "Trust Centre" on left hand side and click on "Trust Centre Settings"
• Select "Macro Settings" and then select "Enable all Macros", if Macros are also written in Visual Basics programming, put check before "Trust access to the VBA project object model"

How to find Serial number of machine?

To find out the serial number of the machine, go to windows command prompt and type following command on the command prompt,

1. Start > cmd
2. C:\Users\XYZ > wmic csproduct get identifyingnumber,vendor,name

It will give you the output like
IdentifyingNumber    Name          Vendor
L3ZZ896                  5897X7Y     LENOVO

Where IdentifyingNumber is Serial number of machine, Name is Productid and Vendor is the name of manufacturer

CONCAT_WS for PostgreSQL

PostgreSQL do not have function like CONCAT_WS of MySQL, but writing the query as given below we can do much more...


SELECT ARRAY_TO_STRING(ARRAY[initial, firstname, lastname] , ' ') AS user
FROM users

Same as CONCAT_WS function, the above query will ignore fields which are having NULL values, and return the concatenation of remaining fields. But if your fields contains blank value, modify the above query as given below, to get the proper result

SELECT ARRAY_TO_STRING(ARRAY[
CASE title WHEN '' THEN NULL ELSE title END,
CASE firstname WHEN '' THEN NULL ELSE firstname END,
CASE lastname WHEN '' THEN NULL ELSE lastname END], ' ') AS user
FROM users