How to find last used date for .mdb file in C#

Question

We have .mdb files of MS Access 2003 and we have to find out last date when this database was used. We have to do it programmaticly like read .mdb file and find out last used date. Also we need to figure out last modified and date of database creation.

Development Environment:- Visul Studio 2010,C#,Windows Form

Solution:- This may help others in future.

string query = "SELECT MSysObjects.DateCreate,MSysObjects.DateUpdate FROM MSysObjects WHERE Type=2"; 
string[] arrDate = getDBCreationAndModificationDates(query);

private string[] getDBCreationAndModificationDates(string query)
        {
            string[] arrDate = new string[2];
            dao.Database db = appclass.CurrentDb();
            dao.Recordset rs = null;
            rs = db.OpenRecordset(query, Type.Missing, Type.Missing, Type.Missing);
            string strDate=string.Empty;
            if (rs != null)
            {
                arrDate[0] = rs.Fields[0].Value.ToShortDateString();
                arrDate[1] = rs.Fields[1].Value.ToShortDateString();
            }
            rs.Close();
            db.Close();
            return arrDate;
        }

Depends on what you mean by *used*. Was it used if someone just opened the database? Do they need to have made some data changes, run a report, etc.? — Yuck, Aug 26 '13 at 17:06
last used date of a `mdb` file is also the last used date of a `normal` file. — King King, Aug 26 '13 at 17:06
http://msdn.microsoft.com/en-us/library/system.io.file.getlastaccesstime.aspx — Robert Harvey, Aug 26 '13 at 17:07

score 2 · Answer 1 · answered Aug 26 '13 at 17:08

2

You could try getting information using the FileInfo class.

System.IO.FileInfo fi = new System.IO.FileInfo("Path to the file");
System.DateTime strLastAccessed = fi.LastAccessTime;
System.DateTime strLastModified = fi.LastWriteTime;

answered Aug 26 '13 at 17:08

Nicholas Post

1,857
1
18
31

Nicholas, it will give last access time of a file not a database. For example, if database was accessed 3 days ago but .mdb file was copied to another system today then this will give today's date not 3 days back date. – user2525155 Aug 26 '13 at 17:27
If you can't rely on the file's information, then you would have to come up with a custom solution such as putting a last modified column in every table and changing all queries to insert/update that column. You would then have to make another query to check that column in each table and report back the last modified date. This would be a ton of work, especially if it is a large database with a lot of queries/macros. – Nicholas Post Aug 26 '13 at 17:31
On table level we don't need to have an extra column in each table. I think on table level MS Access gives last modified info. I was wondering if there is any single property in master/system db which states for last updated date otherwise I will have to loop for all the tables to find out modified date and finally pick most recent date. Moreover there are n numbers of databases and we have to generate report by scanning all .mdb files in a system. – user2525155 Aug 26 '13 at 17:40
Refer to this question: http://stackoverflow.com/questions/926897/detect-time-of-last-change-on-a-microsoft-access-database-table – Nicholas Post Aug 26 '13 at 17:41
Adding LastUpdatedDate in each table will not work for us as we just have to generate report of all available Access dbs. – user2525155 Aug 26 '13 at 17:47
That's why I said it would be a custom solution as well as a lot of work. Unfortunately, if you can't rely on the file information, I don't think there will be an easy solution for you. – Nicholas Post Aug 26 '13 at 17:49
That is why I was seeking help... :). – user2525155 Aug 26 '13 at 17:50

score 1 · Answer 2 · answered Aug 26 '13 at 17:07

1

Use File.GetLastWriteTime on .MDB file.

Returns the date and time the specified file or directory was last written to.

 DateTime dt = File.GetLastWriteTime(path);

answered Aug 26 '13 at 17:07

user2711965

1,795
2
14
34

2

Embellish a bit, please. When and why would you use one over the other? – Robert Harvey Aug 26 '13 at 17:08
@RobertHarvey, I am not sure about `GetLastAccessTime`, since opening the file for reading will not modify that. So removed it from the answer , now its just GetLastWriteTime – user2711965 Aug 26 '13 at 17:10
You wouldn't use GetLastAccessTime because it's rarely enabled (not enabled by default installations of windows) – Darren Kopp Aug 26 '13 at 17:12
@NewHire, I think it will not give last modification done in database file. I tried this and it gave me today's date as I copied one db to my machine. It should just consider date when last modification made by Access DB. Copying file from one folder to another changes modification date. – user2525155 Aug 26 '13 at 17:44

How to find last used date for .mdb file in C#

2 Answers2