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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I like the Property gig ... but ... I don't think you can put
=Currentdb.TableDefs("Your
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
=Currentdb.TableDefs("Your
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
<No Points wanted>
A bit more.
Because you are running a three step process:
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
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 recordsSet 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
@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.
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.
@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.
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.
ASKER
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.
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.
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.
So ... your option two is the answer.
mx