Create DSN-less connection to SQL Server

[JohnPapa]

As a general comment to everyone including Pat (who was using Function AttachDSNLessTable ), do you call this function each time you want to create a link to a BE table?

It appears that you can save time if it is called once and inside the function loop through all the tables with something like,

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td

 

[JohnPapa]

If you are deleting all the linked tables first and using some other method of determining which tables to relink (which I do in other databases), that is fine and possibly more efficient.

I found a bit of a problem if you want to delete all linked tables, for a software package that will be distributed to clients. Every client will have a different Serial number and a different SQL Server name and pathname. To be able to create links for all the tables you need a link to a BE table which contains this information and this link needs to be available. Like a "chicken and egg" thing.

So if you are going to delete all linked files and you need access to say tblSerial, you would have something like this

'Delete linked tables
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
    
For Each tdf In CurrentDb.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") And (Len(Trim(tdf.Connect)) > 0) And tdf.Name <> "dbo_tblSerial" Then
        CurrentDb.TableDefs.Delete tdf.Name
    End If
Next

If anybody can come up with an idea where there is no need for at least one linked table, please let me know.

 

[Minty]

You could store the data in code in an array, then create a table from it on opening if it needs to be in a table.
Or a local deep hidden table.

 

[JohnPapa]

You could store the data in code in an array, then create a table from it on opening if it needs to be in a table.
Or a local deep hidden table.

Let me give a real example for say a client with a software package with Serial ID = 12. How would the FE software (an accdb) know that it is trying to connect to the client with Serial ID = 12? How would it know the Server location (SQL Server)?

 

[Minty]

Where is the 12 stored or identified?

 

[Gasman]

You could set the properties of the FE before you distribute the FE?

 

[JohnPapa]

Where is the 12 stored or identified?

The Serial ID = 12 is stored in tblSerial

 

[JohnPapa]

You could set the properties of the FE before you distribute the FE?

The FE is the same for all clients

 

[isladogs]

I use DSN less connections with many clients, all of whom obviously have different links for SQL Server tables.
The connection details for each client are stored in an Access 'side-end' configuration database used by the app administrator only and stored in the same folder. Before distributing new versions of the FE, I break all BE links
In order to relink connections when the app admin downloads the latest version of the FE, code in the FE runs automatically to retrieve those connection details then relinks the SQL Server tables. After that is completed, the admin makes the new FE available on the network for each user.

A different process then runs so each user automatically gets the latest version from the local network
As far as the client and individual users are concerned, it all 'just works'

 

[Gasman]

The FE is the same for all clients

Yes, it would be, but those particular properties would be unique for each client.
The data is not the same in each FE, nor do the properties have to be?

So you run a program that sets the attributes in the FE for a client, then pass on that program to the client

 

[JohnPapa]

I use DSN less connections with many clients, all of whom obviously have different links for SQL Server tables.
The connection details for each client are stored in an Access 'side-end' configuration database used by the app administrator only and stored in the same folder. Before distributing new versions of the FE, I break all BE links
In order to relink connections when the app admin downloads the latest version of the FE, code in the FE runs automatically to retrieve those connection details then relinks the SQL Server tables. After that is completed, the admin makes the new FE available on the network for each user.

A different process then runs so each user automatically gets the latest version from the local network
As far as the client and individual users are concerned, it all 'just works'

If the location of the SQL Server changes for a client, there is a need to be able to change the Access 'side-end' configuration database, to include the new Server location. In its simplest format this 'side-end' db can be a .accde that you would need to log in and change the server pathname for the specific client. Once this is in place, you need a way to connect to this db from your main program, to create the links to the tables.

 

[JohnPapa]

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.

 

[JohnPapa]

Yes, it would be, but those particular properties would be unique for each client.
The data is not the same in each FE, nor do the properties have to be?

So you run a program that sets the attributes in the FE for a client, then pass on that program to the client

Isladogs side db would solve the problem.

 

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