Deleting all empty Tables

[dubs]

Hi all,

ive imported a database into access to work with but find that out of the 450 tables only 150 or so are being used, is there some code that would find and delete tables with no contents?

greg

 

[smozgur]

Hi Greg,

Following script might help. You can use it in VBA (in any office application) or as vbscript file (by saving as filename.vbs by using Notepad and double click on it to run). Remember to change database file path in code (dbpath).

Call DeleteEmptyTables

Sub DeleteEmptyTables()
Dim DAO
Dim dbmain
Dim rcset
Dim tbldef
Dim dbpath
dim i, j
dim tblArr()

 '******* Database file path - Change this as you need ********
  dbpath = "C:\MyFolder\mydbname.mdb"
 '************************************************************

  On Error Resume Next
  
  'Attempt to create DAO object
  Set DAO = CreateObject("DAO.DBEngine.36")
  If Err Then
    Err.Clear
    'Version might be 3.5 instead
    Set DAO = CreateObject("DAO.DBEngine.35")
  End If
  If Err Then
    'No way, DAO is not supported in this PC.
    'Suggestion might be using ADO instead.
    MsgBox "Cannot use DAO in this computer."
    Exit Sub
  End If
  
  'Create DAO database object
  Set dbmain = DAO.OpenDatabase(dbpath)

  If Err Then
  	Msgbox Err.Number & "-" & Err.Description
	Exit Sub
  End if
  
  i=0
  On Error Goto 0

  'Loop in tables
  For j = 0 to dbmain.TableDefs.Count - 1
    Set tbldef = dbmain.TableDefs(j)
    'Make sure if it is not a system table
    If tbldef.Attributes = 0 Then
      'Make sure recordcount is zero
      If tbldef.RecordCount = 0 Then
            'Store Table name
            Redim Preserve tblArr(i)
            tblArr(i)=tbldef.Name
            i=i+1
      End If
    End If
  Next 

  'Delete empty tables
  For j = 0 to i-1
   dbmain.TableDefs.Delete tblArr(j)
  Next

  'Close database
  dbmain.Close
  If i > 0 then
	Msgbox i & " empty table(s) have been deleted."
  Else
	Msgbox "There is no empty tables."
  End if
End Sub

It uses DAO and alternative code could be created with ADO as well. Please try it. And please take a backup of your database file. Script is supposed to delete the empty tables which is not system ones but .. in case something wrong make sure you got a backup .

I hope it helps.
Suat

 

[dubs]

You star!! will have a go with that now!

greg

 

[dubs]

Thats excellent!! thanks very much!!

greg

 

[smozgur]

You're welcome, Greg!
Glad to hear it helps.

Suat