Thread: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ?
Hi, I've got an Access-App using PG 8.4 in a LAN. The systems dude said he updated to odbc 8.400 on all clients. Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before. Among others, there are 2 tables sitting in a schema. Their primary keys are sequences. I link those tables into access and the keys are shown as autovalue as expected. When Access tries to add a record, it complains that the relation "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error #7. There are 2 solutions. 1) I move those tables to the public schema and relink within access. 2) I remove the sequence as default ... and reset it again at once. That makes Access happy without relink. Like: ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT 0; ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT nextval('versand.versandinfos_versandinfo_id_seq'::regclass); I can't point a finger to where this issue roots. It's obviously no permission problem since I can enter records all the time with psql or pgAdmin. It appears when I used ALTER TABLE SET SCHEMA. The recent "outbreak" might be triggered by a pg_restore since I sat up a new db-machine yesterday and reimported the whole database.
Re: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ?
From
"Philippe Lang"
Date:
pgsql-odbc-owner@postgresql.org wrote: > Hi, > > I've got an Access-App using PG 8.4 in a LAN. > The systems dude said he updated to odbc 8.400 on all clients. > Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before. > > Among others, there are 2 tables sitting in a schema. > Their primary keys are sequences. > I link those tables into access and the keys are shown as autovalue > as expected. > > When Access tries to add a record, it complains that the relation > "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error > #7. Hi, Have you checked if your ODBC user has enough rights on your sequence "versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that. Regards, ----------------------------------------------------------------------- Philippe Lang Web : www.attiksystem.ch Attik System Email : philippe.lang@attiksystem.ch rte de la Fonderie 2 Phone : +41 26 422 13 75 1700 Fribourg Mobile : +41 79 351 49 94 Switzerland Fax : +41 26 422 13 76
Attachment
Re: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ?
From
Andreas
Date:
Philippe Lang schrieb: > pgsql-odbc-owner@postgresql.org wrote: > >> Hi, >> >> I've got an Access-App using PG 8.4 in a LAN. >> The systems dude said he updated to odbc 8.400 on all clients. >> Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before. >> >> Among others, there are 2 tables sitting in a schema. >> Their primary keys are sequences. >> I link those tables into access and the keys are shown as autovalue >> as expected. >> >> When Access tries to add a record, it complains that the relation >> "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error >> #7. >> > > Hi, > > Have you checked if your ODBC user has enough rights on your sequence > "versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that > By desperation I made GRANT ... TO public. This can't be the solution since I don't touch the sequence in my workaround. I just set the default of the key twice. This affects only the one collumn in the table but not the seq. I found this accidently when I wanted to reduce the error sources some time ago bye cutting the sequence off. As it worked with manually finding the next key, I reattached the sequence as before and expected to get the error again but it was gone. There must be a interference between odbc/access and sequences within schemas. As I mentioned. The problem goes away when I move the table (inluding the seq.) to the public schema and reappeares when I push it in the schema again. Obviously some element in the dataaccess chain "forgets" to store or use the schema to address the seq.