[AccessD] Svar: Make-table query question

I do something like this to allow a table to be cleared or appended to.
asuming table is already created.

If MsgBox("Do you want to create a new file table (yes: create, no: 
append)", _
           vbYesNo) = vbYes Then
    DoCmd.RunSQL "DELETE DISTINCTROW TempTable.fieldname  FROM TempTable;"
End If

Gustav Brock wrote:

>Hi Thomas
>
>Couldn't you just empty the table and then append the new records? This way the table exists all the time.
>
>/gustav
>
>  
>
>>>>ewaldt at gdls.com 29-08-06 19:26 >>>
>>>>        
>>>>
>I have a max table (shows the maximum for some calculations for the purpose
>of choosing which records to display from another table) that is recreated
>via a make-table query on a regular basis. This needs to happen because of
>the complexity of queries being run. I'm sure many of you have run into it:
>Access hits its limit in queries of queries of queries, so you have to stop
>part-way and do a make-table query, and then start up again. Anyway, it
>works fine except for the problems below.
>
>A subform is based in part on the max table mentioned above. When a new
>record is created in the main form, and related records are created for the
>subform, the presence of the subform's records is based in part on
>corresponding records in the max table. Unfortunately, since the max table
>is involved in supporting the open forms, it can't be destroyed and
>rebuilt, as is required by a make-table query. This results in the
>subform's being blank, since there are no corresponding records.
>
>One partial solution is to have the user close the form, run a function
>I've created to recreate the tables--there are actually several tables
>involved--and then reopen the form. If that user is the only one logged
>into the app at the time, that works. I've put a button on the toolbar to
>run the function, so it's only a minor inconvenience for the user. However,
>if anyone else is logged in, it's a definite no-go.
>
>Any suggestions for help with this? As usual, I may not have explained it
>clearly enough. Please let me know if that is the case.
>
>Thomas F. Ewald
>FCS Database Manager
>General Dynamics Land Systems
>(586) 276-1256
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada