0

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!

Edited by blocker

4
Contributors
6
Replies
33
Views
2 Years
Discussion Span
Last Post by blocker
Featured Replies
  • 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 Read More

0

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.

0

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

0

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.

0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.