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