469,643 Members | 1,890 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

When queries fail in VBA code, then work when run manually, then work fine in code?

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?
Jun 18 '07 #1
6 5998
Rabbit
12,516 Expert Mod 8TB
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.
Jun 18 '07 #2
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, 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?)
Jun 18 '07 #3
Rabbit
12,516 Expert Mod 8TB
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?
Jun 18 '07 #4
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.


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?
Jun 19 '07 #5
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.
Jun 19 '07 #6
Rabbit
12,516 Expert Mod 8TB
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.
Jun 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.