Thread: Access 97/Postgres migration
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
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!
Haven't any experience with the ODBC driver via Access, but you can
insert into the serial column provided your value is unique. However,
it may screw up subsequent inserts if you don't update the sequence.
So, instead define your integer key fields as "int4" types. Then bulk
load your data. Then for each table that should be auto numbered,
create a sequence with the start = max("ID") + 1 of the respective
table. Then use "alter table" to set the default to
nextval('mytable_id_seq').
Like:
create table foo (
id int4 PRIMARY KEY,
...
);
copy foo from '/path/to/file' using delimiters '|';
-- or use a script from Access to do inserts...
select max(id) + 1 from foo;
create sequence foo_id_seq start [value from above];
alter table foo alter column foo set default nextval('foo_id_seq');
Rinse, Repeat. ;)
--
Eric G. Miller <egm2@jps.net>
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 > >
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/
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/