When queries fail in VBA code, then work when run manually, then work fine 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) update queries.

The weirdest part was the following:

After I had identified the queries that failed, I ran each one once, manually, typing in 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?

[Rabbit]

Usually, when queries are failing and I'm not getting an error message, it's because I turned off warnings in the code and didn't turn it back on. Or I'm using error catching without returning the error message.

Your queries could be failing for a number of reasons. You would have to give us the specifics of the queries and code that you're running before we can even begin to help.

[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.Query Defs(strQueryTo Run)

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.Execu te

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, to a table. That's how I know which query fails since I run a few in order.

Now this function was written over a year ago, and the queries were deployed
in February without problems - I basically use this code any time I run an append, update, or delete query, to log/document any changes to data since none of my forms have the tables themselves as the record source.

If I run the query manually once, then run the query via 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 - it just worked normally after manually executing one time.

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?)

[Rabbit]

What is the actual SQL statement that you're trying to run?
What is the SQL statement supposed to do?
What is the SQL statement doing instead?

[AdmiralXizor]

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.OrderNu mber = [ON]
WHERE (((tbl_ESS.ESSI D)=[ESS]));

The function (ActionParam) takes the name of the saved query(strQueryToRun) and its parameters(strParamNamesLi st, strParamValuesL ist).

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(arrParam Name) To UBound(arrParam Name)

strParamName = arrParamName(iC ounter)
strParamValue = arrParamValue(i Counter)

'link each parameter value with the correct parameter
QueryName.Param eters(strParamN ame) = strParamValue

Next

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

QueryName.Execu te

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.

What is the actual SQL statement that you're trying to run?
What is the SQL statement supposed to do?
What is the SQL statement doing instead?

[AdmiralXizor]

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.

[Rabbit]

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.

I'm not sure about that either but I'm not a DAO expert, that's Mary. I'm glad you found your answer though. When using error handling you should have it return the message for troubleshooting purposes.