DSNless connection on specific port using windows authentications

Question

Post reply

DSNless connection on specific port using windows authentications

Etsoft

SSC Rookie

Points: 33
More actions
August 27, 2020 at 12:06 pm

#3784080

Hi,
i'm trying to connect with an dsnless connection dro microsoft access to a sql server on a specified port using windows authentication.
this function does not work :
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function

Even after adding the port.

Please assist.

Thanks in advance

E.Traas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 2

My first thought - what is the error?

Without the error message, it is hard for us to help.

Also, when you say "DNSLess", i am assuming you mean you are connecting to the IP of the server, not to the server name?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Etsoft SSC Rookie Points: 33 More actions · Answer 3

Hi, thanks. I was connection with the servername. I want to use a dsnless connection method because i want to prevent for users to make a obdc connection by hand.

Regards

Erwi

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 4

Not entirely sure why that would need to be "DNSLess"... you can use DNS without using ODBC. But maybe I'm just misunderstanding the terminology.

Which brings me back to the first question - what is the error message you got?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.