Odd problem running a query from VBA (1 Viewer)

[Papa_Bear1]

In Access 365, I often dynamically construct queries in VBA, (e.g., in a variable like "strSQL"), and then run them with DoCmd.RunSQL strSQL.

I've encountered a very odd situation where Access throws Error 3113 when encountering a certain query in a VBA routine, but then upon clicking the Debug button, which then highlights the RunSQL command in Debug mode, it allows me to continue to run it, successfully, without error when I hit the "Play" button (i.e., simply let it continue.)

This makes no sense - as this means there is actually nothing inherently wrong with the query!

So, this should mean there is no troubleshooting to do with the query - since it runs successfully - but only after it "complains" about it. I tried storing the query (rather than constructing it with a variable) and calling that query with DoCmd.OpenQuery qryBlahBlah - but again - no go.

I'm really stumped on this one.
Any ideas/help?
thanks!

 

[conception_native_0123]

Hmmmm ... A very strange problem indeed. Never heard of it.

Perhaps the query that you are referring to has become corrupted in its last object state and there has been some confusion in terms of it being referenced by name? One other thing that I can think of...

Have you tried to use the current database alternative to that run command called database.execute to see if the debugger actually stops the code when using that as an alternative command?

 

[theDBguy]

Hi. When a procedure fails but works fine after a pause in execution, such as when you go to debug mode, it could mean there is a timing issue.

 

[Gasman]

And error 3113 is?

 

[arnelgp]

only you know the query statement, maybe there is something in it anyway.

 

[CJ_London]

error 3113 is field not updateable

 

[Minty]

Is the query affecting a record you might have already edited on a form, before the update is saved?

Really need a bit more information about the process, the table being updated and the code being used.

 

[Papa_Bear1]

Hmmmm ... A very strange problem indeed. Never heard of it.

Perhaps the query that you are referring to has become corrupted in its last object state and there has been some confusion in terms of it being referenced by name? One other thing that I can think of...

Have you tried to use the current database alternative to that run command called database.execute to see if the debugger actually stops the code when using that as an alternative command?

Corrupted query - that is interesting. I would think that compact/repair would resolve something like that - which I've tried - but maybe compact/repair would not solve that.

I did try the currentdb.execute also, and it behaved the same way - seems no matter HOW I try to run that query - it is not happy - initially.

 

[Papa_Bear1]

Hi. When a procedure fails but works fine after a pause in execution, such as when you go to debug mode, it could mean there is a timing issue.

Interesting idea.
I will say, however, that I experimented with the usual "On Error Resume Next" before and "On Error GoTo 0" after thing and then trapped this error and told it to go back and try again. It just errorred over and over -- which is also very strange given that it normally "works" when I hit "go" upon first hitting the Debug mode. Really odd.

 

[Papa_Bear1]

only you know the query statement, maybe there is something in it anyway.

Also a very good point.
Here's yet another strange set of characteristics surrounding this - related to that idea of it actually still being something wrong with the query.

As you point out - I originally assumed it was a problem with the query - as we all would of course!
I then modified my approach - based on a theory of what I thought was wrong with it.
It then ran fine, so I thought I resolved it.
I sent the solution off - only to have the user inform me they were seeing this message (at this same point etc.).
So, it *temporarily* thought it was fixed, but wasn't really.

This happened yet again when I made yet another change to the query.
Worked on first testing - and then didn't on 2nd testing.

I really don't get it...

 

[Papa_Bear1]

Is the query affecting a record you might have already edited on a form, before the update is saved?

Really need a bit more information about the process, the table being updated and the code being used.

Good idea.
I did try a "DoEvents" to see if maybe there were some lingering things undone, but it didn't help.
Perhaps there is some other command I should run to make sure nothing is lingering?

 

[theDBguy]

Interesting idea.
I will say, however, that I experimented with the usual "On Error Resume Next" before and "On Error GoTo 0" after thing and then trapped this error and told it to go back and try again. It just errorred over and over -- which is also very strange given that it normally "works" when I hit "go" upon first hitting the Debug mode. Really odd.

On Error Resume and GoTo are very quick (cpu cycles). Have you tried using a time delay? Hitting the Debug button and then hitting the Play button is very slow compared to On Error or DoEvents.

 

[The_Doc_Man]

Error 3113 is "Field not updateable" - but there is potentially more to it than this. What is the EXACT text of the error message? The generic lookup we can do for 3113 omits something that Access might tell you at run time that is far more specific.

 

[Minty]

Good idea.
I did try a "DoEvents" to see if maybe there were some lingering things undone, but it didn't help.
Perhaps there is some other command I should run to make sure nothing is lingering?

I think it would really help if you can post up the code creating and running the problem query, and the data structure of the table the query is running against.
Without it, we are all stabbing in the dark with a rubber mallet.

 

[conception_native_0123]

Corrupted query - that is interesting. I would think that compact/repair would resolve something like that - which I've tried - but maybe compact/repair would not solve that.

access is notorious for corruption. and everyone here will tell you that. that is why migrating away from it is sometimes the wisest move you can possibly make.

 

[CJ_London]

access is notorious for corruption. and everyone here will tell you that.

don't think they will. they may say it is down to not following the very simple rules required to avoid corruption. The number of users is increasing and MS are investing heavily in development of new features, although admittedly not always ones that are a real improvement. So suggest keep your misinformed opinions to yourself. Don't even know why you are here with your complete lack of access knowledge and disdain for the product. Suspect it is because this is the only forum that tolerates you.

 

[Papa_Bear1]

On Error Resume and GoTo are very quick (cpu cycles). Have you tried using a time delay? Hitting the Debug button and then hitting the Play button is very slow compared to On Error or DoEvents.

True - I'll try a delay to see if that helps any...

 

[Papa_Bear1]

Error 3113 is "Field not updateable" - but there is potentially more to it than this. What is the EXACT text of the error message? The generic lookup we can do for 3113 omits something that Access might tell you at run time that is far more specific.

True...
The exact message was:

Run-time error '3113':
Cannot update '(expression)'; field not updateable.


I guess that isn't all the much more informative - other than it is referring to an '(expression)' rather than a particular field name.
I'm trying to put together something I can post to show more specifics...

 

[Papa_Bear1]

I think it would really help if you can post up the code creating and running the problem query, and the data structure of the table the query is running against.
Without it, we are all stabbing in the dark with a rubber mallet.

Funny - I had not heard the "with a rubber mallet" add-on before... ;-)
I'll try to hand-jam in something that represents the actual query --- can't really put the entire real thing in here...

UPDATE
tabMain AS M, tabTemp AS T
SET
M.Position = T.[Position],
M.MoveDate = T.[Move Date],
M.RecLastImported = Now()
WHERE
M.Last & M.First = T.Last & M.First AND
T.Ambig_Name = False AND
(
NZ(M.Position,'') <> T.[Position] OR
NZ(M.MoveDate, '1/1/1900') <> T.[Move Date]
);

I know that there should be keys used in a join statement with "ON" etc. - but I can't do that as I don't have control over this data in the temp table (essentially junky data from Excel etc. - we have to limp by using names as a key...)
I know that NZ stuff with the 1/1/1900 is perhaps weird - but it helped handle nulls... I hope that isn't the problem though...

 

[conception_native_0123]

don't think they will. they may say it is down to not following the very simple rules required to avoid corruption.

one thing....then why is that the default response when there are no other answers? why do I hear it over and over endlessly? send a PM. don't want to hijack. thanks. the other thing is that I said SOMETIMES.