Table sizes in Kb

[dh_ander]

When I do a "dir" command on a directory I can see the size of each file.

If I look at my tables in access I have no idea how many records I have in each table (or perhaps none at all) and the size is of each table in Kb.
one table might have 10.000 records but just two 3 fields with numbers
and another table might have 5.000 records with lots of text in the memo fields.
Is there some easy way to get some statistics about this?

 

[MyBoo]

Simple answer.... NO

Get out your abacus, go into design mode of each of your tables, identify the field type of each field, figure out how many bytes each field takes up, add this then mulitply by number of records and add 10% for overhead.

Do this for every table in the db.

Not much fun.

 

[dh_ander]

Well the result is as follows, based on some code I found here as well
I have just one problem and I hope somebody knows a workaround.
I am just interested in internal tables.
If I have a linked table the line : Set Rs = CurrentDb.OpenRecordset(strSelect)
will create an error
Any suggestions for exluding linked tables?

Sub WriteFieldNames()
    Dim oFile As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sType As String
    Dim tsize1 As Integer
    Dim tsize2 As Integer
    Dim tsize3 As Integer
    Dim Rs As Recordset
    Dim strSelect As String
    
    
    Set dbs = CurrentDb()
    oFile = CurrentProject.Path & "\Fields.txt"
    MsgBox "Table sizes will be saved in: " & oFile
    
    On Error Resume Next
    Kill (oFile)
    On Error GoTo 0
    
    Open oFile For Output As #1
    For Each tdf In dbs.TableDefs
        strSelect = "Select * From " & tdf.Name
        Set Rs = CurrentDb.OpenRecordset(strSelect)
        If Rs.RecordCount = 0 Then
            tsize3 = 0
        Else
            Rs.MoveLast
            tsize3 = Rs.RecordCount
        End If
      
        If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
        Print #1, Chr(10)
        Print #1, tdf.Name
        tsize1 = 0
        For Each fld In tdf.Fields
            tsize2 = 0
            Select Case fld.Type
                Case 2 'integer
                    sType = "Integer"
                    tsize2 = CInt(fld.Size)
                Case 4 'long
                    sType = "Long"
                    tsize2 = CInt(fld.Size)
                Case 5 'currency
                    sType = "Currency"
                    tsize2 = CInt(fld.Size)
                Case 8 'date/time
                    sType = "Date/Time"
                    tsize2 = CInt(fld.Size)
                Case 10 'text
                    sType = "Text (" & fld.Size & ")"
            End Select
            Print #1, fld.Name & "; " & sType
            tsize1 = tsize1 + tsize2 + fld.Size
        Next fld
        Print #1, "Record size = " & CInt(tsize1) & " Number of Records = " _
         ; CInt(tSze3) & " Tablesize = " & CInt(tsize1 * tsize3)
        End If
    Next tdf
    
    Close 1
        
    MsgBox "Complete"
End Sub

 

[VicRauch]

Open oFile For Output As #1 
    For Each tdf In dbs.TableDefs 
        strSelect = "Select * From " & tdf.Name 
        Set Rs = CurrentDb.OpenRecordset(strSelect)

This is the section of code where you will make the change. Right after the "For Each ..." line, you can test to see if there is connection information for this table def. The property name is Connect.

Open oFile For Output As #1 
    For Each tdf In dbs.TableDefs 
      If Len(Nz(tdf.Connect)) = 0 Then   '***Added Line***
        strSelect = "Select * From " & tdf.Name 
        Set Rs = CurrentDb.OpenRecordset(strSelect)

Be sure to close this If statement. When the Connect string is not there, then the table is a local table.
HTH

 

[dh_ander]

VIC Thanks

Hereby my contribution.
The attached code (put it in a module and use F5 to run) will write all your fields to and sizes to a text file and will give you some overview of what's going on in your database.

all additions and suggestions are welcome

