Table Build Dates

[unclefink]

I've got a secondary database that will be used in a portable device which will get its data from another db prior to being placed onto the portable device. What I would like to do is put a text box on the opening form of this database showing the date the table was built.

To update the secondary "portable" db, I've created a button to delete the necessary tables and another to import new tables from the sourse db to this new one. I don't want the users to have visual access to the tables properties in order to see when the imported tables were built. Is there a way I can get this information, either by query or vba and place it on a form.

What I am looking for is the information listed in the table properties. Every time the new table imports, it will always be the same name so that shouldn't be an issue. Hopefully.........

Thanks in advance.

Respectfully,

 

[rzw0wr]

From what I am reading Access does not store the date the data was changed or added to a table.
Access does store the date a record was changed. That appears to be stored with each record and is not a table property.

The last update for the table is for the date the structure of the table was changed.



Dale

 

[nanscombe]

A couple of possibilities:

a) Is there is a datestamp in the data that you could grab the latest from using the DMax() function?

b) A small table in the database with a field that gets updated by rebuild routine, however that will be the time that the update was run.

c) If you try to use the table that you are creating to grab the date it will end up being when the routine created the table rather than when the data itself was updated. This means it won't be the same for everybody though. If that's Ok then you could try this.

Create a function

Private Function tableCreatedAt()
tableCreatedAt = CurrentDb.TableDefs("yourTableName").DateCreated
End Function

In the Properties of yourTextBox set the Control Source as "= tableCreatedAt()" (without the quotes)

If all the users use the same physical table then it would be the same. However, if they don't, we'd need to get a bit more creative.

 

[unclefink]

I think what I am trying to do is probably more described in "C". The data itself is really being updated in the main source db. I am trying to get the date a table was created as this specific db is more of a read only concept vs. data input. I just want the created date to be visible and say table x created date is x.

Essentially, if you right click on a table and go to table properties, the created date is what I am looking for. This will tell me if this db has been updated with a new imported table or if its an old import. I am trying to get this information without following the steps above as I don't want users to have direct access to the tables.

Reading what rzwowr posted, i'm not interested in getting a date for the data itself, I'm looking for a date for the actual table itself.

 

[nanscombe]

The Access database in the attached Zip file illustrates what I meant and should do what you want it to do.

 

[unclefink]

The Access database in the attached Zip file illustrates what I meant and should do what you want it to do.

nanscombe,

Thank you very much for the help, its much appreciated, that's exactly what I was looking for and trying to do. I currently have a macro that imports two different tables so the table times would likely be the same anytime the update is completed. If I break that up, is there a way to do this method with multiple tables. I was trying to dissect the function but didn't get very far before I had to close out of the program.

 

[nanscombe]

You could extend the original code:

Private Function tableCreatedAt()
dim strTemp as String

  strTemp = "yourTableName: " & CurrentDb.TableDefs("yourTableName").DateCreated

  strTemp = strTemp & "yourTableName2: " & CurrentDb.TableDefs("yourTableName2").DateCreated

  strTemp = strTemp & "yourTableName3: " & CurrentDb.TableDefs("yourTableName3").DateCreated

  tableCreatedAt = strTemp
End Function

But it get a bit slow, which might not matter too much if the form isn't opened frequently.


However, in the attached database, I have taken the idea one step further.

Form frmManifest is a self-contained form that will update a table called sysManifest, it will even create it if it doesn't exist.

When the Create Manifest button is pressed it will:
Clear down the table sysManifest
Run through each table in turn and add the name and date to sysManifest

It should ignore any system tables.

The values in the table can then be viewed in a Listbox as in Form myForm.