Delet multiple temp tables 1

[kiwieur]

I have several temp tables that are created when we extract data from our legacy system.

Whenever we want to re extract the data these tables need to be deleted first.

I have tried to use the following code I wrote but it does not work

Private Sub DeleteTables()
    Dim curDatabase As Object

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "tmp*"
End Sub

I am not very experienced yet in creating my wn code and any help tp point me in the right direction would be appreciated

Regards

Paul

 

[Remou]

You need to go through the collection of tables. Something like:

For Each tdf in CurrentDB.TableDefs
   If Left(tdf.Name,3)="tmp" Then
       CurrentDB.TableDefs.Delete(tdf.Name)
   End If
Next

The above is just typed, so I hope I have it right.

 

[kiwieur]

Remou,

thanks for the code, i have changed mine to this now

Private Sub DeleteTempTables()

Dim tdf As TableDef


For Each tdf In CurrentDb.TableDefs
   If Left(tdf.Name, 3) = "tmp" Then
       CurrentDb.TableDefs.Delete (tdf.Name)
   End If
Next

End Sub

And it works fine

It now does not matter if we either add a new temp table or remove one the code will hopefully cover all eventualities so long as we use the same naming convention.

A star for you

Regards

paul