Split database: display last update datetime of Back End table in Front End

Experts,

I have a split database and would like to display to users when the back end data was last refreshed, which happens when I import source files from Excel and then delete the old and append the new records to an output table.

If I put a text box in the front end, say on the switchboard form header, can it directly retrieve the last update date/time attribute of the back end table, or would I need a procedure to store the value somewhere and retrieve it from there?

thx
LVL 1
DavisroBudget AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
There is no attribute of a Table that contains the last time the table was updated.
So ... your option two is the answer.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
A simple table in the backend could hold this information ... then Link to that Table from the Front End,  and display that value directly as you indicated in the switchboard form header.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Another way to address this would be to use the CreateProperty method to create a new property for that table.  In the immediate window:

set db = currentdb
set tdf = db.tabledefs("yourTable")
set prp = tdf.createproperty "Updated", dbDate, Now()
tdf.properties.Append prp

Then you could set that property whenever the table is updated:

currentdb.tabledefs("yourTable").Properties("Updated") = Now()

 and retrieve that property when you need it:

?Currentdb.TableDefs("YourTable").Properties("Updated")
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
I use the table method suggested by Joe.  Every time I run an import, I log the date in a table.  Then in the FE, I display the date on the form/report they use to view the data.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I like the Property gig ... but ... I don't think you can put

=Currentdb.TableDefs("YourTable").Properties("Updated")
in a Text Box Control Source. So code would have to populate it.

Also ... Dale ... you forgot:

Dim db as DAO.Database
Dim tdf as DAO.TableDef
Dim prp as DAO.Property
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>
A bit more.

Because you are running a three step process:
import source files from Excel and then delete the old and append the new records
Set your "UpdatedDate" datestamp to be generated when the last step is successfully completed(append).
If you simply set this date on the import process, you run the risk of the Delete or Append portion failing, and you not having an accurate date.

And finally, you may wish to add the time to the datestamp, (in case you ever need a History, if more than one update is done in a day)
And also consider using the ISO international date format as well.
    Format(Now, "YYYY-MM-DDTHH:NN:SS")
This is so that dates and times sort correctly.

JeffCoachman
PatHartmanCommented:
@Jeff,
Can you set properties for linked tables?

@Ron,
I also add a create date column to these tables that are refreshed.  It is one more way to validate the exact time the table was populated and can be easily used in queries.
Dale FyeOwner, Developing Solutions LLCCommented:
@Joe,
1.  You are correct that you cannot set the control source to:

= ...

so you would have to populate it with a line of code somewhere, either in a function, which could be called from the control source, or the Form Current or Load even.

2.  the code I provided was for use in the immediate window, as stated in the line above the code.  ;-)

@Pat,

Yes, you can set the properties of linked tables like this.
DavisroBudget AnalystAuthor Commented:
It's always such a pleasure to hear your valuable feedback. Thanks guys, and thanks to Dale for introducing the table property method. I supp I se it would serve the same purpose as storing a value in a named range in Excel.
Dale FyeOwner, Developing Solutions LLCCommented:
Ron,

Glad I could help.  I create custom table and database properties to store things like paths to data.  This way, I don't have to expose those things to prying eyes in tables.  

Very few people will go so far as to peek into the properties of either the database or tables.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.