Search This Blog

20 January 2011

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);
?>
____________________________________________________

1 comment:

  1. If we want to migrate data from MS access databases without programming, what are some of the best data migration tools that we can use? Have you used one that offers the best value for the money?

    ReplyDelete