Re: Access 97/Postgres migration

From: Patrick Dunford <a47xxy(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Access 97/Postgres migration
Date: 2001-04-16 11:45:34
Message-ID: MPG.1545a284e9e827a0989d55@news.clear.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, 14 Apr 2001 02:41:00 +0000 (UTC) AD in
comp.databases.postgresql.questions, Eric G. Miller said:

>On Wed, Apr 11, 2001 at 03:18:38PM +0000, ZHU Jia wrote:
>> Hi there,
>>
>> we are considering using Postgres as our new backend DB. But we have a
>> rather complicated Access application which we need to migrate. The
>> idea is to export all the tables from Access to Postgres, then link
>> them back using ODBC so that the Access interface will remain
>> untouched. I just wonder how it would work with the auto_increment
>> data type of Access, I've read that Postgres has the data type
>> "Serial" but it doesn't seem that I can insert a value into it because
>> it should be generated automatically. Now the problem is how can I
>> convert the existing IDs (primary key) to serial? And would this
>> setup work well at all? Is there anything I should keep in mind from
>> the beginning? Any hints or tips would be highly appreciated, and
>> many thanks in advance!

Using the serial type with MS Access is fraught with potential problems -
you will get "#deleted" when a record is posted because of the way access
works.

I suggest the following:
1. Use INT4 instead
2. Create a sequence in Postgres and set the starting number appropriately
3. Write a ODBC passthrough query to retrieve the next value of the
sequence.
4. Write a DAO function to run the query and retrieve the value
5. Call this function in the BeforeInsert event of the form.

This will overcome the potential problem mentioned.

--
=======================================================================
Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/

Then the end will come, when he hands over the kingdom to God
the Father after he has destroyed all dominion, authority and
power.
-- 1 Corinthians 15:24
http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010416
=======================================================================
Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Dunford 2001-04-16 11:47:35 Re: Access 97/Postgres migration
Previous Message Justin Clift 2001-04-16 10:11:52 HOWTO document