I am trying to figure out how to retrieve the creation date of a table using vba, so that it can be displayed in a form and a report.

The quickest way I can think of offhand is to grab it from the MSysObjects table, which is one of the
several system tables in an MSAccess MDB database file. Here's an example:

Dim TableCreateDtTm As Date
Dim TableName As String
Dim WhereCond As String

TableName = "MyTable"
WhereCond = "Name = '" & TableName '" & And Type = 1"

TableCreateDtTm = DLookup("DateCreate", "MSysObjects", WhereCond)

MSysObjects contains a bunch of information for all objects in an MS Access MDB database file. You
can see all existing system tables for an MDB by selecting Tools | Options, selecting the View tab,
and clicking 'System Tables' to TRUE.

Each object type has its own number assigned to it - here's the most common ones:

Table (Native: non-linked) = 1
Table (ODBC-linked) = 4
Table (MS Jet/Access linked) = 6
Query = 5
Form = -32768
Report = -32764
Macro = -32766
Module = -32761

Please note that when using this method, the actual date create coming from MSysObjects will
be accurate for Native (non-linked) tables only. The information for the Create Date for a
linked table will be stored in either the other MDB database (MS Jet-linked) or in a system
table, etc for an ODBC-linked table.

Hope that helps!

Warren

:)

I tried this but got an error message of Syntax error in string in query expression 'Name = 'Shippers Report'.

The name of the table i am trying to get the date from is "Shippers Report"

The code i am using is below

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

    Dim TableCreationDtTm As Date
    Dim TableName As String
    Dim WhereCond As String

    TableName = "Shippers Report"
    WhereCond = "Name = '" & TableName '" & And Type = 1"

    TableCreationDtTm = DLookup("DateCreate", "MSysObjects", WhereCond)

    Me![txtDate] = TableCreationDtTm

Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox Err.Description
    Resume Exit_Form_Open

End Sub

My apologies - should have tried running the code myself first.

Old: WhereCond = "Name = '" & TableName '" & And Type = 1"

New: WhereCond = "Name = '" & TableName & "' And Type = 1"

Thanks its working fine now.

:)