Drop a table starting with ?

[PinoyDev]

Good day!

I just want to ask on how to drop a table. Let say all tables starting with "DailyCashSalesRpt". That means it will drop all tables starting that word.

I use this code but it will only delete one specified table name.. How to use this with like?

sql ="DROP TABLE IF EXIST DailyCashSalesRpt1453"
rs.execute sql

Thank you!

[AleMonteiro]

There's no build-in function that does that.
I think the best approach is to search for the tables you want in the information schema and then delete them by name.

Here's a way: http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string

[jhai_salvador]

Is this purely on SQL or it will be use in VB6?

[AleMonteiro]

It can be done only with SQL but can also be done using VB.
I suggest an stored procedure to delete and execute in VB.

[PinoyDev]

Thank you AleMonteiro,jhai_salvador! but this program uses access as database..

[Reverend Jim]

I think you can get a list of the matching tables by

SELECT MSysObjects.Name AS table_name
  FROM MSysObjects
 WHERE [Name] LIKE "DailyCashSalesRpt*"

Then you can step through the resulting records and build your delete queries. I can't test this out because I only have MS SQL installed, not Access.

[PinoyDev]

I did something like this..and it works..I hope this is the right way to do it..

Dim tempDB As New ADOX.Catalog
Dim tbltoDelete As ADOX.Table
Dim counter As Integer

tempDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=" & App.Path & "\Data.mdb;Jet OLEDB:Database Password=''"

'Loop all tables in the database and remove the specefied table from the Tables collection.
For counter = 1 To tempDB.Tables.Count
    For Each tbltoDelete In tempDB.Tables
        If tbltoDelete.Name Like "Table*" Then
            tempDB.Tables.Delete tbltoDelete.Name
        End If
    Next

    tempDB.Tables.Refresh
    counter = counter + 1
Next

    Set tempDB = Nothing
    End