lock data for the last year (1 Viewer)

khodor

Member
Local time
Today, 10:51
Joined
Feb 2, 2021
Messages
39
Hi everybody
How can I lock the data from the last years
is it better to create new database for every year?
if not, how can I deal with the data for the last years?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:51
Joined
May 7, 2009
Messages
13,693
use form for data entry. use Query as
recordsource of your form.
the Query should filter the date field
excluding previous years data.

you can also have a History table where
you can put old data then purge the old
data from your transaction table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:51
Joined
Feb 19, 2002
Messages
32,787
So on Jan 1, you can't update the previous year's data? You need to probably use something other than calendar year. Maybe 365 days or 1 year and use datediff() to determine if data is updateable.

In the form's current event, you can check the date of the current record. If it is more than one year prior to today, you can set the AllowEdits and AllowDeletions to false.
Code:
Private Sub Form_Current()
    If Add("y", -1, Date()) >= SomeDateInYourRecord) then
        msgbox "Data older than 1 year, cannot be updated.", vbOKOnly
        Me.AllowEdits = False
        Me.AllowDeletions = False
    Else
        Me.AllowEdits = True
        Me.AllowDeletions = True      
    End If
End Sub

The data should not be split into separate tables or especially not separate databases. If you run into Jet/ACE limits, it's time to upsize to SQL Server.
 

Users who are viewing this thread

Top Bottom