Create DSN-less connection to SQL Server

[JohnPapa]

In my sold to the public apps, when a new version of the app is delivered to the client, the procedure is for the admin to unzip the FE and open it locally. The app checks a critical table and if it isn't found, opens the link form. The admin then links to the server. The same procedure works whether the BE is ACE or SQL Server. Once the BE is linked, the admin renames the previous FE and adds the new FE to the distribution folder and sends an email that the new version is now available.

Both the FE and BE have version tables and each time the FE opens it compares the FE version with the BE version and only opens if they are compatible.

The final part is what I call a "token". It is 16 characters and has encoded in it the app's expiration date as well as what additional features the client has purchased.

The opening process continues after the table links are confirmed and the versions are confirmed. Then the expiration date and features are extracted from the token and saved for easy reference on the login form which hides itself after everything is confirmed and it opens the main menu. If the product has expired, the app won't open. If a feature wasn't purchased, it usually doesn't even show up on the menus but if it does, it won't operate if the token doesn't say it is available.

Our maintenance period is usually one year. Six weeks out, there is a countdown on the menu to remind the client to resubscribe.

These apps are not ones where there would be any incentive to steal the app and redistribute it. The market is very small and the potential purchasers would all be competitors. It isn't a very good business plan to give your tools to your competitors, or even to sell it to them so we rely on our contract to enforce valid usage beyond what I described. We know our clients and who is authorized to contact us for support.

Hi Pat,

Inspired by some code you sent a couple of days ago, for which I thank you again, I thought of the following procedure and once it is settled I will post the code. Also, I use IslaDog's side db idea, with the only difference being that I can have a local table instead of another db.
1) Delete all linked files (except the temporary and system files)
2) All clients have a Serial number, so every FE has a table which has information for all the clients and includes the Serial ID, the Server name including the pathname and a boolean field which indicates the SerialID-Server combination to be used. The first time the FE opens in a form where we need to choose the SerialID-Server combination.
3) I then create the links for all the tables, based on the SerialID-Server combination and if there is a problem, such as invalid server pathname, a Server info form appears which prompts for the correct server info. all table links are deleted and the process is repeated.
4) On exit all table links are deleted, but the boolean in the local file continues to point to the correct SerialID-Server info.

 

[isladogs]

Question Isladogs: Do you use a separate side db for each client or do you have a single side db with all the server info for all the clients and maybe have a boolean select field to indicate which server info to use?
I just created a .accde with one table and linked it to the main program. Would work fine.

The 'side-end' configuration db is supplied with the app on purchase & is initially blank. On initial setup the connection details to the backend are entered & stored in the side-end db for future use.
On the rare occasions that these settings need to be changed, these can be updated by the program administrator.

 

[nector]

If you create the DSN less string then there is completely no need to use VBA , the only thing you need to do is to copy the created DSN file to the target computer and automatically it will pick.

Personally that is the way I work no one is complaining of the tables not properly linked, there is no need for VBA unless you want to be swicthing from Access BE to SQL server BE

 

[JohnPapa]

If you create the DSN less string then there is completely no need to use VBA , the only thing you need to do is to copy the created DSN file to the target computer and automatically it will pick.

Personally that is the way I work no one is complaining of the tables not properly linked, there is no need for VBA unless you want to be swicthing from Access BE to SQL server BE

If I use DSNless do I create a DSN file?

 

[Minty]

If you create the DSN less string then there is completely no need to use VBA , the only thing you need to do is to copy the created DSN file to the target computer and automatically it will pick.

This only works if you choose a file DSN, and you still have to select it.

Personally that is the way I work no one is complaining of the tables not properly linked, there is no need for VBA unless you want to be switching from Access BE to SQL server BE

If you regulalry switch between Dev, Test and Production SQL backends then you want to automate it, and that's when the VBA is virtually essenstial .

Also a file DSN will have the password stored in it, which is just as easily read as a linked table connection string.
I thought the idea was to hide the connection details, in which case sticking everything into VBA and compiling to an accde does that very sucessfully.

 

[spaLOGICng]

If I use DSNless do I create a DSN file?

No. the "...less" part of the "DSN-less" implies that it does not require a DSN File.

 

[JohnPapa]

No. the "...less" part of the "DSN-less" implies that it does not require a DSN File.

Many thanks for stating the obvious!!! My comment was for Minty's reference to a FileDSN.

 

[JohnPapa]

The 'side-end' configuration db is supplied with the app on purchase & is initially blank. On initial setup the connection details to the backend are entered & stored in the side-end db for future use.
On the rare occasions that these settings need to be changed, these can be updated by the program administrator.

I wanted to come back to your suggestion about the use of an external file, to let you know that it proved necessary, especially when sending updates of the software. Thanks for the suggestion.

 

[isladogs]

Glad it helped you. I've used that approach for over a decade and it works well