table size

[salemnj1]

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?

 

[VicRauch]

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]

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]

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]

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

 

[SydneyGeek]

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]

Here is some code to do what I suggested. You will need to set a reference to the Microsoft DAO 3.6 Object Library.

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]

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