delete all the records in the temporary tables automatically

[mseo]

hi,
I have a form that fills the temporary table in some conditions, so, is there any way to delete all the records in that temporary table dynamically (using functions or on an action

appreciate any suggestions
thank you

[MMcCarthy]

The following statement should work ...

DoCmd.RunSQL "DELETE FROM TableName"

[jimatqsi]

You could put this in the forms close event, supplying the correct table name

dim strSQL as string
strSQL = "Delete * from tablename"
docmd.RunSQL strSQL
 

Jim

[jimatqsi]

Mary, looks like we're on the same wavelength :)

[MMcCarthy]

@jimatqsi
LOL, great minds and all that!

[mseo]

thank you

dim ssql As String
    DoCmd.SetWarnings False
                 ssql = "DELETE * FROM tbl_OrderTEMP"
                 ssql = "DELETE * FROM tbl_OrderDetailTEMP"
                 DoCmd.RunSQL ssql
    DoCmd.SetWarnings True

just one table gets deleted and this table is the first declared table
when I tried to declare another variable to excute the delete of the other table the same result
any suggestion

[MMcCarthy]

@mseo
You need to run the sql each time ...

Dim ssql As String 
 
    DoCmd.SetWarnings False 
 
    ssql = "DELETE * FROM tbl_OrderTEMP"
    DoCmd.RunSQL ssql
 
    ssql = "DELETE * FROM tbl_OrderDetailTEMP" 
    DoCmd.RunSQL ssql
 
    DoCmd.SetWarnings True 

[mseo]

@msquared
thank you
it works fine
I just need to know, if I can Create temp table using vba and after insert the data into it replace the delete line with drop code
I just need to know if it could be done or not
appreciate your help

[MMcCarthy]

@mseo
If you are asking me if you can do this ...

    DoCmd.RunSQL "DROP TABLE tbl_OrderTEMP;"

Then the answer is yes :)

[mseo]

@msquared
thank you again and over again
could be more than one table, if so
the steps would be like:
1- create the temp tables
2- insert data in the base tables or temp tables
3- drop the temp tables

[MMcCarthy]

More or less, there are various different approaches to take from SQL as we've used here to setting up VBA objects using TableDefs. The approach to take depends on what you are doing at the time.