Use VBA to link to SQL Server tables using File DSN instead of System DSN
Posted on 2008-11-05
My Access application currently uses System DSN links to a SQL Server DB. I have a routine that allows the linked tables to be switched back and forth between the live data and a test database. I now have some users who don't have the System DSN data source. Can I use a similar VBA routine to use the File DSNs instead of the System DSNs?
The connection string I currently use is as follows:
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & DSN & ";Description=" & DSN & ";APP=Microsoft Office 2003;WSID=" & LinkWSID & ";DATABASE=" & DSN & ";TABLE=dbo." & rs!ContainerName, acTable, "dbo." & rs!ContainerName, rs!ContainerName, 0