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 :)

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

Highlight a table and press the delete key

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?

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.

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? :confused:

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

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? :confused:

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

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 :confused:

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? :confused:


use the DCount function

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