When queries fail in VBA code, then work manually, then work in code?

[AdmiralXizor]

I have written a function that executes action parameter queries given a name
and a list of parameters.

It splits a string into an array, passes the parameters to the (already saved)
query name, then runs the query using the Execute method, etc., then writes a
record to a table that logs the name of the table and whether the query works
or not.

The function has worked flawlessly for over a year; I have used it in ACC2000
and ACC2002, in multiple implementations, all without error. I used it with
ACC2003 a few months ago and implemented the app as an MDE. No issues.

However, in the last couple of days I have had a weird problem I’ve never
seen before. Certain saved queries have been failing when run by my function.
It is a random occurrence in that it does not seem to have any warning, but
it does only affect a few (at the moment, four) queries.

The weirdest part was the following:

After I had identified the queries that failed, I ran each one once, manually,
typing the same parameters that were passed by the function.

Not only did the query execute with no problem, once I ran the query using
the function again, the query ran fine!!!

I have programmed VBA in Access since Access 97 and have never seen this
happen before. Has anyone else? Any ideas?

 

[Jerry Whittle]

I've never seen this before so my comments are pure speculation. The fact
that it's an MDE takes some wind out of my sails. For instance, it's compiled
so it can't really be a compliation issue. It 'might' be a corruption issue
but I doubt it. Ditto a references issue.

That leaves the possibilities of you stumbling upon a bug; data problems
such as nulls; or you code has a hole in it somewhere.

Also what do you mean by "failing"? Error messages? Not getting expected
results?

 

[AdmiralXizor]

Thanks for responding!

Basically, I set the query as follows (strQueryToRun is the name of the saved
query):

Dim QueryName As QueryDef 'the actual query object
Set QueryName = CurrentDb.QueryDefs(strQueryToRun)

Then I run different statements based on the type of query. For action
queries, I just do the following after passing the parameters to QueryName:

QueryName.Execute

That's when the error happens. At the beginning of the function I set it to
got to a flag called ErrorHandling, which runs a SQL statement (DoCmd.RunSQL),
appending the login name, query name, and error number, if any.

Now this function was written over a year ago, and the queries were deployed
in February. If I run the query manually once, then run the function,
everything works OK! I did it with the MDB, stepping through the code, then
again with the MDE. Didn't change the saved queries or the code in any way...

I've never seen this before so my comments are pure speculation. The fact
that it's an MDE takes some wind out of my sails. For instance, it's compiled
so it can't really be a compliation issue. It 'might' be a corruption issue
but I doubt it. Ditto a references issue.

That leaves the possibilities of you stumbling upon a bug; data problems
such as nulls; or you code has a hole in it somewhere.

Also what do you mean by "failing"? Error messages? Not getting expected
results?

I have written a function that executes action parameter queries given a name
and a list of parameters.

[quoted text clipped - 23 lines]

I have programmed VBA in Access since Access 97 and have never seen this
happen before. Has anyone else? Any ideas?

 

[AdmiralXizor]

One more thing I noticed this afternoon:

* I ran the query once manually. No problem.
* I ran the query via the function. No problem.
* Went into Linked Table Manager and refreshed the table.
* Ran the query via the function. Failed.
* I ran the query once manually. No problem.
* I ran the query via the function. No problem.

Now, just to be sure, I checked to design of the target table and the keys
were intact and everything, so I don't think it's an indexing issue keeping
data from being updated(and if it was, it should fail when running the query
manually, right?)

 

[Chris2]

I have written a function that executes action parameter queries given a name
and a list of parameters.

[quoted text clipped - 23 lines]

I have programmed VBA in Access since Access 97 and have never seen this
happen before. Has anyone else? Any ideas?

I've never seen this before so my comments are pure speculation. The fact
that it's an MDE takes some wind out of my sails. For instance, it's compiled
so it can't really be a compliation issue. It 'might' be a corruption issue
but I doubt it. Ditto a references issue.

That leaves the possibilities of you stumbling upon a bug; data problems
such as nulls; or you code has a hole in it somewhere.

Also what do you mean by "failing"? Error messages? Not getting expected
results?

Thanks for responding!

Basically, I set the query as follows (strQueryToRun is the name of the saved
query):

Dim QueryName As QueryDef 'the actual query object
Set QueryName = CurrentDb.QueryDefs(strQueryToRun)

Then I run different statements based on the type of query. For action
queries, I just do the following after passing the parameters to QueryName:

QueryName.Execute

That's when the error happens. At the beginning of the function I set it to
got to a flag called ErrorHandling, which runs a SQL statement (DoCmd.RunSQL),
appending the login name, query name, and error number, if any.

Now this function was written over a year ago, and the queries were deployed
in February. If I run the query manually once, then run the function,
everything works OK! I did it with the MDB, stepping through the code, then
again with the MDE. Didn't change the saved queries or the code in any way...

AdmiralXizor,

Reading through the text of your exchange with Jerry Whittle, I
noticed that you did not actually say what the error was. (You said,
"That's when the error happens," but not what the error is.)


Sincerely,

Chris O.

 

[AdmiralXizor via AccessMonster.com]

It’s a saved query with a key field (AutoNumber type) and one text field. The
query updates the text field:

UPDATE tbl_ESS SET tbl_ESS.OrderNumber = [ON]
WHERE (((tbl_ESS.ESSID)=[ESS]));

The function (ActionParam) takes the name of the saved query(strQueryToRun)
and its parameters(strParamNamesList, strParamValuesList).

It uses the Split command to put the parameter names and values into an array.
Then it links each parameter value with the correct parameter:

'cycle through parameter values in array
For iCounter = LBound(arrParamName) To UBound(arrParamName)

