How to delete blank tables from database

[Zdeny]

Hi everyone,

I am solving an issue with MS Access. I have a .mdb database with about 300 tables, but just lets say 10 are containing data. How can I remove blank tables from mdb file? I tried "Compact and Repair database" tools but without any success... Is there any filter for that?

Thanks for help and sorry for my English

 

[DCrake]

first question. How did you get so many tables in the first place.

Highlight a table and press the delete key

 

[Zdeny]

Database is an export from another software (AutoCad Electrical)....

I know how to delete table... but it takes really long time to delete all unnecessary tables one-by-one... Is there any possibility to sort tables by size?

 

[ghudson]

in theory, you could loop through the a tables collection, count the records in the table, if = 0 then delete the table. If you already know the names of the tables you want to keep, you could import those tables into a new database and start fresh.

 

[Zdeny]

That's the problem because I don't know the names of the tables I want to keep...

How can I count the records in the tables without opening them?

 

[vbaInet]

How sure are you that there aren't some empty tables that are not relevant to the working of the db?

 

[jdraw]

That's the problem because I don't know the names of the tables I want to keep...

How can I count the records in the tables without opening them?

I don't think you can count the records in a linked table without opening the table.

Sub RecsInAllTables()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Dim rs As DAO.Recordset
Dim dropstring As String
   On Error GoTo RecsInAllTables_Error

Set db = CurrentDb

For Each tdf In db.TableDefs
 Set rs = db.OpenRecordset(tdf.Name)
 dropstring = dropstring & tdf.Name & "          " & rs.RecordCount & vbCrLf

rs.Close
Next

Debug.Print dropstring

   On Error GoTo 0
   Exit Sub

RecsInAllTables_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ")"
    
    
End Sub

 

[Zdeny]

How sure are you that there aren't some empty tables that are not relevant to the working of the db?

I am 100% sure that I don't need empty tables

Code:

I am sorry but I don't know what to do with this code

 

[ghudson]

That's the problem because I don't know the names of the tables I want to keep...

How can I count the records in the tables without opening them?

use the DCount function

 

[vbaInet]

Well, let's see if this helps:

    Dim db As DAO.Database, tdf As TableDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If Left$(tbl.Name, 4) <> "MSys" And tdf.RecordCount = 0 Then
            DoCmd.DeleteObject acTable, tdf.Name
        End If
    Next