Stopping DB after "free trial" etc

[Mike375]

as a point of interest, I never use macros ever. Well, only to run a function from a ribbon control. They seem problematic. Vba is far more versitile so it seems easier to stick with 1 method rather than 2.

I use both. Macros are the best for what i do with people with telemarketing. The reason is that often things are changed and it is very easy to do the macro over the phone. Just go to the 8th line down etc.

I also use them because if they are complicated and a change is wanted then I stick the new macro in a DB with and autoexec doing copyobject. Problem with code is I would need to send a form and their form will be different to what I have.

They are also very quick to make and reduce typos because of the drop down lists for Forms, Queries etc.

I think I am the only regular poster on the forum that uses macros.

If I could hide macros with MDE then I would not waste time with code on this exercise. The macro kills the code. I will dig the macro one up and attach for you, I think it still here, if not I will make another one, won't take long

 

[NigelShaw]

hi mike,

There really isn't any difference from my code getting the values and your form. I think the problem lies with your first form. On your form, both abc and xyz have a onclick value set. This in itself will hold the dates for non reversing whereas the code will calculate it. I will work something out as this too is now starting to bug me!

I can't see why it can't be done with only 1 form. It's more a case of where to store the values to prevent reversing. Either a table or a txt file or the registry.

It's my new years resolution to do this

Nigel

 

[Mike375]

hi mike,

There really isn't any difference from my code getting the values and your form. I think the problem lies with your first form.

My If Then would not read the value in the OnClick and also failed when I had the Date() in an unbound. So I swithed to that being inserted into a table and the If Then works. I suspect that the process of the opening of the other form might have meant the date() now was lost for a split second. But the macro worked. Perhaps that was just luck in that it either went a bit faster or a bit slower. But the macro condition would read direct from what was on OnClick

On your form, both abc and xyz have a onclick value set. This in itself will hold the dates for non reversing whereas the code will calculate it.

But the form must be opened in Design, then the OnClick value changed and the form saved to make the value permanent. The finishing value is permanent because it is entered in the normal way vai design view. But the updating based on current date is only permanent if done to the form in Design view. That is the reaon I use two form. The second form has the code/macro to open the first form in Design, change the OnClick value and then save/close the form.

I can't see why it can't be done with only 1 form. It's more a case of where to store the values to prevent reversing. Either a table or a txt file or the registry.

Two forms is 100% better than fiddling with the registry. Remember the program is delivered by email or disk and the buyer just copies it on. So it has to be self contained and with no problems.

It's my new years resolution to do this

Nigel

Please to hear it

 

[HiTechCoach]

But the form must be opened in Design, then the OnClick value changed and the form saved to make the value permanent.

This will not work in an MDE/ACCDE since you can not enter design mode on a form. I would only use this format for distribution unless you are also selling the source.

I would also store the the value in a custom database property.

 

[Mike375]

This will not work in an MDE/ACCDE since you can not enter design mode on a form. I would only use this format for distribution unless you are also selling the source.

I would also store the the value in a custom database property.

Oh shit, I forgot about that Back to the drawing board.

 

[Mike375]

To add (say) a Copyright Notice to the database,

• open a new module
• create a function named (say) tmp: Function Tmp() Dim DB As Database Dim P as Property Set DB = DBEngine(0)(0) Set P = DB.CreateProperty("Copyright Notice", DB_TEXT, "(C) JT Software 1995") DB.Properties.Append P End Function
• open the immediate window
• run tmp by entering ?tmp(). this will add the property to the DB
• run it again. This time it should give an error "Can't Append: Object already in collection"

And that's it. Don't bother saving the function - the property is now a permanent part of the database.

 

[NigelShaw]

Oh shit, I forgot about that Back to the drawing board.

Ha Haaa,

i thought about that too but i was out on the farm all day so couldnt post back. tonight, i will revise and add a table to it to place the calculated values. the table will be hidden so not noticable available. if i am correct, in the mde / accde, the tables are not available for deleting and if you place the code to prevent shift open, will never be available.

