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.
Code:
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.