VBA question for accessing Microsoft Access

[Anchundan]

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?

 

[dazzerd]

Seems strange what you need the do. Why not just have one or multiple related tables that all users use? Then you don't have to compare anything. If you really must do what you want to do then just create a separate table which has all the different table names as a field and then have your update field there.

 

[andy o]

Changing the name of a table when it's modified is going to be a complication for any form of whatever uses the data. Been a while since I did any serious ms access dev. You can link tables from your local database to those on a server. Ms access isn't so great at multiple concurrent users. You're ok with one or two. By 20 users you have problems with updates. The way I got round that was to have a local copy of each table. The user chose to edit a row and it was copied from the central database to the use one. The use edited. On commit, the row was copied back to the central database. I soft locked rows. I had a last user editing with a username column. And two dates. User editing, user committed. Blank the use editing once change was committed. As a plus, you can also see who made the last change if there's a query.

 

[Tee Hee Johnson]

There is a program called accdbmerge that might be useful. Been a while since I used it, the free version was somewhat limited… but it could help.

 

[Anchundan]

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:​

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.

 

[Ergates]

Seems a rather convoluted way of doing that - a record create date and record update date are pretty standard fields on a database.