i will do this today for you.


Nigel

 

[Mike375]

Nigel,

I just tried an MDE and could delete tabes, queries and macros.

 

[HiTechCoach]

Ha Haaa,

i thought about that too but i was out on the farm all day so couldnt post back. tonight, i will revise and add a table to it to place the calculated values. the table will be hidden so not noticable available. if i am correct, in the mde / accde, the tables are not available for deleting and if you place the code to prevent shift open, will never be available.

i will do this today for you.


Nigel

So far, every trail/demo that uses a table to store the trail/demo info, I have been able to bypass with ease. In some cases I could even turn it into a full version.

If you are going to use a table, you will probably need to encrypt the data in the fields.

I would not store the values in a table. I would store the values in Custom Database properties. They are very difficult to find unless you know the actual property name. These are not the same thing as the Custom tab in the Database Properties sheet.

 

[The_Doc_Man]

The only way to make this work most of the time is to put some qualifying data outside of the database file. The registry is one possible place. You can try the xxxSettings functions that read and write entries in the registry. You could, for example, write something in HKEY_LOCAL_USER/Software/.... {some complex path}...

What to write? Take the current system time via the NOW() function, convert that to a LONG via CLNG( NOW() ), and store that as a longword. Don't write it as a string. Write it as an integer. You can convert it back to a DOUBLE with CDBL() and then re-cast it as a date with CDate(), which will give you midnight of the date it was written.

If you try to read it and you get back a value, it has been installed once before. Unless someone has an omnibus system protection package, they won't know you were updating the registry. (Defender Pro would warn the user of a registry update. Some others also do this.) Even if they do have the high-test a/v package, you can just warn them that you have to store some registry values during the install. Besides any references you might need updated, add in the extra value of the system date as an integer.

Another trick is to have an install procedure that creates a folder for your database, then puts some file in a child folder of same. Then inside your VBA, which must be an MDE file (not include source code) in order for this to be obscured, use the File System Object to look at the properties of the file you created in the child folder. Look at this file's dates. ALSO look at the dates of the child folder itself. Compare these to the system time. If someone wants to reset system time, they can do so. If they realize you created a file in a child folder, they might even recopy the file. But they might not think to reset the date/time on the child folder. Look at "last updated" for this folder.

What you would be looking for would be a file or folder update time that is later than the current system time. Which tells you that someone was scamming the system date on you. In that case, put up an "OK Only" dialog box and once they click OK, crash the application.

Third possibility is to write a file in an obscure location that you find by searching the \Program Files\Windows\System or \System32 folder for child folders. Do the same "file revision date" test on it as discussed earlier.

 

[HiTechCoach]

To add (say) a Copyright Notice to the database,

• open a new module
• create a function named (say) tmp: Function Tmp() Dim DB As Database Dim P as Property Set DB = DBEngine(0)(0) Set P = DB.CreateProperty("Copyright Notice", DB_TEXT, "(C) JT Software 1995") DB.Properties.Append P End Function
• open the immediate window
• run tmp by entering ?tmp(). this will add the property to the DB
• run it again. This time it should give an error "Can't Append: Object already in collection"

And that's it. Don't bother saving the function - the property is now a permanent part of the database.

You may also want to read this:

Real World Microsoft Access Database Protection and Security

 

[HiTechCoach]

The only way to make this work most of the time is to put some qualifying data outside of the database file. The registry is one possible place. You can try the xxxSettings functions that read and write entries in the registry. You could, for example, write something in HKEY_LOCAL_USER/Software/.... {some complex path}...

What to write? Take the current system time via the NOW() function, convert that to a LONG via CLNG( NOW() ), and store that as a longword. Don't write it as a string. Write it as an integer. You can convert it back to a DOUBLE with CDBL() and then re-cast it as a date with CDate(), which will give you midnight of the date it was written.

