Solved

how to expire an access database that can be reactivated via password

Posted on 2011-09-04
24
293 Views
Last Modified: 2012-05-12
I have an access 2007 database that I want to have expire or lock after say 60 days. During or after be re activated via a password or something.
Basically, I am not a programer but spent the last 3 months putting a database with forms etc for a client and as they do not wat to pay a great deal for it I have told them I own the database program (excuse my terms). What I would like to do, so as to safeguard my work, is to have a time out feature that I can reset perhaps if it does lock/ expire.
I found this on Expert Exchange but could not get it to work.
any advice would be greatful

Private Sub Form_Load()
Dim myRecordedDate As String
 
'lookup date
myRecordedDate = Nz(DLookup("dtExpire", "ExpireTable"), "Empty")
 
'insert expiry date if there is none
If myRecordedDate = "Empty" Then
  CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) VALUES " & Chr(34) & Format(Now(), "dd-mmm-yy") + 30 & Chr(34) & ";"
End If
 
If myRecordedDate >= Now() Then
  'expired
  Application.Quit
Else
  'let em in
 
End If
End Sub

Open in new window

0
Comment
Question by:tucup
  • 8
  • 8
  • 4
  • +2
24 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36480905
How did you try to get this to work?

I'm not sure where you found it but looking at the code and making a few guesses it's meant to be code for a startup form.

That form would open when the database opened and the code would run.

There's a bit more than that involved, eg a table called 'TableExpire'.

Have you set the start form, table etc up?

By the way this code can easily be avoided by holding F5 down while the database opens, sorry.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36481068
>> ...that I want to have expire or lock after say 60 days....

Not an easy job, there are so many ways to defeat possible routes.
0
 
LVL 75
ID: 36481110
If I need to do this, I would be using Peter's product:

Keyed Access
http://www.peterssoftware.com/ka.htm

I use several of his excellent products in various apps.

mx
0
 

Author Comment

by:tucup
ID: 36481444
imnorie,

 hi, I put the code directly into my form via: Design view> View code. And then put it in Private Sub Form_Load()

I then created a Table called ExpireTable and a row called Expire

It threw up an error by highlighting the sentence starting CurrentDb.Execute

This was supposed to just timeout the database after 30 days I think.

AndyAinscow

Hi, I understand what you are saying, and I dont want to go too deep, as it is only a simple measure. I dont mind there being flaws where a clever guy would know how to bypass it. I was just hoping for something that would stop the average Joe. Do you think it will be too much to do?

DatabaseMX

Thats great looking software. It is $299 for it though. I am sure it is ideal but I was hoping for an alternative rather than paying for it, sounds like im being cheap, sorry, but I was hoping for a simple solution that probably is not simple. thats for the link though.
0
 

Author Comment

by:tucup
ID: 36481451
DatabaseMX
Oops..

Thats great looking software. It is $299 for it though. I am sure it is ideal, but I was hoping for an alternative rather than paying for it, sounds like im being cheap, sorry, but I was hoping for a simple solution that probably is not simple. THANKS for the link though.
0
 

Author Comment

by:tucup
ID: 36481457
0
 
LVL 75
ID: 36481662
You can take a look at these also:

