08-03-04, 15:07 #1Registered User
- Join Date
- Jul 2004
Unanswered: Make-Table Query Fails Unpredictably
I have a problem with a make-table query that sometimes works, and sometimes doesn't.
In a database that deals with insurance coverage, I need to generate a report showing information on claimants, insurance companies (carriers), policies, and several kinds of cost. To get this, I wrote a procedure that generates a table that has multiple cost records per set of claimant+carrier+policy fields. The procedure is executed by a button in a form.
However, for the report I need to consolidate the multiple records to a single record (for each set of claimant+carrier+policy) containing the total cost of each type. So, I made a make-table query, and execute it as:
The problem is that sometimes this works perfectly (producing about 1700 records) but sometimes it produces 0 records. There doesn't seem to be much pattern to whether it works or not. However, I found that when the query is executed by itself, manually as it were, it always works. Also, if I set a breakpoint just before this code and single-step through it, it always works.
The only way I've found to get it to work fairly reliably is to add a delay of 6 or 7 seconds immediately before the code above. Usually it works then, and almost always on the second push of the button.
What could be causing this behavior? And what can i do about it?
08-08-04, 12:48 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
try a few doevents loops before the .OpenQuery to let the OS get on with what it has to do before your code hits it with another task.
dim someLoops as long
for someLoops = 1 to 50 ' experiment with the number of loops 10....10,000
izycurrently using SS 2008R2
08-08-04, 14:03 #3Registered User
- Join Date
- Feb 2004
Having an exisitng table may be causeing the delay. Check for the existiance of the table then Delete it if it exists prior to calling the query. Something like this.
If fExistTable("nameofyourtable") Then
DoCmd.DeleteObject acTable, "nameofyourtable"
' See: How to find out if a table exists in a database for Function fExistTable
Have you considered basing the report on the Query? The query should always produce the output based on the current data at runtime. You would not need to generate a table for the report unless you need to capture a snapshot of the data at a specific point in time to keep the report data.~