If you try to read it and you get back a value, it has been installed once before. Unless someone has an omnibus system protection package, they won't know you were updating the registry. (Defender Pro would warn the user of a registry update. Some others also do this.) Even if they do have the high-test a/v package, you can just warn them that you have to store some registry values during the install. Besides any references you might need updated, add in the extra value of the system date as an integer.

Another trick is to have an install procedure that creates a folder for your database, then puts some file in a child folder of same. Then inside your VBA, which must be an MDE file (not include source code) in order for this to be obscured, use the File System Object to look at the properties of the file you created in the child folder. Look at this file's dates. ALSO look at the dates of the child folder itself. Compare these to the system time. If someone wants to reset system time, they can do so. If they realize you created a file in a child folder, they might even recopy the file. But they might not think to reset the date/time on the child folder. Look at "last updated" for this folder.

What you would be looking for would be a file or folder update time that is later than the current system time. Which tells you that someone was scamming the system date on you. In that case, put up an "OK Only" dialog box and once they click OK, crash the application.

Third possibility is to write a file in an obscure location that you find by searching the \Program Files\Windows\System or \System32 folder for child folders. Do the same "file revision date" test on it as discussed earlier.

I do the same thing but store the data in a with a custom database property. This will work even if they copy the database to another machine since everything is self contained.

 

[HiTechCoach]

Here is code that I use to read and write text into a custom database property.


Place the following code in a module.

Public Function bimGetDBProp(strPropName As String) As Variant

Dim strOut As Variant
Const conPropNotFoundError = 3270

On Error GoTo ErrorHandler

bimGetDBProp = CurrentDb.Properties(strPropName)

Exit Function

ErrorHandler:
If Err.Number <> conPropNotFoundError Then
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
End If
Resume Next

End Function

Public Sub bimSetDBPropText(strPropName As String, strPropText As String)

Dim obj As Object
Const conPropNotFoundError = 3270
Const conPropNotFoundErrorDelete = 3265


On Error GoTo ErrorHandler
  
  CurrentDb.Properties.Delete strPropName
  Set obj = CurrentDb.CreateProperty(strPropName, dbText, strPropText)
  CurrentDb.Properties.Append obj
  Set obj = Nothing
 
 Exit Sub
 
ErrorHandler:
If Err.Number <> conPropNotFoundErrorDelete Then
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
End If
Resume Next


End Sub

 

[Mike375]

Here is code that I use to read and write text into a custom database property.


Place the following code in a module.

Public Function bimGetDBProp(strPropName As String) As Variant
 
Dim strOut As Variant
Const conPropNotFoundError = 3270
 
On Error GoTo ErrorHandler
 
bimGetDBProp = CurrentDb.Properties(strPropName)
 
Exit Function
 
ErrorHandler:
If Err.Number <> conPropNotFoundError Then
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
End If
Resume Next
 
End Function
 
Public Sub bimSetDBPropText(strPropName As String, strPropText As String)
 
Dim obj As Object
Const conPropNotFoundError = 3270
Const conPropNotFoundErrorDelete = 3265
 
 
On Error GoTo ErrorHandler
 
  CurrentDb.Properties.Delete strPropName
  Set obj = CurrentDb.CreateProperty(strPropName, dbText, strPropText)
  CurrentDb.Properties.Append obj
  Set obj = Nothing
 
 Exit Sub
 
ErrorHandler:
If Err.Number <> conPropNotFoundErrorDelete Then
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
End If
Resume Next
 
 
End Sub

Where does it go when the module runs. What do I reference etc.

 

[HiTechCoach]

Where does it go when the module runs.

You do not run a module. You can execute one of the a functions within the module.

It "goes" within the mde/mdb file as a database property.

What do I reference etc.

You should not have to set any references.

This may help:

to store a value in the property "Expiry", call the function bimSetDBPropText()

bimSetDBPropText "Expiry", Date() + 14

