Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Question 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:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryConsolidatePolicies"
    DoCmd.SetWarnings True

    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?

  2. #2
    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
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryConsolidatePolicies"
    DoCmd.SetWarnings True

    currently using SS 2008R2

  3. #3
    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.

    DoCmd.SetWarnings False
    If fExistTable("nameofyourtable") Then
    DoCmd.DeleteObject acTable, "nameofyourtable"
    End If

    DoCmd.OpenQuery "qryConsolidatePolicies"
    DoCmd.SetWarnings True

    ' 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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts