Create DSN with server alias and port number?

[JayT]

Hi,

I have a module that create the system DSN (odbc) and it works. Now i need to modify this module to add a server Alias with port number for a connection to SQL Server. I searched all over the internet but found nothing regarding the option for port number and server alias. I tried adding the keyword "port=" and assign the port number but it doesn't work. The code shown below works without the Port option

 & Chr$(0) & _
"Port=" & sPort

Is there a way to accomplish this task using the existing code?

Any input is much appreciated.

Thanks,

I have attached the images of the process to create the server alias using "odbcad32".

Option Compare Database

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.dll" _
(ByVal hwndParent As Long, _
ByVal fRequest As Long, _
ByVal lpszDriver As String, _
ByVal lpszAttributes As String) As Long



Public Function MakeDSNx(sDSN As String, sServer As String, sDB As String, sDesc As String, sPort As String)

'Create ODBC System Data Source
Dim lngRet As Long
Dim strAttributes As String

'Set the detail of DSN
strAttributes = "Dsn=" & sDSN & Chr$(0) & _
"Server=" & sServer & Chr$(0) & _
"Database=" & sDB & Chr$(0) & _
"Description=" & sDesc & Chr$(0) & _
"Trusted_Connection=Yes" [B][COLOR=Magenta]& Chr$(0) & _
"Port=" & sPort[/COLOR][/B]

'Create DSN
'0& = Null, 4 = Add a System DataSource
lngRet = SQLConfigDataSource(0&, 4, "SQL Server", strAttributes)

If lngRet = 0 Then
    MsgBox Err.Description
End If

End Function


Function tsx()

Call MakeDSNx("VirtualX", "VirtualX", "VirtualX", "VirtualX", 1234)

End Function

 

[Banana]

If you are doing it in code, why go to trouble of creating DSN when you can use DSN-less connection?

Looking at Carl's site, it seems to be not a standalone option but rather is paired with IP address...

To connect to SQL Server running on a remote computer (via an IP address)

oConn.Open "Driver={SQL Server};" & _
           "Server=xxx.xxx.xxx.xxx;" & _
           "Address=xxx.xxx.xxx.xxx,1433;" & _
           "Network=DBMSSOCN;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named 
Pipes (Q238949)

Source: Here

 

[JayT]

If you are doing it in code, why go to trouble of creating DSN when you can use DSN-less connection?

Banana, thanks for your quick response. The solution looks promising. However, I need a way to create odbc connection so that user can create his/her own query within the access database.

The solution you suggested requires the sql statement within the module?

 

[Banana]

No; this is a complete connection string so you do not have to use DSN to connect.

I wrote an article explaining the DSN-less connections. See if that helps clears thing up.

 

[JayT]

No; this is a complete connection string so you do not have to use DSN to connect.

I wrote an article explaining the DSN-less connections. See if that helps clears thing up.

It's getting better!!! I read your article, it's helpful. I'm going to give the solution you suggested a try. I'll come back with update

And just in case anyone looking at this post and wonder of how to get the ip address of the SQL server (in my case), use the [PING] command line. See the attached image if you're not sure how to do it.

Meanwhile, I'm open for other suggestions.

Thanks.