[AccessD] Operation must use an updatable query...and it does.

Charlotte Foust cfoust at infostatsystems.com
Wed Sep 22 15:19:34 CDT 2004


Try adding the Distinctrow keyword to SQL.  

Charlotte Foust


-----Original Message-----
From: Christopher Hawkins [mailto:clh at christopherhawkins.com] 
Sent: Wednesday, September 22, 2004 11:44 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Operation must use an updatable query...and it does.


This is weird.

I'm refitting an Access 2000 app that a client of mine wrote to run his
business.  One of the issues that has recently started happening (after
the client exported all the objects from a corrupted MDB into a fresh
MDB container) is that a query being run from a form triggers the
following error:

"Operation must use an updatable query"

Here is the code that is firing the query:

[code]  

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateNotesCount"
DoCmd.SetWarnings True

[/code]

Primitive, but OK.  I figure the query is written incorrectly or has
recently been changed, and prepare myself to go clean it up/fix it. 
But here's the kicker:

1) This exact code - and this exact query - has been running fine for
years.  I can dig up previous versions of the app with this code and
query in them, and this line of code executes just fine.

2) If I actually go to the queries tab and double-click
qryUpdateNoteCount, it works just fine, even immediately after trying to
execute that code and throwing an error.

So now I'm scratching my head.  This is very perplexing.  This is the
only build of the app in which this line of code errors out.  Yet, I can
manually execute the query just fine in this build.  Even worse, it only
fails on some machines.  Whiskey Tango Foxtrot, over?  This used to work
on every machine in the house.  

Here is the syntax of the query:

[code]

UPDATE Job SET Job.JobNotes = DCount("Note","tblNotes","JobID =" &
[forms]![frmfcod]![jobid])
WHERE (((Job.JobID)=[Forms]![frmFcod]![JobID]));
[/code]

I decompiled, recompiled, compacted & repaired.  No change.

I am considering going through the app and replacing this line of code
with something else to update this field.  But I'm loathe to do so until
I understand why the failure is occurring to start with.

Any ideas?

-Christopher Hawkins, the Great and Terrible-
Respectfully,

Christopher Hawkins
Software Developer
(559) 687-7591
http://www.christopherhawkins.com


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list