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

Posted on 2008-11-05
Medium Priority
Last Modified: 2010-04-21
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
Question by:DHompster
  • 2
LVL 22

Accepted Solution

Kelvin Sparks earned 1000 total points
ID: 22891215
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.


''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
        Set tdf = db.CreateTableDef(rsTables!TableName, dbAttachSavePWD, rsTables!RemoteName, strConnect)
        db.TableDefs.Append tdf

Open in new window

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 22891221
If you are linking to SQL 2000, replace the reference to {SQL Native Client}
with {SQL Server}


Author Closing Comment

ID: 31513722
Excellent!!!!  Thank you!!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question