How To Find Which Table Is Causing Your Access Database To Be So Big

Some of you, like me, sometimes have the question of “what is causing my Access database to be so big!”

As you may or may not know, the database’s tables are the only objects which store the data.

It’s very frustrating not to know, and just to try and look smart by saying the generic “you are just storing a lot of data.”

Well, “duh”, “Which table is using all the space?”

For example this database is huge (and it really shouldn’t be), it just contains a bunch of tables (the other database objects could be causing such a bloat, but not in this case (maybe use this to see what database objects are the biggest…hmmm, that’s a good idea)).

(and, no, compacting it doesn’t help this one, thanks 😉 )

I went to hunt down some reasons for this and here’s a way to find out.

Before showing the code, basically you have an empty box and put each item in the box 1 by 1, and measure the difference in weight each time you put something in.

???

Create an empty database. Get its size. That’s your “baseline”. Then you put the first item in and subtract the new file size of the database from the new size.

Here’s the code (You can tweak it, to make the result more accurate (like field properties and datatypes), but it will give you a ballpark of what tables are taking up the most space):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Sub DBSizeItemizer()
    ' Table Size Analysis
    Dim ThisDB As DAO.Database
    Dim NewDB As String
   
    Dim lngSizeAft As Long
    Dim lngSizeBef As Long
    Dim strTableListSQL As String

    Dim rstTables As Recordset
    Dim rstFields As Recordset
   
    Dim intFields As Integer
   
    Dim strFieldNames As String
    Dim strFieldList As String
    Dim strCreateTable As String
    Dim strLocalTable As String
   
    Dim qdf As DAO.QueryDef
   
    '1. create new db using DAO
    Set ThisDB = CurrentDb
   
    NewDB = Left(ThisDB.Name, InStrRev(ThisDB.Name, "")) & Replace(Format(Str(Now), "mm-dd-yyyy"), ":", "-") & " " & _
        Mid(ThisDB.Name, InStrRev(ThisDB.Name, "") + 1, Len(ThisDB.Name))
    Application.DBEngine.CreateDatabase NewDB, DB_LANG_GENERAL
   
    '2. open new access database using ADO and late binding
    Set objAccess = CreateObject("Access.application")
   
    Call objAccess.OpenCurrentDatabase(NewDB)
   
    '3. create duplicate tables in new database and notate size change
   
    strTableListSQL = "SELECT  Name FROM MSysObjects WHERE Type=1 AND Flags=0"
    Set rstTables = CurrentDb.OpenRecordset(strTableListSQL)

    Do Until rstTables.EOF
        strLocalTable = rstTables(0)
        strFieldNames = "SELECT * FROM [" & strLocalTable & "] WHERE 1=0"

        Set rstFields = CurrentDb.OpenRecordset(strFieldNames)
        For intFields = 0 To rstFields.Fields.Count - 1
            'Debug.Print rstfields(i).Name
            strFieldList = strFieldList & ", [" & rstFields(intFields).Name & "] VARCHAR(255)"
        Next
'
        If Left(strFieldList, 1) = "," Then
            strFieldList = Mid(strFieldList, 2)
        End If
'
        strCreateTable = "CREATE TABLE [" & strLocalTable & "] (" & strFieldList & ") "
        'Debug.Print strCreateTable

        'create the table
        objAccess.CurrentProject.Connection.Execute strCreateTable

        lngSizeBef = FileLen(NewDB)
       
        'insert records into a remote database using a querydef
       
        strInsertSQL = "INSERT INTO  [" & strLocalTable & "] IN '" & NewDB & "' SELECT * FROM  [" & strLocalTable & "]"
       
        Set qdf = CurrentDb.QueryDefs("qrySize")
        qdf.SQL = strInsertSQL
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qrySize"
        DoCmd.SetWarnings True
       
       
        'minus the size of the db after you add the new records
        'calculate the size of the increase of the database after you just inserted new records
        lngSizeAft = FileLen(NewDB) - lngSizeBef
       
        'insert the size in a new table
       
        CurrentDb.Execute "INSERT INTO tblSizes(TableName,TableSize) VALUES ('" & strLocalTable & "'," & lngSizeAft & ")"
       
        Debug.Print strLocalTable & " - size = " & lngSizeAft

        'clear the field list
        strFieldList = vbNullString
       
       
        'move to the next table
        rstTables.MoveNext
    Loop
   
    Debug.Print ">>> Done! <<<"
    MsgBox "Done!", vbInformation + vbSystemModal, "CheckTableSize"
    'Kill NewDB 'depends on your computer's permissions
   
    'close objects
    rstTables.Close
    rstFields.Close

    Set qdf = Nothing
   
End Sub

The FileLen function returns the value in bytes

So the new database will contain all the tables of the database you are running the function from.

What does this code do?

Basically it creates another database with today’s date.
It creates tables and adds the same data as your source database.
It will insert the name and the calculated size of the table and data in your source database in a table called tblSizes

NOTE: YOU WILL HAVE TO MAKE THE TABLE tblSizes YOURSELF!

ID
TableName
TableSize
DateEntered

…it’s simple to create one.

After you run the function you will get a list of the size of the table objects in tblSizes:

The list of tables and sizes in tblSizes should add up to the size of the new database you created (in bytes).

Then you can sort your size column in descending order to reveal the “space hogs”:

Archive the unneeded records, and delete them, compact your database, and the space will go down!

Let me know if you have any questions.

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , ,