Create DSN-less connection to SQL Server

[Josef P.]

You will never be able to get Null from a string parameter/variable in VBA.
In SQL or via a recordset, the data type nvarchar & Co can already contain Null, but not a String parameter/variable in VBA.

Public Sub TestMe(Optional ByVal x as String):
TestMe Null => Error: Invalid use of Null
Call TestMe() => x is empty (vbNullstring) but not Null

Note: However, I recommend always adding the default value (except for Variant, if you want to check IsMissing), as this makes the code easier to read in my opinion. Even if it would not be necessary with vbNullstring, for example.
Public Sub TestMe(Optional ByVal x as String = vbNullString, Optional Byval y as Long = 0) is easier to read because you don't have to think about the default value of the respective type.

 

[Pat Hartman]

IsMissng(xxx) works when the call omits the last two arguments but not if you include them but leave them null.

 

[Josef P.]

Public Sub TestMe(Optional x As String)
    If IsMissing(x) Then
        MsgBox "IsMissing(x) = True" ' <-- will never be shown
    ElseIf Len(x) = 0 Then
        MsgBox "Len(x) = 0, StrPtr(x)=" & StrPtr(x)
    Else
        MsgBox x
    End If
End Sub

IsMissing check works, but it will never be True in this example.
Call TestMe() => "Len(x) = 0, StrPtr(x) = 0"

 

[cheekybuddha]

IsMissing() only works with Variant parameters

See the help here

Syntax​

IsMissing(argname)

The required argname argument contains the name of an optional Variant procedure argument.

(Help's emphasis, not mine!)

 

[JohnPapa]

That is fine but not if you have BE's from multiple databases linked. I have a different, customized link procedure in a different application because there are two BE's involved. The primary one for the application plus one with reference information from a different application

If you have multiple BEs then in the table you hold the info (in your case tblTableList) you could store for each table to be linked the Server, DB etc

 

[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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

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.