table size

salemnj1

Board Regular
Joined
May 5, 2004
Messages
87
Is there a way to show the actual memory size of your access tables within a database?

I have a database approaching 2 gigs, and I figured if I could sort the tables by size it would help me target the largest tables to delete.

Any ideas?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Calculate the size of a record by adding up the length of each field. The length is specified by either the field type, or length you gave it (strings) when creating the table. Then multiple that by the number of records in the table.
HTH,
 

salemnj1

Board Regular
Joined
May 5, 2004
Messages
87
THX. What I'm sayinig is I have a ton of tables and I want to sort them somehow so Access will show me the largest of the tables. I don't really have time to go through opening every table, determining # records and adding it up...

Seems weird that they wouldn't provide a calc or something for you to sort the tables if you are trying to find the largest tables in a list of say 100 tables...
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Sorry, I don't know of any way, other than what I suggested. My suggestion, although not explicit, was to write some code to loop though all the tables, then loop though all the fields and add up the field lengths at that point. If you don't have any idea how to do this type of code, it just might be faster to open each table and manually count.
 

salemnj1

Board Regular
Joined
May 5, 2004
Messages
87

ADVERTISEMENT

cool.. might have a friend at work who is good at vba code to help me out with it... thanks for the suggestion...
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
I don't think there is a direct way to get table sizes within Access.

You could do the following though...
1. Create some code that exports each table as text (use the TransferText method) to a designated folder.
2. In Windows Explorer, sort the contents of that folder, descending on file size.

This will give a pretty good indication of relative file sizes. It will also give you an alternate data backup in case of disaster.

Denis
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Here is some code to do what I suggested. You will need to set a reference to the Microsoft DAO 3.6 Object Library.
Code:
Function ExportTablesToText()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim sPATH As String
    Dim i As Integer
    
    i = 1
    sPATH = CurrentProject.Path & "\"
    
    Set dbs = CurrentDb()
    For Each tdf In dbs.TableDefs
        If InStr(1, tdf.Name, "Msys") = 0 Then
            DoCmd.TransferText acExportDelim, , tdf.Name, sPATH & tdf.Name & ".txt", True
        End If
        i = i + 1
    Next tdf
    Set tdf = Nothing
End Function
Denis
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

We had a similar thread on this topic not too long ago - have a read of this thread - whilst it only estimates the table size it will provide the relative table sizes which is pretty much what you are after (ie finding the largest tables).

HTH, Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,133,463
Messages
5,658,918
Members
418,476
Latest member
Tristram_ZX81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top