VBA question for accessing Microsoft Access

Associate
Joined
26 Feb 2004
Posts
970
Location
China (Qinhuangdao)
This is a bit of a long-winded explanation. Basically, we have several users using the same database for projects. If they have updated their database while on a project, I want to compare the databases side-by-side to see which tables have been the last to be modified, so I can tell that table needs to be copied to a 'Master' database.

However, what I have noticed using the OpenSchema command, is the 'Date Modified' is not actually when the table has been updated, but when the table has been redesigned in some way.

So, in my software, I need to store the actual date updated somewhere in the database. When I open the database in MS Access, I can right-click on the table, and it will display the following:
Date Created
Date Modified
Description

So, rather than adding another field to the table, like "Date Updated", I wanted to write the actual date updated in the Description part of the table.

I've manually entered a date in the Description, e.g. "2023/12/10" and I can read this back in my software using Master.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table")). However, this is read-only. And I cannot seem to work out to write to this part of the table.

I really don't want to add another field for every record of the database, when I only need to store one date. So does anyone know how to write to the description part of the table?
 
Thanks Everyone. I realise my original post was very complicated, and I should have just said "How to write to the description property of a table".

I did eventually find some code on the internet which helped:

Tek-Tips Link

MarkSweetland (MIS)20 Jun 04 09:44​
I believe the Table description property is read-only through the ADODB.Connection object. If you are going to modify the description of the table you can get there through DAO:​


Code:
Private Sub setTableDescription(strSourceFile As String, strTableName As String, strDescription As String)
    
Dim ws As New DAO.DBEngine
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim prop As DAO.Property
    
    Set db = ws.OpenDatabase(strSourceFile)
    Set tbl = db.TableDefs(strTableName)
    Set prop = tbl.Properties("Description")
    prop.Value = strDescription
    
    Set ws = Nothing
    
End Sub

This was exactly what I needed. Now every table has the date when new data is added/modified.
 
Back
Top Bottom