Bizzare delete query problem

[Dave]

I have a single table, no relationship defined and code that has
worked for two years:
DoCmd.OpenQuery "qryFlushRawTemp" Now I'm getting "3086: Cannot
delete from specified table". Although I have modified the db, I have
not changed the portion that updates the records. Thinking it might be
due to those few times that the table could be empty, I changed it to:
sql = "DELETE tmpRawData.* FROM tmpRawData;"
DoCmd.RunSQL sql
No difference. If I run the query manually from the db window, it
deletes all the records as wanted. Here's a real strange one: when it
last failed while testing, I clicked the Run button in the VBE where
code execution stopped, and it ran anyway, deleting the records!
This is Access 2002 SP3 Windows xp Pro
I sure would appreciate some insight or suggestions, but please note
that I said there is no defined relationship on that table, it did
work before, works by running the query, and seems to respond to the
run button in the VBE!

 

[Douglas J. Steele]

Can't tell you why it's suddenly not working, but in my opinion OpenQuery
isn't really the appropriate method here: it's really intended for Select
queries (although why you'd want to open a query is beyond me...)

Try:

CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError

 

[Dave]

Can't tell you why it's suddenly not working, but in my opinion OpenQuery
isn't really the appropriate method here: it's really intended for Select
queries (although why you'd want to open a query is beyond me...)

Try:

CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError

Well then, the VBE help file is misleading because it says the
openquery method runs the openquery action, and the help topic on the
openquery action says it is for action queries. I had considered your
suggestion since I've used that elsewhere but confess I'm not totally
up on the failonerror parameter. In any event, I tried it and got the
same error. So, I created a new db and imported everything into it
except that table (one of many linked tables). I then linked the table
in the new db (not sure if importing into the new db would bring
across any problem that might reside with its link properties) and it
seems to work. I'll compile it as a new mde as before, and hope for
the best. If this does not permanently resolve the issue, I will
rebuild the table in the BE as something could be wrong with it.
Thanks for your suggestion.

 

[John Spencer]

I doubt that the problem was the table. The problem could have been slight
corruption in your VBA code (the compiled version).

I might have tried a decompile (undocumented feature) and recompile on the
code and see if that fixed things. You effectively did that when you imported
everything into a new database.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave]

I doubt that the problem was the table.  The problem could have been slight
corruption in your VBA code (the compiled version).

I might have tried a decompile (undocumented feature) and recompile on the
code and see if that fixed things.  You effectively did that when you imported
everything into a new database.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

A guy at work ran it today; same problem. I ran it from home via
Citrix, it ran OK. This is crazy. If it's not a corrupted table, what
about a network connection issue? But then, why only that table? If
rebuilding the table doesn't fix it, I can only think two other
approaches. Create a new back end or nest error handling statements to
force it, since it seemed to work by forcing it via the vbe run
button. Example:
Original error handling
some code
On error resume next
CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError
CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError
Resume original error handling
rest of code

 

[David W. Fenton]

m:

A guy at work ran it today; same problem. I ran it from home via
Citrix, it ran OK. This is crazy. If it's not a corrupted table,
what about a network connection issue?

If it's running on different PCs, it's running with different images
of Access and VBA. Also, if it's an MDB/ACCDB and not an MDE/ACCDE,
then it could be recompiling on the fly so that the binary p-code
that's being executed is not identical.

And since that p-code has symbols pointing to external components
(like the VBA DLLs), differences in those outside components could
cause different behavior.

Also, variations in the Jet/ACE version could do the same thing.

Another source of peculiar problems could be video drivers, believe
it or not!

But then, why only that table? If
rebuilding the table doesn't fix it, I can only think two other
approaches. Create a new back end or nest error handling
statements to force it, since it seemed to work by forcing it via
the vbe run button. Example:
Original error handling
some code
On error resume next
CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError
CurrentDb.QueryDefs("qryFlushRawTemp").Execute dbFailOnError
Resume original error handling
rest of code

Well, that's terrible code. NEVER use On Error Resume Next. Period.
Handle the errror -- you know it's going to happen, so prevent it
from happening. You can't know that only the error you're
anticipating is being thrown away.

 

[a a r o n . k e m p f]

Jet sucks because it randomly corrupts data.

Get rid of Jet and your corruption problems go away.

Move to SQL Server, upsize to ADP.

Thanks

-Aaron

 

[Dave]

If it's running on different PCs, it's running with different images
of Access and VBA. Also, if it's an MDB/ACCDB and not an MDE/ACCDE,
then it could be recompiling on the fly so that the binary p-code
that's being executed is not identical.

And since that p-code has symbols pointing to external components
(like the VBA DLLs), differences in those outside components could
cause different behavior.

Also, variations in the Jet/ACE version could do the same thing.

Another source of peculiar problems could be video drivers, believe
it or not!


Well, that's terrible code. NEVER use On Error Resume Next. Period.
Handle the errror -- you know it's going to happen, so prevent it
from happening. You can't know that only the error you're
anticipating is being thrown away.

I agree that is not an elegant solution. But your advice to "prevent
it from happening" is what I've been trying to do all day long. I can
update the situation by reporting that upon subsequent tries, it
failed on me again (more than once) even after I rebuilt the table
from scratch. If anyone has a suggestion on how to "prevent it" or
"handle the error" it would be much appreciated even if it doesn't
work!

 

[David W. Fenton]

m:

On Sep 22, 4:00 pm, "David W. Fenton"


I agree that is not an elegant solution. But your advice to
"prevent it from happening" is what I've been trying to do all day
long. I can update the situation by reporting that upon subsequent
tries, it failed on me again (more than once) even after I rebuilt
the table from scratch. If anyone has a suggestion on how to
"prevent it" or "handle the error" it would be much appreciated
even if it doesn't work!

I think you're misunderstanding my point. On Error Resume Next has
possible unforeseen consequences and you want to avoid those
consequences. But the way it's being used here is to avoid whatever
errors are caused by executing the query. I don't know exactly which
error you're trying to avoid, but you should get rid of On Error
Resume Next and in the error handler do this:

Select Case Err.Number
Case 1234 ' the error you're going to ignore
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error!"
Resume exitRoutine
End Select

Now, the other alternative is to remove or avoid the condition that
produces the error in the first place. For instance, if the query
you're executing is a MakeTable query, it will throw an error if the
table already exists, so you should check to see if the table
already exists before you run it, and delete it. That way, the error
can't happen. Better still, don't use a MakeTable, but instead,
append the data to an existing persistent table. If your append
creates duplicate records, you can avoid that error by deleting all
the records from the table before appending the records. Or you
could write the query to use a LEFT JOIN to see if records don't
exist in the target table and only append them in that case.

The point is, if there's an error you can anticipate sufficiently
that you're going to use On Error Resume Next, it's an error that
you can either avoid entirely (by removing or avoiding the
error-producing conditions) or you check for the specific error
number and ignore it.

Frankly, I'm not entirely sure what happens with dbFailOnError when
error handling is turned off with On Error Resume Next. Those two
instructions seem to be mutually contradictory, and likely the
..Execute would fail (you told DAO not to do it if any errors were
produced), but you'd never know it failed because you told VBA to
ignore errors and skip on to the next line of code.

 

[Dave]

m:





I think you're misunderstanding my point. On Error Resume Next has
possible unforeseen consequences and you want to avoid those
consequences. But the way it's being used here is to avoid whatever
errors are caused by executing the query. I don't know exactly which
error you're trying to avoid, but you should get rid of On Error
Resume Next and in the error handler do this:

  Select Case Err.Number
    Case 1234 ' the error you're going to ignore
      Resume Next
    Case Else
      MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
         "Error!"
      Resume exitRoutine
  End Select

Now, the other alternative is to remove or avoid the condition that
produces the error in the first place. For instance, if the query
you're executing is a MakeTable query, it will throw an error if the
table already exists, so you should check to see if the table
already exists before you run it, and delete it. That way, the error
can't happen. Better still, don't use a MakeTable, but instead,
append the data to an existing persistent table. If your append
creates duplicate records, you can avoid that error by deleting all
the records from the table before appending the records. Or you
could write the query to use a LEFT JOIN to see if records don't
exist in the target table and only append them in that case.

The point is, if there's an error you can anticipate sufficiently
that you're going to use On Error Resume Next, it's an error that
you can either avoid entirely (by removing or avoiding the
error-producing conditions) or you check for the specific error
number and ignore it.

Frankly, I'm not entirely sure what happens with dbFailOnError when
error handling is turned off with On Error Resume Next. Those two
instructions seem to be mutually contradictory, and likely the
.Execute would fail (you told DAO not to do it if any errors were
produced), but you'd never know it failed because you told VBA to
ignore errors and skip on to the next line of code.

Your method makes sense given that I know what the error is that I'm
trying to handle. If you're not aware of what that error is, you must
have missed the beginning of the thread where I documented it along
with all the circumstances I could think of. To encapsulate, it is
"3086: Cannot delete from specified table" where the running the query
from the db window works, but if fails at run-time sometimes. I am
left with the solution to run the code to delete a second time if it
fails once.
Thanks.

 

[AC]

Your method makes sense given that I know what the error is that I'm
trying to handle. If you're not aware of what that error is, you must
have missed the beginning of the thread where I documented it along
with all the circumstances I could think of. To encapsulate, it is
"3086: Cannot delete from specified table" where the running the query
from the db window works, but if fails at run-time sometimes. I am
left with the solution to run the code to delete a second time if it
fails once.
Thanks.- Hide quoted text -

- Show quoted text -

In a prev post higher up DFenton made the comment that different
machines can be running different versions of access or different dlls
or different <insert a myriad of items here>. I think it would be
worth looking down this track a bit further if you havn't already, as
we had a similar issue to you once (not the same delete error, a
different error) - code worked perfectly for years, suddenly started
failing, worked fine on some machines and not others, worked fine if
ran SQL directly but not always during code execution etc etc.

Turned out a software update somewhere had done something which made
the Access code flakey on some machines, possibly a shared dll
somewhere or an overwritten dll or something (I wasn't part of the
solution team, but I think the MDAC files rings a bell for some
reason). This may not be an option for you, but we basically blitzed
the computer and reinstalled windows and office etc, and the problem
just isappeared overnight never to return.

I know the above doesn't really give you a cause for or solution to
the problem, but i am not sure we ever would have have figured out
what exactly the offending issue was anyway.

Cheers
AndyC

 

[David W. Fenton]

:

Your method makes sense given that I know what the error is that
I'm trying to handle. If you're not aware of what that error is,
you must have missed the beginning of the thread where I
documented it along with all the circumstances I could think of.
To encapsulate, it is "3086: Cannot delete from specified table"
where the running the query from the db window works, but if fails
at run-time sometimes. I am left with the solution to run the code
to delete a second time if it fails once.

But you don't know that it succeeds either time, as you've got both
of them inside the On Error Resume Next.

You need to figure out WHY it's not working the first time, and then
fix that.

 

[David W. Fenton]

m:

Turned out a software update somewhere had done something which
made the Access code flakey on some machines, possibly a shared
dll somewhere or an overwritten dll or something (I wasn't part of
the solution team, but I think the MDAC files rings a bell for
some reason).

MDAC is not used by Access, though components installed with the
MDAC may peripherally affect use of outside libraries like ADO. It
won't have anything at all to do with Jet/DAO.

 

[Access Developer]

Jet sucks because it randomly corrupts data.

Simply not true, no matter how many times Mr. Kempf repeats it.

Get rid of Jet and your corruption problems go away.

Also simply not true.

Move to SQL Server,

May or may not be good advice; but Mr. Kempf never considers the details.
If it were always the proper thing to do, as Mr. Kempf assumes, Microsoft
would not have made such an investment in Office 2010 implementing Access
Web Apps, which use SharePoint lists as the back end.

upsize to ADP.

LOL. ADP is "unofficially deprecated", and no longer recommended by the
Access team at Microsoft. But, Mr. Kempf demonstrates once again how loyal
he can be to an obsolete methodology.

 

[Dave]

In a prev post higher up DFenton made the comment that different
machines can be running different versions of access or different dlls
or different <insert a myriad of items here>.  I think it would be
worth looking down this track a bit further if you havn't already, as
we had a similar issue to you once (not the same delete error, a
different error) - code worked perfectly for years, suddenly started
failing, worked fine on some machines and not others, worked fine if
ran SQL directly but not always during code execution etc etc.

Turned out a software update somewhere had done something which made
the Access code flakey on some machines, possibly a shared dll
somewhere or an overwritten dll or something (I wasn't part of the
solution team, but I think the MDAC files rings a bell for some
reason).  This may not be an option for you, but we basically blitzed
the computer and reinstalled windows and office etc, and the problem
just isappeared overnight never to return.

I know the above doesn't really give you a cause for or solution to
the problem, but i am not sure we ever would have have figured out
what exactly the offending issue was anyway.

Cheers
AndyC

This is all good food for thought and much appreciated. I could
believe a corporate update may be the trigger, but it is not a small
organization and I'm not an IT guy, so I won't go there on this one. I
did think that perhaps a different order of project references (as a
result of importing all objects into new db's over a year of part-time
development) might be a possibility, but Mr. Fenton believes it is not
DAO related. And no, there is no conflict of references such as DAO vs
ADO. He is also correct in that the proper thing to do is figure out
why it doesn't work the first time, but alas, it is an intermittent
failure and I am out of options for now. It has not failed since I
implemented this "dirty band-aid". My plan is to see how long it
functions without failing and go from there. The situation and
"solution" is documented within the code in case I move on. That being
said, I was just informed that the department may be getting out of
the business that this particular function relates to, so there is not
much incentive to work on an intermittent problem such as this. I had
hoped to gain more insight into the workings of things as I continue
to develop my skills, and as usual, this forum has done that even if I
am not 100% successful. Thanks guys!

 

[a a r o n . k e m p f]

please. dude... explain to me how this isn't true?

Get rid of Jet and your corruption problems go away.

Also simply not true.

Jet is the only problem with Access, once people lose the training
wheels and move to SQL Server (directly) all of this tedium goes away.

Just because _ONE_ person at Microsoft doesn't recommend it, that
doesn't mean that it's not the best way to do things.