[AccessD] Table size

Hi Jim

Okay, okay, so everyone here have lost their arms!
Anyway, skipping a sip at the pub, here is how - leaving the "nice" things to someone else.

<code>
Public Sub ListTableSize()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  
  Dim strName As String
  Dim strFile As String
  Dim strPath As String
  Dim lngBase As Long
  Dim lngSize As Long
    
  Set dbs = CurrentDb
  strName = dbs.Name
  strPath = Left(strName, Len(strName) - Len(Dir(strName)))
  
  ' Create empty database for Jim to measure the base file size.
  strFile = strPath & "base" & ".mdt"
  CreateDatabase strFile, dbLangGeneral
  lngBase = FileLen(strFile)
  Kill strFile
  Debug.Print "Base size", lngBase
  
  For Each tdf In dbs.TableDefs
    strName = tdf.Name
    ' Apply some filtering.
    If Left(strName, 4) = "tblS" Then
      strFile = strPath & strName & ".mdt"
      Debug.Print strName, ;
      CreateDatabase strFile, dbLangGeneral
      DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
      lngSize = FileLen(strFile) - lngBase
      Kill strFile
      Debug.Print lngSize
    End If
  Next
  
  Set tdf = Nothing
  Set dbs = Nothing
  
End Sub
</code>

Note that it will not measure linked tables; the function must be run from withing the backend or modified to use the backend database for dbs.

/gustav

>>> jimdettman at verizon.net 01-10-2009 19:16 >>>

  That needs to be modified a tad; a blank DB needs to be created and the
disk size needs to be subtracted after the table is exported into it.

  It would also be nice to have the tables sizes stored in a table.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, October 01, 2009 12:37 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Table size

Hi Arthur

So much talking, so little action ...

<code>
Public Sub ListTableSize()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  
  Dim strName As String
  Dim strFile As String
  Dim strPath As String
    
  Set dbs = CurrentDb
  strName = dbs.Name
  strPath = Left(strName, Len(strName) - Len(Dir(strName)))
  
  For Each tdf In dbs.TableDefs
    strName = tdf.Name
    ' Apply some filtering.
    If Left(strName, 4) = "tblS" Then
      strFile = strPath & strName & ".mdt"
      Debug.Print strName, ;
      CreateDatabase strFile, dbLangGeneral
      DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
      Debug.Print FileLen(strFile)
      Kill strFile
    End If
  Next
  
  Set tdf = Nothing
  Set dbs = Nothing
  
End Sub
</code>