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.
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...
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.
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.
Here is some code to do what I suggested. You will need to set a reference to the Microsoft DAO 3.6 Object Library.
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
i = i + 1
Set tdf = Nothing
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).