Sub WriteFieldNames()
    Dim oFile As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sType As String
    Dim tsize1 As Long
    Dim tsize2 As Long
    Dim tsize3 As Long
    Dim tsize4 As Long
    Dim tsize5 As Long
    Dim Rs As Recordset
    Dim strSelect As String
    
    tsize5 = 0
    
    Set dbs = CurrentDb()
    oFile = CurrentProject.Path & "\Fields.txt"
    MsgBox "Table sizes will be saved in: " & oFile
    
    On Error Resume Next
    Kill (oFile)
    On Error GoTo 0
    
    Open oFile For Output As #1
    For Each tdf In dbs.TableDefs
        'If table is linked, go to next table
        If Len(Nz(tdf.Connect)) = 0 Then
            'MsgBox (tdf.Name) 'for debugging only
            strSelect = "Select * From " & tdf.Name
            Set Rs = CurrentDb.OpenRecordset(strSelect)
            If Rs.RecordCount = 0 Then
                tsize3 = 0
            Else
                Rs.MoveLast
                tsize3 = Rs.RecordCount
            End If
      
            If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
            Print #1, Chr(10)
            Print #1, tdf.Name
            tsize1 = 0
            For Each fld In tdf.Fields
                tsize2 = 0
                Select Case fld.Type
                    Case 2 'integer
                        sType = "Integer"
                        tsize2 = CInt(fld.Size)
                    Case 3 'Double
                        sType = "Double"
                        tsize2 = CInt(fld.Size)
                    Case 4 'long
                        sType = "Long"
                        tsize2 = CInt(fld.Size)
                    Case 5 'currency
                        sType = "Currency"
                        tsize2 = CInt(fld.Size)
                    Case 6 'memo
                        sType = "Memo"
                        tsize2 = CInt(fld.Size)
                    Case 8 'date/time
                        sType = "Date/Time"
                        tsize2 = CInt(fld.Size)
                    Case 10 'text
                        sType = "Text"
                        tsize2 = CInt(fld.Size)
                End Select
                tsize1 = tsize1 + tsize2
                Print #1, fld.Name & "; " & sType & " (" & CLng(tsize2) & ")"
                tsize1 = tsize1 + tsize2 + fld.Size
            Next fld
            tsize4 = tsize1 * tsize3 / 1024
            Print #1, "Record size = " & CLng(tsize1) & " Number of Records = " _
             ; CLng(tSze3) & " Tablesize = " & CLng(tsize4) & " kB"
            tsize5 = tsize5 + tsize4
            End If
        End If
    Next tdf
    
    Print #1, Chr(10) & "Total of all table sizes excluding linked tables = " & CLng(tsize5) & " kB"
    Close 1
    MsgBox "Complete"
End Sub

 

[Andrew Fergus]

Hi David

Haven't you double counted the variable tsize1? This is the code I am referring to :

                End Select
                tsize1 = tsize1 + tsize2
                Print #1, fld.Name & "; " & sType & " (" & CLng(tsize2) & ")"
                tsize1 = tsize1 + tsize2 + fld.Size
            Next fld

Also, you don't need this line :

And Left(tdf.Name, 1) <> "~"

because that only refers to unsaved SQL queries for forms etc.

Also, try removing this:

Left(tdf.Name, 4) <> "Msys"

and your text file will tell you how much space is being taken up by the system tables too. For my tests I just changed "MSys" to "XSys".

Lastly, in addition to double counting the tsize1, I think you are double counting tsize2 - in your code you assign the fld.size (which is the field size in bytes) to tsize2 and then you have this line in your code :

tsize1 = tsize1 + tsize2 + fld.Size

So I suspect it is triply counting the value of each field size.

If you exclude the MSys objects you end with a small value compared to your database size, but if you include the hidden tables and only add the fld.size values (* the number of records \ 1024) then I believe you end up with a figure which is much closer.

Andrew

 

[Andrew Fergus]

This is the code I used (hacked from yours of course!) :