Marcus Fisher (EE's harfang)
Need to supply purchasers with a software unlocking code
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Architecture-Design/Q_27074447.html?cid=1573#a35892087


Add license key functionality to your source code similar to that used by Microsoft.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=25237&lngWId=1

http://www.ssware.com/cryptolicensing/cryptolicensing_ocx.htm
'Standard' version is less expensive that Peter's ... but ...?

mx
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36482516
The very simplest way I can think of is the following - maybe it would be suitable.

Forget about any table / key to unlock the database.
Put a piece of code into the startup form as follows

Private Sub Form_Load()
If Now() >= CDate("#dd.mm.yy#") Then 
  Application.Quit
End If
End Sub

Open in new window

where dd.mm.yy is the date in the local format for dates (could be mm.dd.yy for instance) that you want it to expire upon- I think the CDate is the correct function to force it to be a date.

Now convert it to an mde - this prevents the end user being able to look at/modify code.


When they pay for the full version then you give them another copy but with the if...end if statement removed.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36484860
This is always a touchy subject.

*First* I would consult a lawyer.
Despite you best efforts and assumptions, you may not have satisfied all the legal requirements that you "own the database program"
In legal terms this has to be clearly spelled out in a legal document signed by both parties.
In other words, did you create a "Legally approved" EULA?
http://en.wikipedia.org/wiki/Software_license_agreement

Also note that by denying them access to the Application, you may be denying them access to "Their" data, ...another touchy subject, as it may be your program, but it is their data, and you cannot deny them access to it.

Again, this all has to based on a firm legal agreement,
...just you saying "I own the database program" may not stand up in court.

Again, consult a Lawyer first.


JeffCoachman
0
 
LVL 75
ID: 36485078
Yes ... I absolutely agree ... consult an attorney *first* ... it's not black and white.

mx
0
 

Author Comment

by:tucup
ID: 36485349
Yes you are right, I will have an agreement for it. Basically it will outline that the program is mine but the data is theirs. To which I can detach the tables and delete the forms etc

I will need to look a little deeper on thast subject and I thank you for raising it. But, any one know how to amend or fix my original code?

AndyAinscow

While I do like your answer, do you think my one can be tweaked, I would like it to check and see if a date exists and put it in but also later allow me to reset it again
0
 
LVL 33

Expert Comment

by:Norie
ID: 36485392
Do you mean the code in the first post?

Did you get any error message when the line was highlighted?

I agree with Jeff, you need to be careful with this sort of thing.

Your employers could have some clause somewhere, eg contract of employment, that is meant fotr these sort of situations.

Might be something that says any work done on their equpiment is there, unless there has been a prior agreement, but in 'legalese'.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75
ID: 36485410
It's one thing to call something a 'demo version', which expires in N days, but this is a different situation.

Also, all of these schemes done in VBA code can, in one way or another ... pretty much be circumvented.  

Meanwhile ... try this:

Private Sub Form_Load()

    Dim myRecordedDate As Variant

    'lookup date
    myRecordedDate = Nz(DLookup("dtExpire", "ExpireTable"), "Empty")

    'insert expiry date if there is none
    If Nz(myRecordedDate, "") = "" Then
        CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) VALUES #" & DateAdd("d", 30, Date) & "#"
    End If

    If Date >= myRecordedDate Then
        'expired
        Application.Quit
    Else
        'let em in

    End If
End Sub

mx
0
 

Author Comment

by:tucup
ID: 36485635
DatabaseMX

thanks for that.
It does not seem to put a date in if it does not exist. If I put it in manually it is OK. One other thing. If I wish is to ask for a reset password is that simple to input.

Say something like:
 If Date >= myRecordedDate Then
' put in a popup box asking for password which is ExpireTable, dtPassword
,
this could be a simple word like letmein so that the database works afterwards 
0
 

Author Comment

by:tucup
ID: 36485647
imnorie

yes I got an error in my original:

in debug it highlighted the following line of code in yellow
runtime error 13
type mismatch


CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) VALUES " & Chr(34) & Format(Now(), "dd-mmm-yy") + 30 & Chr(34) & ";"
0
 
LVL 33

Expert Comment

by:Norie
ID: 36485689
Remove +30 and change Now() to Now()+30.

That's assuming you want to add 30 days to the date, and then format it.
CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) VALUES " & Chr(34) & Format([b]Now()+30[/b], "dd-mmm-yy") & Chr(34) & ";" 

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 36485693
I was trying to bold the change but it didn't work.

Thinh I'll check how the forum deals with formatting.:)
0
 
LVL 75
ID: 36485852
"It does not seem to put a date in if it does not exist. I"

Standby ...

mx
0
 
