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.






