Opening ADODB Recordset Connection with a File DSN

[Paul H]

This is related to my previous post about keeping users out of the navigation pane and apparently how the previous developer managed it. She did not link certain tables in the Back End. She opened a connection using a system DSN, created a recordset, nabbed the information she wanted, then closed it up. So far so good, but the code requires a System DSN to do it. This will make distributing the new version much more difficult. We will have to edit the DSN on about 60 different computers on sites all around the state. Our IT department is going to have a fit. We were planning to use file DSNs to link the tables to the SQL Server back end. Is there a way we can point to these file DSNs or extract the contents and use it to make these connections.

This little piece of code here is causing the most trouble because it is called from about 20 different places.

 
Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open sql, "PAIRS", , adCmdText

This is the (modified) content of the file DSN we are using to link the tables.

[ODBC]
DRIVER=SQL Server
DATABASE=Test
APP=Microsoft® Windows® Operating System
Trusted_Connection=Yes
SERVER=abc12345

I am a real novice in setting up connections.

I'm see some examples, but I'm not clear on how to make this work.

[Paul H]

I'm back. I finally learned how to build a connection string and open it. It's rather simple when you know what you are doing.
I borrowed the syntax from this site and used a few lines from it. This is what I ended up with. SQL Server handles the security so we didn't need a User_ID or a Password. Once I pulled that out, it worked like a champ.

Dim strDatabase As String

gblServerName = "ABC12345"

strDatabase = "Test"
 
gblConnectString = "Driver={SQL Server};Server=" & gblServerName & ";Database=" & strDatabase &  ";"
    
Set gblConn = New ADODB.Connection

gblConn.Open gblConnectString

https://social.msdn.microsoft.com/Fo...forum=exceldev