There is a problem with Access links to SQL Server in that when
you create links through the UI they are dependent on ODBC DSN's
(Data Source Names) being on the client desktop. This is pain in
the neck if you will be using the database from multiple
INFORMATION PROVIDED IN THIS DOCUMENT AND THE DNSStripper
Utility IS PROVIDED
"AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED. THE
USER ASSUMES THE ENTIRE RISK OF RUNNING THIS SOFTWARE.
Licensing: Anyone can use this
program or modify the source code in anyway.
The author makes no warranties as to its use or misuse.
© Paul Litwin
Thus, I saw a need and I created an Access add-in called DSN
Stripper. The idea is this: go ahead and create your SQL Server
linked tables using the UI and then when you are done use this
add-in to convert the links to DSN-less links. That is, it
converts the links to links that do not depend on DSNs being
created on the user's desktop. I created it as an add-in which
makes it easy to use across Access databases.
Should work with Microsoft Access 2000, 2002, and 2003.
To install the utility as an Access add-in:
- Unzip the zip file and place DSNStripper.mda in any
- Open any Access database and select Tools|Add-Ins|Add-in
- Click Add New and navigate to the DSNStripper.mda file.
- Click close at the Add-in dialog.
To use the add-in:
- Create some links to SQL Server tables using the UI.
- Startup the add-in by selecting Tools|Add-Ins|DSN
- The add-in needs to know the name of the SQL Server
server, which defaults to localhost. (For future use, it
remembers the last setting).
- Optionally, it will also strip off the annoying "dbo_"
prefix that Access adds to the name of the link.
- Click the Convert button and close the form when it is
Hope it helps,