to retrieve a value from the property "Expiry", call the function bimGetDBProp()

strExpiry = bimGetDBProp("Expiry")

Hope this helps ...

 

[Mike375]

I put this in from the AllenBrown link you posted

Function Tmp()
Dim DB As Database
Dim P as Property
Set DB = DBEngine(0)(0)
Set P = DB.CreateProperty("Copyright Notice", DB_TEXT, "(C) JT Software 1995")
DB.Properties.Append P
End Function

open the immediate window
run tmp by entering ?tmp(). this will add the property to the DB
run it again. This time it should give an error "Can't Append: Object already in collection"

I ran the code from a RunCode macro and as he said it would run a second time without the message "Can't Append: Object already in collection"

But I could not find anything in DB properties to see where it went

In that code you just posted

bimSetDBPropText "Expiry", Date() + 14

What happend if the computer date is wound back.

Is this one called each time the program purchased is run

strExpiry = bimGetDBProp("Expiry")

 

[HiTechCoach]

But I could not find anything in DB properties to see where it went

The only way to see the value is with VBA code and you have to know the exact name of the property. That is what makes it so invisible to snoopers.

The functions give you the ability to store and retrieve data within a database as properties. They are also very difficult for anyone snooping around to find. What you store and how you used the data is up to you. I think of it as a way to create hidden constants that are able to be changed after the MDE is made.

 

[Mike375]

Hat cuistom data base settings is beyond me but I think I have the simple answer and it works in MDE this time

Use the MSysObjects system table.

The following code to make a table and puts Now() in the DateUpdate field of the MSysObjects system table. The table created has the Usys prefix so will be hidden. But if they use Options/View/System Objects and delete it that does not matter since it will be created each time the program is run. I would give some name so it like the other system objects.

On Error Resume Next
DoCmd.DeleteObject acTable, "UsysTest"
Dim dbs As Database, tbl As TableDef, fld As Field
Set dbs = CurrentDb
Set tbl = dbs.CreateTableDef("UsysTest")
Set fld = tbl.CreateField("test1", dbText)
tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

A query on MSysObjects table to for a Left() on the DateUpdate field to get the date and with the query name having the Usys prefix. If the choose View System Objects and delete the query then that simply mean nothing works.

The top record is compared to Date() and Date() must be >= for the program to run. In other words winding the computer back won't work because that will produce an earlier date.

The table MSysObjects can't be edited so they can't change the date there.

What ye all

 

[Mike375]

=Round(((Text2.OnClick)-([TrialDate]*86400))/86400,0)

[Text2].OnClick is the finish date *86400 and [TrialDate] is from MSysObjects system table

I just realised that I don't need a query to pull the date from the Now() entry in the MSysObjects system table as the Now() type entry less a date gives days plus part of a day and hence either > 0r < 0 bur Round fixes display.

Does anyone see a problem like the one where I forgot about MDE and opening form in Design view etc

I think?? this has it.You can't delete or update the MSysObjects system table and deleting the table that is created to get the date does not matter since it gets deleted at the start of the code and then recreated.

 

[NigelShaw]

=Round(((Text2.OnClick)-([TrialDate]*86400))/86400,0)

[Text2].OnClick is the finish date *86400 and [TrialDate] is from MSysObjects system table

I just realised that I don't need a query to pull the date from the Now() entry in the MSysObjects system table as the Now() type entry less a date gives days plus part of a day and hence either > 0r < 0 bur Round fixes display.

Does anyone see a problem like the one where I forgot about MDE and opening form in Design view etc

I think?? this has it.You can't delete or update the MSysObjects system table and deleting the table that is created to get the date does not matter since it gets deleted at the start of the code and then recreated.

Hi Mike,

as a question. if you are entering the date for the trial to end which is part of the calculation into the table and the table is deleted, wouldnt the date that is reproduced on the creation of the new table be different to that originally entered into the field thus giving a new date for the trial to finish?

Nigel