How to get 'DateCreate' from MSysObjects

[sumdumgai]

Using MS Office 2007 and 2013. I'm trying to get the date that a table [T1] was created in an Access database. So, I'm trying to get the value of the 'DateCreate' field in the 'MSysObjects' table where the 'Name' field is equal to 'T1'. I'm also committed to using DAO. Problem is I'm getting permissions error on this table.
Any help would be appreciated.
Thanks.

 

[MarkK]

I'm trying to get the value of the 'DateCreate' field in the 'MSysObjects' table where the 'Name' field is equal to 'T1'.

Can you elaborate on how you are doing this?
Mark

 

[static]

Private Sub Command0_Click()
    MsgBox CurrentDb.OpenRecordset("select datecreate from msysobjects where name ='t1'")(0)
    MsgBox DLookup("datecreate", "msysobjects", "name ='t1'")
End Sub

 

[sumdumgai]

Tried this:

    Set dbs = DAO.OpenDatabase(MDBFullPath, False, True)
    These two commands did not work so commented out
    'strDdl = "GRANT SELECT ON table MSysObjects TO public;"
    'CurrentProject.Connection.Execute strDdl

   strSQL = "SELECT MSysObjects.DateCreate FROM MSysObjects WHERE MSysObjects.Name = ""T1"" ;"

    Set rst = CurrentDb.OpenRecordset(strSQL)

I tried the 'Dlookup' function earlier but got a function not defined error. Is there a library that I need for DLookup?

Thanks.

 

[MarkK]

Where are you writing this code? DLookup() is a public method of the Access.Application object, which has global exposure in all VBA modules in MS Access, and would be very unlikely to raise a "function not defined" error. Are you writing this in Access? If so, why do you need to open a different database, as you seem to be doing here...

Set dbs = DAO.OpenDatabase(MDBFullPath, False, True)

It still feels like we don't have all the information about this problem.
hth
Mark

 

[isladogs]

I'm also confused.

You should definitely be able to get the date created (DateCreate) & if you need it date modified (DateUpdate) fields from MSysObjects for tables in your current database.

It will work for any of these :
- local tables
- linked Access or SQL tables
- linked spreadsheets
- linked CSV files

SELECT MSysObjects.Connect, MSysObjects.Database, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

However I don't believe you can open another database & expect to access MSysObjects in that database ... which is what I think you're trying to do

 

[sumdumgai]

Sorry. I should have mentioned that I was trying to retrieve this data from Access using Excel VBA back into Excel.

 

[static]

From outside of Access you'll need to create a connection to open a recordset from.

https://www.connectionstrings.com/access/

I'm pretty sure Excel has a wizard for connecting to external data sources though.

 

[sumdumgai]

Thanks. So, I can retrieve a table name into Excel, but to get to a record within that table, I have to connect to the database? Is that right?

 

[Galaxiom]

Change the Command type to SQL in Definition tab in the Connection Properties and put the query SQL into the Command text box.

 

[sumdumgai]

Galaxiom, thanks. But could you please elaborate? I tried to set up a new connection, but the MSysObjects table would not expand to its columns in the query wizard.

 

[Galaxiom]

This doesn't use VBA but displays the data as a table in Excel. There are a few ways to get to the Wizard. Here is one.

Data Ribbon
> Get External Data > From Other Sources
> From Data Connection Wizard
> Other/Advanced
> Microsoft Office xx.x Access Database Engine

DataSource: Your database file
The rest of the fields can be left as default

Select a table and insert the data where you want it to be displayed.

Then edit the Connection Property as outlined in my earlier post and Refresh.

 

[sumdumgai]

Thanks again. I actually found another way, much simpler, to get the table create date. 'dateCreated' is a table property that is easily gotten without going through MSysObjects.

 

[isladogs]

For the benefit of others who may want to do the same, please explain how you did it

 

[sumdumgai]

Got table create date as follows:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim MDBFullPath As String

    ' Open the Access database in shared mode (False) and read-only (True).
    Set dbs = DAO.OpenDatabase(MDBFullPath, False, True)
    For Each tdf In dbs.TableDefs
        ' Get date created for 'tableT1'
        If tdf.Name = "tableT1" Then
            dateCreated = tdf.dateCreated
            GoTo nextT
        End If
nextT:
    Next