Create DSN-less connection to SQL Server (1 Viewer)

[JohnPapa]

I am referring to Method 1 in

Create DSN-less connection to SQL for linked table - Microsoft 365 Apps | Microsoft Learn

The beginning of the module is as follows

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

I do not understand what "stLocalTableName " refers to.

 

[CJ_London]

It’s the name of the local table - as in linked table

See the code and you will see where it is being used

 

[JohnPapa]

It’s the name of the local table - as in linked table

See the code and you will see where it is being used

Maybe I do not understand what local table is.

What I expect, and I could be wrong, is to be able to dynamically create a link to a table in a database in the SQL server. I do not understand why a local table is involved.

 

[CJ_London]

What I expect, and I could be wrong, is to be able to dynamically create a link to a table in a database in the SQL server. I do not understand why a local table is involved.

what do you expect a 'link to a table' to look like? In my book it is a (local) linked table

even your link title says 'How to create a DSN-less connection to SQL Server for linked tables in Access'

perhaps if the variable was called stLocalLinkedTableName it would be clearer?

 

[isladogs]

Its the local name (alias) for the linked table e.g. dbo_TableName => tblTableName.
The local name can be identical to or different from the name of the table in SQL Server

 

[Pat Hartman]

The name of the table in SQL Server is DBO.TableName Access does not allow dots in object names so it substitutes an underscore for the dot. You can also remove the dbo priefix entirely which many people do.

I happened to be working on this exact code today and that posted code has two errors.
1. the two optional arguments are defined as String. This raises an error if the values are not present so you have to change the data type for username and password to Variant.
2. If Len( stUsername ) = 0 also doesn't work because when stUsername is null, the Len() function returns null so it will never be zero. I changed that line to:
If stUsername & "" = "" Then

This code is out of context and needs to be used within a loop. You can create a table and in that table add a row for each table with two columns. One for the SQL Server name and one for your local linked name. You can loop through this table and for each row in the table, pass the values into the procedure.

There is another problem which I realized later but didn't fix is that the loop in the posted code always goes through every table in the database for each table you are trying to link. When the tabledef name matches the local table name you feed into the code, it should delete the link as it does but then it should exit the procedure.

I attached the database I am working on. It may be more confusing than what you are starting with but it has the code in context so you can see how I am using the procedure. Of course, it isn't going to work because it is tied to my BE but it still might help your understanding.

I added a table that has the defaults saved in it. and this is used to populate the form fields so I don't have to keep typing stuff in. It also substitutes a different server name when it is me "pat" logged in.

This database is going to be sent to a client tomorrow. They will need to extract it from the zip file and link it to THEIR BE. Then once the tables are relinked, they will run the stats which is a bunch of queries that count stuff and save the results in local tables. then the client will send the database back to me for review.

 

[JohnPapa]

The name of the table in SQL Server is DBO.TableName Access does not allow dots in object names so it substitutes an underscore for the dot. You can also remove the dbo priefix entirely which many people do.

Hi Pat and many thanks for your explanation, problem solved.

I do not want to be pedantic BUT if the explanation says

'// stLocalTableName: Name of the table that you are creating in the current database

I expect to define a LOCAL table. Maybe a better explanation would have been
stLocalLINKTO SERVERTableName

For the rest of your message, I will read it through carefully.

 

[JohnPapa]

what do you expect a 'link to a table' to look like? In my book it is a (local) linked table

even your link title says 'How to create a DSN-less connection to SQL Server for linked tables in Access'

perhaps if the variable was called stLocalLinkedTableName it would be clearer?

Sorry, just saw your "stLocalLinkedTableName" suggestion. Yes, that would be better, as I also suggested.

 

[JohnPapa]

2. If Len( stUsername ) = 0 also doesn't work because when stUsername is null, the Len() function returns null so it will never be zero. I changed that line to:
If stUsername & "" = "" Then

Going through your message
Instead of If Len( stUsername ) = 0
can't I use If Len( nz(stUsername,0) ) = 0

 

[Josef P.]

Instead of If Len( stUsername ) = 0
can't I use If Len( nz(stUsername,0) ) = 0

I would leave the parameter of the function set to String and ensure that a string (or empty string) is passed when the function is called if a value is to be set for the optional parameter.

If you want to use the variant data type: If Len( nz(stUsername,0) ) = 0 or If Len( stUsername & "" ) = 0 or If stUsername & "" = ""
Or you can reverse the order:

If len(stUsername) > 0 then
  ' with sql user name
else
  ' with trusted connection
end if

BTW: I do not find stUsername suitable as a name for a variant data type.

 

[JohnPapa]

I would leave the parameter of the function set to String and ensure that a string (or empty string) is passed when the function is called if a value is to be set for the optional parameter.

If you want to use the variant data type: If Len( nz(stUsername,0) ) = 0 or If Len( stUsername & "" ) = 0 or If stUsername & "" = ""
Or you can reverse the order:

If len(stUsername) > 0 then
  ' with sql user name
else
  ' with trusted connection
end if

BTW: I do not find stUsername suitable as a name for a variant data type.

Pat may have some input to your suggestion.
varUsername is more appropriate

 

[gemma-the-husky]

I thought I would be able to connect a SQL server table by constructing the connect property as a string, but I couldn't do it. I found I had to set the connection details, and then the string was formed as a consequence of setting those details.

Maybe I wasn't creating the string correctly but I wasn't able to do it, from memory.

 

[Josef P.]

I read that from Pat, but I don't see the advantage of defining a parameter unclearly in the function interface.

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, _
      Optional stUsername As String, Optional stPassword As String)