Private Sub WriteFieldNames()
Dim oFile As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim tsize3 As Long
    Dim Rs As Recordset
    Dim strSelect As String
    Dim MySize As Long, MyTotSize As Long, MyGrandTot As Long
   
    MyGrandTot = 0
   
    Set dbs = CurrentDb()
    oFile = CurrentProject.Path & "\Fields.txt"
   
    On Error Resume Next
    Kill (oFile)
    On Error GoTo 0
   
    Open oFile For Output As #1
    For Each tdf In dbs.TableDefs
        If Len(Nz(tdf.Connect)) = 0 Then
            strSelect = "Select * From " & tdf.Name
            Set Rs = CurrentDb.OpenRecordset(strSelect)
            If Not Rs.EOF Then
                Rs.MoveLast
            End If
            tsize3 = Rs.RecordCount
            Print #1, tdf.Name & vbCrLf & "---------------"
            MySize = 0
            For Each fld In tdf.Fields
                Print #1, fld.Name & ", Size = " & fld.Size
                MySize = MySize + fld.Size
            Next fld
            MyTotSize = (MySize * tsize3) \ 1024
            Print #1, "Record size = " & MySize & " Number of Records = " _
             ; CLng(tsize3) & " Tablesize = " & MyTotSize & " kB"
            MyGrandTot = MyGrandTot + MyTotSize
        End If
        Print #1, vbCrLf
    Next tdf
   
    Print #1, vbCrLf & vbCrLf & "Total of all table sizes excluding linked tables = " & MyGrandTot & " kB"
    Close 1
    MsgBox "Complete"
End Sub

Whilst the text and memo fields are designed to use 2 bytes per character, the default setting for the 'Unicode compression' property is 'Yes' so that the database does not actually use 2 bytes when storing one character for Western European languages (incl English). So I believe it is correct to assume only 1 byte per character for text and memo fields. However, the flaw with the methodology in the code above is that it doesn't account for fields containing null values within each record.

Andrew

[edited spelling error]

 

[dh_ander]

Thanks Andrew

As I said all suggestions are welcome and yes you correctly corrected the size.

If you insist on removing the first msgbox then I suggest you replace the second one with the following statement:

MsgBox "Complete" & Chr(13) & "Results are saved in: " & oFile

Keep up the good work guys
Much appreciated

 

[giacomo]

Here's some code that I use to get Field Types. It will correctly identify AutoNumber fields as such rather than just long integer and it also shows the field size for text field types.

hth,
Giacomo

Public Function FieldType(T As DAO.TableDef, field_name As String) As String
  Dim strReturn As String
         
     strReturn = vbNullString
     Select Case T.fields(field_name).Type
          Case 0
              strReturn = "Invalid"
          Case dbBoolean '1
              strReturn = "True/False"
          Case dbByte '2
              strReturn = "Byte"
          Case dbInteger '3
              strReturn = "Integer"
          Case dbLong '4
            If (T.fields(field_name).Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
          Case dbCurrency '5
              strReturn = "Currency"
          Case dbSingle '6
              strReturn = "Single"
          Case dbDouble '7
              strReturn = "Double"
          Case dbDate '8
              strReturn = "Date/Time"
          Case dbBinary '9
              strReturn = "Binary"
          Case dbText '10
              strReturn = "Text(" & T.fields(field_name).Size & ")"
          Case dbLongBinary '11
              strReturn = "OLE Object"
          Case dbMemo '12
              strReturn = "Memo"
          Case dbGUID '15
              strReturn = "GUID"
          Case dbBigInt '16
              strReturn = "Big Integer"
          Case dbVarBinary '17
              strReturn = "VarBinary"
          Case dbChar '18
              strReturn = "Char"
          Case dbNumeric '19
              strReturn = "Numeric"
          Case dbDecimal '20
              strReturn = "Decimal"
          Case dbFloat '21
              strReturn = "Floating Point"
          Case dbTime '22
              strReturn = "Time"
          Case dbTimeStamp '23
              strReturn = "Time Stamp"
          Case Else
              strReturn = "Unknown(" & T.fields(field_name).Type & ")"
     End Select
     
     FieldType = strReturn
     
End Function