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

 

[Pat Hartman]

I use an encoded token. The user opens the database and enters the new token. The app then decodes the token and comes up with the new expiration date. You can do the other things but this is the simplest method. I can't give you the code I used but I can give you some suggested techniques for creating the Token and decoding it.

 

[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.

 

[Pat Hartman]

Since you can use the Token method using only Access and the other methods require additional software, I'm not sure why you thing that a token is too complex. Create some arrays and populate them randomly. To encode a day, use an array with 31 entries. To encode a month, you need 12, to encode the year, use 20 or so. Then look up each part to come up with A7E for example. I also use a checksum and embed several random codes which change each time but are not relevant. So the token might be 8Y2E AKW7. Don't put mdy in order spread them out. You don't want someone to compare this year's token with last year's and see a pattern. Day and Month, if you go to that level, will be the same each year so you might even encode the code by using 1 character of the token to specify whether to encode using array set 1, 2, 3, or 4

Keep a database of the tokens you send to each client. My tokens also include feature activation codes. So, if position 6 is in the range J-M, emailing is activated, etc.

 

[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.

 

[Pat Hartman]

I use arrays to randomize the characters. Tables would work but they are visible in the database. Arrays at least look like random strings.

 

[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

 

[Pat Hartman]

I store the token in a hidden table but I never store the decoded values. I decode the token each time the app opens. Since the FE's are replaceable, they can't hold the token and since my app has the option of using SQL Server or any other RDBMS for that matter, the tables are always accessible to the application owner. In the Access BE version the token is a USYS table which is hidden but in the SQL Server version, the USYS table is visible and that's why the token is never stored decoded anywhere. My clients and yours too probably are businesses and have signed contracts that forbid tampering with the source code, etc. So, they are far less likely to even attempt to hack the app than a person who downloads something from the internet.

 

[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