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

  • Thread starter Thread starter Dennis
  • Start date Start date
D

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
 
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"
Click to expand...
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 <--
 
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)
 
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.
Click to expand...
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 <--
 
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.
 
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.
Click to expand...
Sure? What are you trying to achieve?
Incidentally, this behavior never evidenced itself using Access tables; only
with SQL Server ones.
Click to expand...
You may need a server-side cursor. Have you tested this?

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


mfG
--> stefan <--
 
Back
Top