Re: Access 97/Postgres migration

On Sat, 14 Apr 2001 03:58:35 GMT AD in comp.databases.postgresql.questions,
Steve Jorgensen said:

>I have significant experience using Access with Microsoft SQL Server,
>and the situation is similar. Even when the server has something like
>an Auto-increment or Identity column type, using it will be
>problematic. The best thing you can to is to make your own
>auto-increment system.

The biggest problem with MS Access and Postgres SERIAL primary keys is the
dreaded #Deleted message.

>For each regular table, add a table to the database with a single row
>and 2 columns. The first coumn is a dummy primary key so Access will
>allow you to update the table, and the second column is a counter for
>the next available primary key value for the associated data table.
>To obtain and secure a primary key value for a new record in the
>table, begin a transaction, read the value, save the value incremented
>by one and commit the transaction. If an error occurs during the
>transaction, roll it back, and don't use the value.
>
>If that sounds like a hassle, it is, but it's not as bad as it sounds,
>and it's nothing compared to the hassles you'll encounter if you try
>to do it any other way.

1. Create a sequence
2. Create a ODBC passthrough query to get the NEXTVAL result
3. Write some DAO code to get the result of the query
4. Call the DAO code in BeforeInsert event of the form.

--
=======================================================================
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/