How to list all tables in a database that are NOT empty?

[Mikael Lindqvist]

Hi,

Ive just imported a quite large database (my company's enterprise system) to
MS Access 2007.

Now that I'm trying to figure out what information goes where I see that A
LOT of tables are empty (no values, just named fields) and since there are
+2000 tables I wonder if there's a quick way (with SQL query) to list ALL
tables that are NOT empty?

Many thanks in advance!

Cheers,
Mikael
Sweden

 

[John Spencer]

I would write a VBA routine that loops through the table collection and gets
a record count. If these are all local tables you can use the recordcount
property of the table.

In version prior to Access 2007 the code might look like the following. One
problem with this is that you really couldn't use debug.Print with that many
tables. You would either have to break this up into parts by limiting the
loop to segments or export the data.

Again, I'm not sure that this will even work in 2007.

Sub getRecCount()
Dim lCount As Long
Dim rCount As Long
Dim dbAny As DAO.Database

Set dbAny = CurrentDb
For lCount = 0 To dbAny.TableDefs.Count - 1
With dbAny.TableDefs(lCount)
If .Name Like "Msys*" Then
Else
rCount = .RecordCount
If rCount > 0 Then
Debug.Print rCount, .Name
End If
End If
End With
Next lCount
End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..