Use VBA to link to SQL Server tables using File DSN instead of System DSN

DHompster
DHompster used Ask the Experts™
on
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
   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I prefer to link using a dsn-less connection. It does mean that you cannot manually link tables, but removes the need for a dsn altogether.

The code below is out of an existing app of mine, but could be adapted to work.


Kelvin


''Build the dsn-less connection string
strServer = DLookup("[ServerName]", "USysDatabaseLink", "[LinkID] = " & lLink)
strDatabase = DLookup("[DatabaseName]", "USysDatabaseLink", "[LinkID] = " & lLink)
strConnect = "ODBC;Driver={SQL Native Client};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=yes"
sSQL = "SELECT * FROM USysTables where Not IsNull(RemoteName);"
 
Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!TableName Then
            db.TableDefs.Delete rsTables!TableName
            Exit For
        End If
    Next
        Set tdf = db.CreateTableDef(rsTables!TableName, dbAttachSavePWD, rsTables!RemoteName, strConnect)
        db.TableDefs.Append tdf
    rsTables.MoveNext
Loop

Open in new window

If you are linking to SQL 2000, replace the reference to {SQL Native Client}
with {SQL Server}

Kelvin

Author

Commented:
Excellent!!!!  Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial