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]

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

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]

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

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]

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

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]

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

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

    DoCmd.RunSQL "DROP TABLE tbl_OrderTEMP;"

Then the answer is yes :)

[mseo]

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

    DoCmd.RunSQL "DROP TABLE tbl_OrderTEMP;"

Then the answer is yes :)

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.