with call:

dim UserName as String
dim  Password as String
UserName = Nz( ..., vbnullstring) ' Why should you get to Null here?
...
If AttachDSNLessTable("Table123", "dbo.Table123", "ServerXyz\instance", "MyDatabase", UserName, Password) then
...

vs.

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, _
      Optional varUsername As Variant = Null, Optional varPassword As Variant = Null)

with call:

dim UserName as Variant
dim  Password as Variant
UserName = ... ' Why should you get to Null here?
...
If AttachDSNLessTable("Table123", "dbo.Table123", "ServerXyz\instance", "MyDatabase", UserName, varPassword) then
...

 

[JohnPapa]

The name of the table in SQL Server is DBO.TableName Access does not allow dots in object names so it substitutes an underscore for the dot. You can also remove the dbo priefix entirely which many people do.

I happened to be working on this exact code today and that posted code has two errors.
1. the two optional arguments are defined as String. This raises an error if the values are not present so you have to change the data type for username and password to Variant.
2. If Len( stUsername ) = 0 also doesn't work because when stUsername is null, the Len() function returns null so it will never be zero. I changed that line to:
If stUsername & "" = "" Then

This code is out of context and needs to be used within a loop. You can create a table and in that table add a row for each table with two columns. One for the SQL Server name and one for your local linked name. You can loop through this table and for each row in the table, pass the values into the procedure.

There is another problem which I realized later but didn't fix is that the loop in the posted code always goes throw every table in the database for each table you are trying to link. When the tabledef name matches the local table name you feed into the code, it should delete the link as it does but then it should exit the procedure.

I attached the database I am working on. It may be more confusing than what you are starting with but it has the code in context so you can see how I am using the procedure. Of course, it isn't going to work because it is tied to my BE but it still might help your understanding.

I added a table that has the defaults saved in it. and this is used to populate the form fields so I don't have to keep typing stuff in. It also substitutes a different server name when it is me "pat" logged in.

This database is going to be sent to a client tomorrow. They will need to extract it from the zip file and link it to THEIR BE. Then once the tables are relinked, they will run the stats which is a bunch of queries that count stuff and save the results in local tables. then the client will send the database back to me for review.

Hi Pat, I am going through the code you sent over. You can either have the FE with the links to the BE tables or each time create the links to the BE tables from scratch (no links present). In the second case, it would be helpful to be able to delete all links in one loop (reverse count) rather than one at a time with something like

Dim lngCounter as long
Dim lngLinkedTableNum As long
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
lngLinkedTableNum  = dbs.TableDefs.Count - 1

For lngCounter = lngLinkedTableNum To 0 Step -1
    Set tdf = dbs.TableDefs(lngCounter )
    If Left(tdf.Connect, 5) = "ODBC;" Then
        DoCmd.DeleteObject acTable, tdf.Name
    End If
Next lngCounter

 

[cheekybuddha]

Agree with Josef here.

I happened to be working on this exact code today and that posted code has two errors.
1. the two optional arguments are defined as String. This raises an error if the values are not present so you have to change the data type for username and password to Variant.
2. If Len( stUsername ) = 0 also doesn't work because when stUsername is null, the Len() function returns null so it will never be zero. I changed that line to:
If stUsername & "" = "" Then

The above is incorrect.

Declaring the optional arguments As String will not raise an error if they are missing. They will simply have zero length.

Perhaps Pat is confusing checking for whether optional arguments have been passed using the IsMissing() function - this would only work if the arguments were variants.

However, if used here (IsMissing) there would be no advantage since you would still want to test the length of the strings passed before adding them to the connection string anyway, and you lose the strict typing.

 

[Pat Hartman]

can't I use If Len( nz(stUsername,0) ) = 0

You can use whatever you want. You just can't use the code as it is in the procedure.

In the second case, it would be helpful to be able to delete all links in one loop (reverse count) rather than one at a time with something like

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

Declaring the optional arguments As String will not raise an error if they are missing. They will simply have zero length.

Except the code fails with an error. Try it. Of course you can trap it and just move on.

The error happens on the Do Until line. Substituting Variant for String for the last two arguments solves the problem. I was surprised at the error also since null should be allowed since the arguments are optional. But here you have it.

The problem with the If Len() as written is that it returns null so instead of taking the true path of the code, it takes the false path. If you change the logic to check for > 0 rather than = 0 (and obviously swap the code), you can use the Len() method.

 

[cheekybuddha]

The error happens on the Do Until line. Substituting Variant for String for the last two arguments solves the problem.

Shows you should validate your inputs!

You are passing Null(s), not omitting the optional arguments.

You could just do:

If AttachDSNLessTable(rs!Name, rs!ForeignName, Me.txtUseThisServer, Me.txtDatabase, Me.txtUserName & "", Me.txtPassWord & "") = False Then
' ...

Then you don't have to futz with the signature and body/logic of the function.

 

[Pat Hartman]

Shows you should validate your inputs!

No, the user name and password are OPTIONAL. So, null is valid.

Perhaps just omitting the two optional arguments would be better. I'll check to see what that does to the Len() later.

 

[Josef P.]

Public Sub TestMe(Optional ByVal x as String)
is equal to
Public Sub TestMe(Optional ByVal x as String = vbNullString)
A String parameter will never be Null.

Null is only possible with Variant in VBA:
Public Sub TestMe(Optional ByVal x as Variant)=>Call TestMe() => IsMissing(x) = true!
vs.
Public Sub TestMe(Optional ByVal x as Variant = Null)

 

[Pat Hartman]

A String parameter will never be Null.

I guess you and Access disagree on this point.