lock data for the last year (2 Viewers)

[khodor]

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]

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]

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.

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.