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.
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.
On Wed, 11 Apr 2001 15:18:38 GMT, ZHU Jia <jia.zhu@ains.at> wrote:
>Hi there,
>
>we are considering using Postgres as our new backend DB. But we have a rather complicated Access application which we
needto 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!
>
>regards
>ZHU Jia
>
>