Action query prompt to delete existing table.

[Christopherusly]

I have a short piece of code behind a button:

Private Sub miniwip_Click()
On Error GoTo Err_miniwip_Click

    Dim stDocName As String

    stDocName = "qry_to calcuate costs for est time hours"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    'run first make table query for finance costs and summing to a single line per transaction
    
    stDocName = "qry_tocalculatejobcosts"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    'run second make table query for job costs and summing to a single line per transaction


    stDocName = "qry_earlywarningtime"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    'run third make table query for job costs and summing to a single line per transaction

    stDocName = "rpt_to join two queries data"
    DoCmd.OpenReport stDocName, acPreview

Exit_miniwip_Click:
    Exit Sub

Err_miniwip_Click:
    MsgBox Err.Description
    Resume Exit_miniwip_Click
    
End Sub

to run an action query to make a table, however, on the click event, say for the second time, where the tables already exist i get the following message:

The existing table 'tbl_estimatecost' will be deleted before you run the query.

Do you want to continue anyway

Yes / No and so on...

Is there anyway to add something to the VBA that says select YES continue with the new table, i do not see any scenario where it would be undesirable not to make the new tables.

the alternative would be to have an event on the report close which deleted the tables, again is this possible in VBA (newbie)

Many thanks you guys.

 

[vbaInet]

You can check if the table exists and if it does delete it:

    Dim tdf As TableDef, db as DAO.Database
    
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If tdf.Name = "TableName" Then
            DoCmd.DeleteObject acTable, tdf.Name
            Exit For
        End If
    Next

Then perform your other operation(s).

 

[Christopherusly]

Works a treat thank you very much vbaInet you are a star.

 

[vbaInet]

No problemo Christophe