problem updating from form

This is a multi-part message in MIME format.
--------------060507030806050105060106
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

**

--------------060507030806050105060106
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit







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