VBA to identify Empty Tables

[MikeL]

Hello,
I inherited an Access Database. Is there VBA to loop thru and identify all Empty tables?

 

[welshgasman]

Loop through all the tabledefs and use the name with a Select for a recordset and check if > 0 ?

This should get you started, but it DELETES records from various tables as that was what was requested by a forum member in another frum.

Sub DeleteRecordsFromTables()
Dim tdf As TableDef
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.TableDefs
    If Left(tdf.Name, 3) = "tbl" Then
        strSQL = "DELETE * FROM " & tdf.Name
        Debug.Print strSQL
        db.Execute strSQL
    End If
Next

End Sub

 

[xenou]

That particular query also relies on the convention of having all table names start with tbl. I have no idea why that is so popular with Access. But the example is on the right track. I think if you print all the table names some of them start with sys or ~ and those would all be system tables. All the rest would be ordinary tables. At any rate, you could turn that into a select count():

For Each tdf In db.TableDefs
    x = DCount("*", "[" + tdf.Name + "]")
    If x = 0 Then
        Debug.Print(tdfName)
    End If
Next

 

[Micron]

I have no idea why that is so popular with Access.

Because it makes perfect sense to have some sort of naming convention for objects. Some use t to denote a table. It's a heck of a lot better than having 3 objects named "OrderDetails" when one is a table, one is a query and the third is a form.

 

[welshgasman]

That particular query also relies on the convention of having all table names start with tbl. I have no idea why that is so popular with Access. But the example is on the right track. I think if you print all the table names some of them start with sys or ~ and those would all be system tables. All the rest would be ordinary tables. At any rate, you could turn that into a select count():

For Each tdf In db.TableDefs
    x = DCount("*", "[" + tdf.Name + "]")
    If x = 0 Then
        Debug.Print(tdfName)
    End If
Next

It was an example?

The member had tables named tbl_, but I had none in my test DB when I tested the code so just used a few tbl named tables, which is how I name them.?
He even had the temerity to complain he had to change it to tbl_, when all it was, was an example.

 

[welshgasman]

I just thought of this

Sub tblCount()
Dim tdf As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb()
For Each tdf In db.TableDefs
    Debug.Print tdf.Name & " " & tdf.RecordCount
Next

End Sub

but it does not work properly for some tables, which appear to be the linked tables.?

Access Input_ImportErrors 774
access input_ImportErrors1 771
Copy Of LTB 1078
DupeTransactions 1800
Files -1
LTB 1078
mp3list -1
MSysAccessStorage 359
MSysAccessXML 0
MSysACEs 457
MSysComplexColumns 0
MSysIMEXColumns 8
MSysIMEXSpecs 1
MSysNavPaneGroupCategories 3
MSysNavPaneGroups 34
MSysNavPaneGroupToObjects 136
MSysNavPaneObjectIDs 131
MSysObjects 151
MSysQueries 232
MSysRelationships 0
MyTable 7
Table1 -1
Table2 -1
Table3 7
tblDates -1
tblDates2 -1
tblDays -1
tblEmployee -1
tblEmployeeDates -1
tbl-fe_version 1
tblMonths -1
tblMp3List 6
tblRandom 0
tbl-version_fe_master -1
tbl-version_master_location 1
tblYears -1
TestTransactions 1800
TestTransactionsDElete 1800
Transactions -1

 

[xenou]

I would assume that the -1's are linked tables, then? Not sure. Perhaps my DCount function would still work on those. But I would probably want to identify linked tables anyway - they may need to be treated differently from local "empty" tables (or maybe not - I would assume the goal is to remove empty tables, while unused linked tables could just be unlinked).

 

[xenou]

Because it makes perfect sense to have some sort of naming convention for objects. Some use t to denote a table. It's a heck of a lot better than having 3 objects named "OrderDetails" when one is a table, one is a query and the third is a form.

I suppose I do have to take this with a grain of salt because I have never had the least bit of difficulty despite never using such a convention. I would say rather that it makes perfect sense not to do it.

 

[Micron]

If nothing else it's helpful to someone who has to follow you. To each their own.

 

[jackd]

Here is a function to identify linked tables:

' ----------------------------------------------------------------
' Procedure Name: IsLinked
' Purpose: To check whether a given Table is Linked or not
'Non-linked tables have a type of 1,
'tables linked using ODBC have a type of 4
'other linked tables(Access) have a type of 6
' Procedure Kind: Function
' Procedure Access: Public
' Parameter TableName (String): The name of the table to check
' Return Type: Boolean
' Author: Jack
' Date: 30-Mar-21
' ----------------------------------------------------------------
Function IsLinked(TableName As String) As Boolean
10    IsLinked = Nz(DLookup("Type", "MSysObjects", _
      "Name = '" & TableName & "'"), 0) <> 1
End Function