Make an Access Database 'expire'

Is it possible to make a standard, desktop style MS Access database 'expire' after a pre-defined period of use?

This one at would be such a candidate as it gets modified regularly.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

dannywarehamConnect With a Mentor Commented:
There are several ways of doing this.
However, none of them is perfect, as Access is a client-side database.
This means that a savvy user can get in and unsecure your system or the expiry.

The simplest way is to have a table with a field called "dtExpire". Have it with no value in at all.

On the first form you load, have code that checks to see if the expiry date has elapsed.
If there's no date (first opening), we put todays date in.

You could also write to teh registry of the PC. I can show how to do this - but it's a sledgehammer to crack a walnut...

Dim myRecordedDate as String
'lookup date
myRecordedDate = nz(dlookup("dtExpire", "tablename"),"Empty")
'insert expiry date if there is none
If myRecordedDate = "Empty" then
  CurrentDB.Execute "INSERT INTO yourtablename (dtExpire) SELECT " & chr(34) & format(now(),"dd-mmm-yy") + 30 & chr(34) & ";"
End if
If myRecordedDate>= Now() then
  'let em in
End if

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You could "timestamp" the db, then use DateDiff to determine how long it's been in use. For example, add a custom Database Property named "DBTimeStamp", then compare that value at startup:

If DateDiff("m", CurrentDB.Properties("DBTimeStamp"), Now) >2 Then
  '/db is more than 2 months old
  Msgbox "This database is more than 2 months old. Contact the vendor for a new database.", vbOKOnly
End If

How to add custom database properties:
Vadim RappConnect With a Mentor Commented:
  1. in Startup, specify startup form, and specify not to show database window, so the user has no way to use your database other than through the startup form
  2. in startup form, put a code that on 1st run creates secret registry key with the date, and on subsequent runs comparescurrent date to that key
  3. compile the database into ADE/MDE so the user can't see your code.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

verpitAuthor Commented:
all fantastic elements!   Thanks to dannywareham for his earlier tips as well, to LSM and vadimrapp1 on this one so far.

This is going to be one of those questions that takes some doing to explore.  Chances are the credit will be spread pretty thin.  I wish I had control over that and could give each person adequate credit.  This is going to take some brain power on my part so please bear with me and thanks again for chiming in.

OK so I think I will probably start with dannywareham's example and see if I can get that to work it seems like the most immediate solution that will both allow further development, and be least likely to run into permission issues with the OS itself.  Though I will be locking more tightly, I have to start peeling this onion slowly.  I will stop peeling and award credit as necessary as quickly as possible and post a follow-up.

dannywareham suggests:
"The simplest way is to have a table with a field called "dtExpire". Have it with no value in at all."  I will give this a go and paste his code.  

Is it best to have a separate table for this?

Updates always posted here.   Thanks again for the help.    

>>Is it best to have a separate table for this?
Yes - and hide it.

There's no one way to do this, because Access is so easily hacked.
Saving as an MDE is a great way to secure your code (as it's not accessible to users at all).

Using the registry (with an MDE) is yet another more robust solution - but it all depends how secure you need teh db in teh first place.
If the answer is "really secure", then (unfortunatey) Access isn't the best db option...
verpitAuthor Commented:
Excellent!   Points well taken.

Is there a way to "wrapper" it somehow in another type of executable that isn't easily hacked?
You can use Access as part of an exe - but it's a lot of work if you don't know a compilable language (C, C++, C#, VB, VB.Net etc)
Do you have Visual Studio or know these languages?
verpitAuthor Commented:
no, I don't :(
dannywarehamConnect With a Mentor Commented:
In that case, the bronze medal goes to the suggestions already made:

1. Either use table method or registry method to record a date
2. Secure code by saving as MDE

Vadim RappCommented:
Note that tables are visible to the user even in MDE. You can hide them, but all it takes is menu tools/options/hidden. You have to come up with some way to scramble the date visible in the table, and unscramble it in code.
vadimrapp1 is right.
You can prefix with sys to hide (slightly) better. But this is one of the problems with Access.
There are always ways to get around it's inate security
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

FWIW, here is a demo database from this site:
It is a sample of a 30 day demo Access database.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're looking for a commercial solution (i.e. you're going to build a commercial app using only Access) then there are a couple of products that can work with Access, although I'm not sure about 2007. Is this the type of thing you're looking for?
verpitAuthor Commented:
excellent suggestions all
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.