vBA .exe file to update record

[Access_Help]

Hi.

I have a table with product expiry date, when the database opens it checks the expiry date, if the license has expired it will notify the user and close down.


When the user purchases a new license, I want the user to run a VBA script that I send by email that updates the record in the table with a new date.

The database is being run as a desktop application and I have no access to it.

Is this possible?

 

[Uncle Gizmo]

Well it used to be possible to send a "Covert Email" but I'm not too sure anymore because Google have been locking things down and also changing their authorisation process. I'm not up to date with it. I have made various notes about it on my website here:-

Gmail from MS Access, VBA, Excel, Word…​

Gmail from MS Access, VBA, Excel, Word… - Nifty Access

Gmail from MS Access, VBA, Excel, Word… "Send a Secret Email" Escape From Outlook Video 1 (0:56) Escape From OutlookMicrosoft tightly combine their applications together however sometimes it’s nice to escape from Microsoft Products and use others. Here I demonstrate how you can use Google Mail...

www.niftyaccess.com

Might be worth you having a look through. The sample application I provide is available for free to AWF members, you just need to contact me and I will tell you how you can get a free copy. Don't forget, your customer would have to be connected to the internet for this to work.

 

[Gasman]

You could just send and encrypted text file and instruct them to place it in the DB folder.?
Then when you start the DB and find the product has expired, you could look for that file, decrypt it to get the new expiry date, update the table, then delete the file.?

 

[isladogs]

Hi.

I have a table with product expiry date, when the database opens it checks the expiry date, if the license has expired it will notify the user and close down.


When the user purchases a new license, I want the user to run a VBA script that I send by email that updates the record in the table with a new date.

The database is being run as a desktop application and I have no access to it.

Is this possible?

Yes it is possible to do something like that.
I supply a licence updater as an encrypted ACCDE file with several of my commercial products.
When a licence is renewed I provide a 6 digit code which when entered renews the licence for a further period of time (usually one year)
That code causes an update query to run which sets the new expiry date.

The apps concerned also include code to remind the program admin when a licence is about to expire - initially 45 days beforehand, then again at 30,15, 10, 5, 4, 3, 2,1 days before the deadline. Once the deadline is passed without renewal, the application's functionality becomes limited (similar to Office 365) and after a 'period of grace' is disabled completely

I've been using this with many clients for almost 10 years without any issues. Its was fairly easy to code and almost impossible to hack.
However with apologies, as my product licenses depend on this, I'm not prepared to share the code I use

 

[Access_Help]

Some great ideas guys, thank you.

So, when I went back to my database, I realised that the expiry date was not being looked up from a table, but was hard coded into a VBA like below:

Dim dtExpire As Date
Dim DateDiff As Integer

dtExpire = #6/1/2030#                       'I need this to be looked up from an encrypted file
Dim MyDate
MyDate = Date    ' MyDate contains the current system date.
DateDiff = dtExpire - MyDate

If MyDate >= dtExpire Then
DoCmd.Close acForm, "splash"
DoCmd.Close acForm, "Login"
DoCmd.OpenForm "Expired"

ElseIf DateDiff < 15 Then
MsgBox "Your license is about to expire in " & DateDiff & " day(s). Please contact the developer for renewal through the Technical Support link", vbOKOnly, _
            "Warning!"
End If

I like the idea of having an encrypted file with the new expiry date (which would act as an authenticator every-time the database opens), but not sure how to go about coding this so that the date is looked up from the file (any tutorial/guidance will be appreciated.

The token ideas seem a little too complex for me, if there are any ready-made open source examples, do let me know!

The secret e-mailer may not work since the user may not be connected to the internet.

 

[isladogs]

@Pat Hartman
Your solution is almost identical to mine. That requires no additional software and is IMO probably as easy or easier to code. No arrays required.

 

[isladogs]

That's fine and I'm not knocking your approach.
Presumably the new expiry date is then stored in a table or the registry so it can be checked when the application is opened

With my approach, the licence updater is a locked down ACCDE file with no nav pane, no ribbon and the application interface is hidden.
There is no licence data stored in that file.
When the randomised 6 digit code is entered, a message is sent to me

The licence expiry date(s) are stored, encrypted, in a deep hidden table in the main application.
It would be almost impossible to enter a new valid expiry date directly in the table
The expiry date is also stored, encrypted with a different cipher, in the registry.
If the two dates don't match, the licence is considered to have expired.
In over 10 years with multiple applications and numerous clients, the system has never been hacked

 

[isladogs]

Agreed. My clients for these applications are all businesses/schools.
However, I did ask the program admins at two trusted clients to try and break the system.
Neither were able to do so despite being highly capable Access developers themselves.

Just a correction to my previous reply:

The licence expiry date(s) are stored, encrypted, either in a deep hidden table in the main application OR in the case of site licenses, encrypted in the SQL Server BE