如何使用Access VBA获取数据库表的大小？

Great question! Unlike grabbing subform dimensions with Me.Subform_name.Height or Me.Subform_name.Width, getting a table's size requires interacting with Access's underlying database objects or system tables. Here are two reliable methods to do this:

Method 1: Use DAO TableDef Object (Recommended) #

This approach uses the DAO (Data Access Objects) library to directly access table metadata—it’s straightforward and less prone to permission issues compared to system table queries.

Step 1: Optional (but Recommended) - Set DAO Reference #

First, ensure your project references the DAO library for better IntelliSense and error handling:

• Open the VBA editor with Alt + F11
• Go to Tools > References
• Check Microsoft DAO 3.6 Object Library (or the latest version available)

Step 2: VBA Function to Calculate Table Size #

Function GetTableSize(tableName As String) As Double
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim totalBytes As Double
    
    ' Connect to the current database
    Set db = CurrentDb()
    
    ' Fetch the TableDef for your target table
    On Error Resume Next
    Set tdf = db.TableDefs(tableName)
    On Error GoTo 0
    
    If Not tdf Is Nothing Then
        ' Total size = number of pages × bytes per page (includes data + indexes)
        totalBytes = tdf.PageCount * tdf.PageSize
        ' Convert to KB (divide by 1024 again if you want MB)
        GetTableSize = totalBytes / 1024
    Else
        ' Return 0 if the table doesn't exist
        GetTableSize = 0
    End If
    
    ' Clean up objects to avoid memory leaks
    Set tdf = Nothing
    Set db = Nothing
End Function

How to Use It #

Call the function to display the table size in a message box:

MsgBox "Table size: " & Round(GetTableSize("Table_name"), 2) & " KB"

Method 2: Query Access System Tables #

If you prefer using SQL, you can query Access's hidden system tables. Note this requires enabling system table visibility and may need admin privileges.

VBA Function with System Table Query #

Function GetTableSizeViaSysTables(tableName As String) As Double
    Dim rs As DAO.Recordset
    Dim sqlQuery As String
    
    ' Build the SQL query (escape single quotes in table name to avoid errors)
    sqlQuery = "SELECT Sum(MsysACEs.Size) AS TotalSize " & _
               "FROM MsysObjects INNER JOIN MsysACEs ON MsysObjects.Id = MsysACEs.Id " & _
               "WHERE MsysObjects.Name = '" & Replace(tableName, "'", "''") & "' " & _
               "AND MsysObjects.Type = 1;" ' Type 1 = Local table
    
    ' Execute the query
    Set rs = CurrentDb.OpenRecordset(sqlQuery)
    
    ' Retrieve the size if results exist
    If Not rs.EOF And Not rs.BOF Then
        GetTableSizeViaSysTables = Nz(rs!TotalSize, 0) / 1024 ' Convert to KB
    Else
        GetTableSizeViaSysTables = 0
    End If
    
    ' Clean up
    rs.Close
    Set rs = Nothing
End Function

Quick Notes #

• Late Binding: If you don’t want to set a DAO reference, replace DAO.Database/DAO.TableDef with Object, and initialize the database with Set db = CreateObject("DAO.DBEngine.120").
• Enable System Tables: To access MsysObjects and MsysACEs, go to File > Options > Current Database > Navigation Options, check Show System Objects, then click OK.

内容的提问来源于stack exchange，提问作者Stefano