strParamName = arrParamName(iCounter)
strParamValue = arrParamValue(iCounter)

'link each parameter value with the correct parameter
QueryName.Parameters(strParamName) = strParamValue

Next

If the query is an action query (make-table, update, or delete) it then
simply executes the query:

QueryName.Execute

But it doesn’t run the query – it immediately goes to the ErrorHandling line
(On Error GoTo ErrorHandling).

Again, if I run the query manually first, then run the same query using the
function, it doesn’t go to ErrorHandling – it executes the query.

Jerry Whittle wrote:

I have written a function that executes action parameter queries given a name
and a list of parameters.
[quoted text clipped - 23 lines]
I have programmed VBA in Access since Access 97 and have never seen this
happen before. Has anyone else? Any ideas?

[quoted text clipped - 30 lines]

everything works OK! I did it with the MDB, stepping through the code, then
again with the MDE. Didn't change the saved queries or the code in any way...

AdmiralXizor,

Reading through the text of your exchange with Jerry Whittle, I
noticed that you did not actually say what the error was. (You said,
"That's when the error happens," but not what the error is.)

Sincerely,

Chris O.

 

[AdmiralXizor via AccessMonster.com]

I may have found an answer/workaround:

I put in a message box for the error number generated (MsgBox Err.Number) and
3622 came up.

At the MSDN library (http://msdn2.microsoft.com/en-us/library/bb178074.aspx)
the error description said:

**You must use the dbSeeChanges option with OpenRecordset when accessing a
SQL Server table that has an IDENTITY column. (Error 3622)
The server-generated IDENTITY values cannot be reflected at the client side
unless you use the DAO property dbSeeChanges.**

I added the dbSeeChanges option to the Execute method, and the function ran
with no problems (so far).

It still doesn’t explain why it runs fine after executing the query manually,
though.

 

[Chris2]

Jerry Whittle wrote:

I have written a function that executes action parameter queries given a name
and a list of parameters.
[quoted text clipped - 23 lines]
I have programmed VBA in Access since Access 97 and have never

seen
this

happen before. Has anyone else? Any ideas?

[quoted text clipped - 30 lines]

everything works OK! I did it with the MDB, stepping through the code, then
again with the MDE. Didn't change the saved queries or the code in

any way...

AdmiralXizor,

Reading through the text of your exchange with Jerry Whittle, I
noticed that you did not actually say what the error was. (You said,
"That's when the error happens," but not what the error is.)

Sincerely,

Chris O.

It's a saved query with a key field (AutoNumber type) and one text field. The
query updates the text field:

UPDATE tbl_ESS SET tbl_ESS.OrderNumber = [ON]
WHERE (((tbl_ESS.ESSID)=[ESS]));

The function (ActionParam) takes the name of the saved query(strQueryToRun)
and its parameters(strParamNamesList, strParamValuesList).

It uses the Split command to put the parameter names and values into an array.
Then it links each parameter value with the correct parameter:

'cycle through parameter values in array
For iCounter = LBound(arrParamName) To UBound(arrParamName)

strParamName = arrParamName(iCounter)
strParamValue = arrParamValue(iCounter)

'link each parameter value with the correct parameter
QueryName.Parameters(strParamName) = strParamValue

Next

If the query is an action query (make-table, update, or delete) it then
simply executes the query:

QueryName.Execute

But it doesn’t run the query – it immediately goes to the ErrorHandling line
(On Error GoTo ErrorHandling).

Again, if I run the query manually first, then run the same query using the
function, it doesn’t go to ErrorHandling – it executes the query.

AdmiralXizor,

I understand your explanation to the point you reach at the end, but
you haven't reported on what error message or error number is being
produced when the error handler is invoked.

Without that, I have no real clue as to what is happening.

---------------------------------------------------------------

Well, there are two main possible explanations.

1) Something, somewhere, has changed.

2) You have found a bug.


For #1:

My guess is that it isn't your code, but your data (somehow).

*All of the following would be done on a temporary db, obviously.*

I would create some new tables in your database (you'll design the
properly normalized tables based on the data used in this code).
Their structure would be designed to hold all of the information used
in the above VBA code.

To your above code, in the error handling section, I would add new
*temporary* lines designed to INSERT *all* of the data being used (the
parameters and anything else) into the newly created tables.

Put that into production (as an MDE) so that it will be there when the
next "intermittent error" occurs. (The user should be altered to the
nature of this, or at least I would say, "I've got this special
version in place to help catch the error.")

This way, when your code error's out, you have recorded the exact
state of all the parameters (and anything else).

Then create a newly designed *temporary* subroutine.

Design it so that it takes information out of the newly created tables
and feeds it to your QueryName.Execute line.

If you're in luck, the same parameters that caused the error to occur
will cause it to occur again (this time when you're executing your
manual code), and you'll be that much closer to figuring out what
happened.

This way, you have done your best to re-create exactly what happened.
(Making sure all the data in the normal permanent tables is the same
will be more difficult. However, if you insert a msgbox to "Alert Me
Right Away!", you can then show up when the user calls you and you
make a copy of the db exactly the way it is, and do the manual testing
noted above right away.)

-----------------------------------------

Thinking about this for a moment, I realize that I'm not sure what is
going on with your production version distribution.

You say the production code is erroring out (I assume those are MDEs),
but that your manual code attempts work (I assume that's your test
MDB).

You may just take your database with the working manual code, compile
it to MDE, test it out, and put that into production (with user
knowledge) to see if the error goes away.




For #2:

Contact MS Access Technical Support to report a bug.

There is a remote chance something may have happened as a result of a
service pack or patch.


Sincerely,

Chris O.