LVL 75
ID: 36485869
Here you go:


Private Sub Form_Load()

    Dim myRecordedDate As Variant

    'lookup date
    myRecordedDate = DLookup("dtExpire", "ExpireTable")

    'insert expiry date if there is none
    If Nz(myRecordedDate, "") = "" Then
        CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) SELECT  #" & DateAdd("d", 30, Date) & "#", dbFailOnError
    End If

    If Date >= myRecordedDate Then
        'expired
        Application.Quit
    Else
        'let em in

    End If
End Sub
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36485981
tucup,

Just a follow up:

<I own the database program>
Until they purchase it? ...or is this a "Licensing agreement".

Remember,  you can "Lease" the application to them or you can outright sell it to them.
There are no clear cut rules on this;
If you simply create the program for them, and it is fairly basic (Contacts, Simply Order system, ...etc)
You may just want to "sell" it to them (after all this 90 day stuff is cleared up)
Then you can simply get paid one chunk of money once and be done with it. (they can still contact you for support, if they like)
Remember, again, if this is a fairly basic database that *any other access developer can create*.

If, however, you are creating something highly specialized, or something that you may want to market to other clients, (and again, you are sure that *No other developer can create, or has created*) you may consider "Leasing" the app.

Many small clients may balk at your request to "Lease for a small fee", because they know they are tied to you for everything.
Some like the idea, because of the low initial cost.

But this opens up another can of worms about securing, distributing your app (...etc).
So again, these are things you should consider after the 90 day trial issue is resolved.

;-)

JeffCoachman
0
 

Author Comment

by:tucup
ID: 36499469
boag2000

Thanks for the advice.
Without going into to much detail.
I am not, as you probably guessed, a programmer. I researched, and scrounged as much as possible from the internet. Later adapting code for this, to the point where I could not reproduce it if I tried.

The company who use it, asked about a database but did not want to pay anything for this, even though senior staff all have Aston Martins etc. I told them I would design one that they could try but it was my programme. In fact, they refer to it as my program, I now have told them that as I maintain their servers etc, they could use it as part of the package ( so thereby securing my contract)
I now want to safeguard the code from being duplicated or used if they replace my services. I would of course make sure they get the tables of data. The database does do a lot of things, and I do add extras now and again.

The end..., long winded but now you guys know a little about why I wanted to somehow secure it.



DatabaseMX:

Thanks, that has solved the date issue, it puts it in perfectly.

Is it too complicated to add a password reset. So that if it reaches the finnal date, I get a popup asking for reset code  (simple password).

If you guys think it can be done but would take a litle time I am happy to do another request for a further credit:)

imnorie

Thanks for the update there, but I saw DatabaseMX`s answer and just cut n pasted which resolved the issue.
0
 

Author Comment

by:tucup
ID: 36499551
oops, spoke too soon.

DatabaseMX
your answer, though it puts a date in, it immediatly closes the database when next ran. I suspect it is not accounting for the "upto 30 days". it looks like it is just saying, ok its past or equal to today so close. I think it is not allowing a 30 period?



imnorie

your suggestion puts the following in RED under bug mode ( I am using ACCESS 2007)

CurrentDb.Execute "INSERT INTO ExpireTable (dtExpire) VALUES " & Chr(34) & Format(Now()+30, "dd-mmm-yy") & Chr(34) & ";"

hold on...
took space out in front of it, now in black...but still does not put a date in.
it seems happy with code, but does not do anything to ExpireTable. DatabaseMX`s does make the change, but expires the database on next run
0
 
LVL 75
ID: 36499671
@ "DatabaseMX
your answer, though it puts a date in, it immediatly closes the database when next ran."

I don't get that result.  If I clear the table, open the form ... it adds today's date. If I close the form, reopen - no problem - it opens.

So ... are you using Date or Now - which includes Time ?

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 36499794
Typo >> "it adds today's date." >> it adds today's date+30 days (10-7-2011)

mx
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now