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!

Recommended Answers

All 6 Replies

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

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.

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

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.

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

    counter = counter + 1

    Set tempDB = Nothing
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.