" Row cannot be located for updating." WHAT??

[Dennis]

I'm using Access 2003 FE and a SQL 2000 BE. When attempting to update a
record in a recordset (ADO connection), I get the following error:

"Row cannot be located for updating. Some values may have been changed
since it was last read"

This is patently ridiculous, since I have the reord IN-HAND at that moment,
and no one else has it. I have absolutely no idea about what to do. Any
assistance would be greatly appreciated.

Thanks!

MORE:

CursorLoc = adUseClient
CursorType = adOpenForwardOnly
LockType = adLockOptimistic

 

[Stefan Hoffmann]

hi Dennis,

I'm using Access 2003 FE and a SQL 2000 BE. When attempting to update a
record in a recordset (ADO connection), I get the following error:
"Row cannot be located for updating. Some values may have been changed
since it was last read"

Access/Jet is locating the table by comparing all fields. So when a
field like float is in your recordset, this message is based on a
reounding issue.

To avoid that ensure that every table in your database has a primary key
consisting of precise field types and add an additionl field of the SQL
Server type TIMESTAMP.

This leads to a different behaviour of Access/Jet: it now only uses the
fields of the primar key and the TIMESTAMP field to locate the record.
So unprecise fields are no longer used.

TIMESTAMP is a binary, database wide counter indicating the update order
of all records.

So a query like that on the Server shows the order of changes:

SELECT TableName, ID
FROM
(
SELECT 'table1' AS TableName, ID FROM table1
UNION ALL
SELECT 'table2', ID FROM table2
) Q
ORDER BY timeStampField DESC


mfG
--> stefan <--

 

[Dennis]

I'm not sure I understand your potential solution, though I do appreciate
your response.

I did a lot of reading on the Net yesterday, and basically discovered that
when using a SQL backend, you should not attempt to UPDATE in a RECORDSET. So
what I did was create a stand-alone SQL string to do the UPDATE. I built the
string like you'd write an UPDATE query, then did a DoCmd.RunSql with that
string (using the PK that I had in-hand from the record being
displayed/edited.) Then I did a RecordSet.ReSync, and everything worked like
a charm.

I chalk it up to lesson learned.

Oh, in my research I discovered that this issue is a bug/"feature" of MS SQL
Server/Jet (it also affects other front-end database languages like Foxpro,
so I really think it's in SQL Server), and that MS is aware of the issue.
Problem is, it's been "known" for years, so far with no correction provided.
Nice..... (not)

 

[Stefan Hoffmann]

hi Dennis,

I did a lot of reading on the Net yesterday, and basically discovered that
when using a SQL backend, you should not attempt to UPDATE in a RECORDSET.

This is nonsense. There are circumstances where you want to avoid this,
but this is not a rule of thumb.

Generally you should avoid utilizing server resources you don't need.
But you need a recordset.

btw, are you using an .adp or a .mdb?


mfG
--> stefan <--

 

[Dennis]

Well, you may say it's nonsense, but in this case (and I'm using an MDB),
there is no workaround using an ADO client-side recordset that will work
properly. Since I'm not an ADO internals expert, I am (of course) just
quoting what I'd read in multiple technical venues.

Incidentally, this behavior never evidenced itself using Access tables; only
with SQL Server ones.

 

[Stefan Hoffmann]

hi Dennis,

Well, you may say it's nonsense, but in this case (and I'm using an MDB),
there is no workaround using an ADO client-side recordset that will work
properly.

Sure? What are you trying to achieve?

Incidentally, this behavior never evidenced itself using Access tables; only
with SQL Server ones.

You may need a server-side cursor. Have you tested this?

btw, it would be nice to see your actual code...


mfG
--> stefan <--