Re: Access 97/Postgres migration

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/