Its my intention to implement a History function in my database where users can see what records they've previously worked on. To achieve this initially, I created an extra field in the table they we're looking at and added their name from the login screen and timestamp to the record. This works, but the problem is if another user loads up the record, it overrides the last person since its just one field and its inconsistent.

So instead I created a new table and added their names and timestamps in there as a new line. The issue here is that eventually there's going to be 1000's of records in a table eating away at my hard drive space over time and eventually since the max for a table is 2GB, its just going to break one day.

After researching, Queries have a max limit, but you've still got to link that to a table that holds the data. I would like a table that contains, lets say 100 records, and at the 101st record, the oldest line is overridden and so on.

There's no code to share since my problem isn't with VBA, but rather finding a work around for this, I would of assumed this would be implemented into Access by default. I just have a table called 'History' and in VBA, an 'open recordset' function and '.addnew' 'User' and 'Date'.

CodePudding user response：

You could use an