Обсуждение: problem updating from form
Hi,
I have a problem that has me stumped.
We have a table that is the main table (tbl_Assemblies) in a production Microsoft Access application. Our users are complaining that sometimes they enter data in the form, move off of the record & then upon returning to the record the data reverts back to what existed in the field prior to the entry. These complaints have recently become more frequent. This is an intermittent problem, but I have been able to replicate the issue consistently on one particular record. There isn't anything unusual about the data.
We are using PostgreSQL 8.1.4 on RHEL 4.1, Microsoft Access 2002 & psqlodbc 8.01.0200 though I have tried psqlodbc 9 with this configuration. I am aware that our postgres major & minor versions are out of date. We are looking at that now.
When we have a problem record, I can update at the table level from Access, but not in the form. I have isolated the table, removing any foreign keys from the table. There aren't any triggers. I created a new Access DB with only the table & a crude form with only the primary key & the work order field. I have played around with the ODBC settings. We have vacuumed, reindexed & performed a full vacuum on the Postgres DB multiple times. None of these actions have had a positive effect. I have looked at pg_stat_activity & pg_locks for open transactions & row locks. I created an identical copy of the table & imported all of the data, about 194,000 records, into it. I saw the same problem results until I removed all, but about 20 records. Only then was I able to get the update from a form to stick on our test record. Incidentally, the production Access DB grows in size rapidly, from 14MB after a repair & compact to 400 MBs a day or two later.
Here is the table structure with the foreign keys removed.
CREATE TABLE "Production_Tracking"."tbl_Assemblies"
(
"BarCode" varchar(12) NOT NULL,
"PartNumber" varchar(32) NOT NULL,
"LRU" varchar(20),
"PartsListRev" varchar(10),
"SerialNumber" float8,
"MTN" varchar(15),
"DocNum" varchar(50),
"Comments" text,
"SupplierNo" varchar(7),
"DrawingNoRev" varchar(10),
"WorkOrder" varchar(8),
"BldStdRev" varchar(15),
"Quantity" int4 NOT NULL DEFAULT 1,
"Salesorder" varchar(8),
"WOQuantity" int4,
CONSTRAINT "pkey_BarCode" PRIMARY KEY ("BarCode")
)
WITHOUT OIDS;
I can provide the ODBC logs if helpful. I've noticed that the logs do not show the xmin field.
Here are the ODBC settings:
Unicode
Disable Genetic Optimizer = True
KSQO(Keyset Query Optimization) = True
Recognize Unique Indexes = True
Use Declare/Fetch = False
ComLog (C:\psqlodbc_xxx.log) = False
Parse Statements = False
Cancel as Free Stmt (Exp) = False
MyLog (C:\mylog_xxxx.log) = False
Unknown Sizes = Maximum
Text as LongVarChar = False
Unknowns as LongVarChar = False
Bools as Char = True
Max Varchar = 254
Max LongVarChar = 8190
Cache Size = 100
SysTable Prefixes: dd_;
Read Only = False
Show System Tables = False
LF <-> CR/LF conversion = False
Updateable Cursors = False
byea as LO = False
Row Versioning = True
Disallow Premature = False
True is -1 = True
Server side prepare = False
use gssapi for GSS request = False
Int8 As = default
Level of rollback on errors = Transaction
I apologize for the length & detail of this message. I would greatly appreciate any help.
Thank you,
Karen
I have a problem that has me stumped.
We have a table that is the main table (tbl_Assemblies) in a production Microsoft Access application. Our users are complaining that sometimes they enter data in the form, move off of the record & then upon returning to the record the data reverts back to what existed in the field prior to the entry. These complaints have recently become more frequent. This is an intermittent problem, but I have been able to replicate the issue consistently on one particular record. There isn't anything unusual about the data.
We are using PostgreSQL 8.1.4 on RHEL 4.1, Microsoft Access 2002 & psqlodbc 8.01.0200 though I have tried psqlodbc 9 with this configuration. I am aware that our postgres major & minor versions are out of date. We are looking at that now.
When we have a problem record, I can update at the table level from Access, but not in the form. I have isolated the table, removing any foreign keys from the table. There aren't any triggers. I created a new Access DB with only the table & a crude form with only the primary key & the work order field. I have played around with the ODBC settings. We have vacuumed, reindexed & performed a full vacuum on the Postgres DB multiple times. None of these actions have had a positive effect. I have looked at pg_stat_activity & pg_locks for open transactions & row locks. I created an identical copy of the table & imported all of the data, about 194,000 records, into it. I saw the same problem results until I removed all, but about 20 records. Only then was I able to get the update from a form to stick on our test record. Incidentally, the production Access DB grows in size rapidly, from 14MB after a repair & compact to 400 MBs a day or two later.
Here is the table structure with the foreign keys removed.
CREATE TABLE "Production_Tracking"."tbl_Assemblies"
(
"BarCode" varchar(12) NOT NULL,
"PartNumber" varchar(32) NOT NULL,
"LRU" varchar(20),
"PartsListRev" varchar(10),
"SerialNumber" float8,
"MTN" varchar(15),
"DocNum" varchar(50),
"Comments" text,
"SupplierNo" varchar(7),
"DrawingNoRev" varchar(10),
"WorkOrder" varchar(8),
"BldStdRev" varchar(15),
"Quantity" int4 NOT NULL DEFAULT 1,
"Salesorder" varchar(8),
"WOQuantity" int4,
CONSTRAINT "pkey_BarCode" PRIMARY KEY ("BarCode")
)
WITHOUT OIDS;
I can provide the ODBC logs if helpful. I've noticed that the logs do not show the xmin field.
Here are the ODBC settings:
Unicode
Disable Genetic Optimizer = True
KSQO(Keyset Query Optimization) = True
Recognize Unique Indexes = True
Use Declare/Fetch = False
ComLog (C:\psqlodbc_xxx.log) = False
Parse Statements = False
Cancel as Free Stmt (Exp) = False
MyLog (C:\mylog_xxxx.log) = False
Unknown Sizes = Maximum
Text as LongVarChar = False
Unknowns as LongVarChar = False
Bools as Char = True
Max Varchar = 254
Max LongVarChar = 8190
Cache Size = 100
SysTable Prefixes: dd_;
Read Only = False
Show System Tables = False
LF <-> CR/LF conversion = False
Updateable Cursors = False
byea as LO = False
Row Versioning = True
Disallow Premature = False
True is -1 = True
Server side prepare = False
use gssapi for GSS request = False
Int8 As = default
Level of rollback on errors = Transaction
I apologize for the length & detail of this message. I would greatly appreciate any help.
Thank you,
Karen