Re: Ms Access 2000 - Update/Delete fails with Write conflict

Greg,

Thank you for your reply. My comments are mixed in below:

On Tuesday 05 September 2006 22:15, greg(dot)campbell(at)us(dot)michelin(dot)com wrote:
> First, you might want to try pgODBC version 8.2.xxx (Use Control Panel --
> Add/Remove programs to remove the old)
>
I tried this, but it doesn't seem to change the issue. The update is still not
using the primary key while the select statement does. I did restart my
Access database and relink the tables after the update.

> In your log, the UPDATE statement has a WHERE clause that tells us that the
> primary key is not being used for the update.
> yet the SELECT does use the primary key.
>
> This suggest that Access/Jet is not fully understanding the primary key OR
> not always using it when it should.
> I do not have Access 2000, but I do have Access'97 and Access XP. I ran a
> test with a simple table. With the ODBC Administrator, I turned on MyLog
> for my DSN. I found both used the primary key field and the row versioning
> field. For example: UPDATE "machine_id"=200 WHERE "my_id"=8 and xmin=79.
>
I'm not sure about this row versioning field. I have row versioning enabled in
psqlODBC (as was mentioned in one or the other ODBC forum to solve a similar
problem), but I don't know what it is about or what other things should be
configured for it to work. Should you have a good link about this, that would
be welcome.

> I suggest checking what Access thinks your metadata is:
> Use Tools->Analyze->Documenter and select the tables of concern.
> Use the Options button to select "Include For Indexes...", Select Names,
> Fields, and Properties.
> Click OK to run the report. It should report your keys, and for your
> primary key, it should report Primary as True.
>
> If something here is off (data types, primary/unique keys)...you need to
> re-link or drop tables and reattach.
>
A good suggestion ! Unfortunatly, I don't see anything out of the ordinary:
datatypes are as expected, the primary key seems correct, the unique keys (dt
for sure) are indicated as such,...

> If Access does understand your metadata, but is still is generating the
> wrong SQL, it might be something underlying like the Access version, Jet
> Engine, MDAC components.
>
The versions I have are:
- Access 2000 (9.0.3821 SR-1)
- Microsoft DAO 3.6
- Microsoft ActiveX Data Objects 2.1
- MDAC (hotfix) 2.53
- I'm not sure where to find the version of Jet, although I believe it is 4.0

> There are some recommended things to do and avoid when making tables to
> link for using Access such as
> -avoid types Access doesn't understand like int8 (Access has a 4 byte
> maximum on Longs and Doubles).
> -Be careful with TEXT to MEMO and BLOB so they do not map to VARCHARS or
> something strange.
>
> Your table doesn't seem to violate these rules.
> I believe the timestamp field is no longer necessary with modern
> implementation of PostgreSQL and the pgODBC.
>
I tried the timestamp based on old mailing list posts. The most recent one was
dated somewhere in 2003. So maybe this is indeed not needed anymore. I found
a mention of timestamps in the documentation that gets shipped with psqlODBC,
about the precision of the seconds. I -perhaps wrongly- interpreted that as a
suggestion this issue is still to be solved by adding the timestamp. If not,
I'd prefer to remove the timestamp field again. It doesn't simplify things
anyway.

> There is a small possibility that you have a threading problem. A record
> is SELECTed in one thread and another thread tries to open a new database
> connection and update the record. I am afraid I do not recall a solution,
> but seem to remember setting the Jet Connection to never timeout (0), but
> that may have been for a different issue.
>
Also I haven't found how to verify this. Would MyLog reveal this ?

> This all said, you have a table with fields named
> number
> date
> type.
> I have worked across a number of database, and this seems not be a best
> practice if you hope to achieve portability.
>
You are quite right. I was already aware of the poor choice of date as a field
name, the others are new (although obvious when looking back at it). If I get
this Write conflict error out of the way, I intend rename the fields and
propagate the changes throug all the related queries and code (sigh...).

>
> You are doing some of the right steps. I don't know if this will help.
> Except for the driver version I feel like I have not given any specific
> advice, but good luck.
>
Thanks again. Although I haven't fixed this issue yet, you helped me already
with a more general background and I would never have come up with the
Documenter. It will help me further in other debuggings as well.

Regards,

Geert

--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel : +32 479 339 655
Email: info(at)kobaltwit(dot)be