DSN Connection

Posted on 2012-08-21
Last Modified: 2012-08-23
I've inherited an access database that has a SQL Server back end.  When looking at the Linked Table Manager, it shows, after the table name, (DSN = LAAT; Database = LAAT).  The table data is available even though there is no LAAT datasource on the workstation.

Today we copied the tables for the LAAT to a new SQL Server.  However, no matter what I've tried, I cannot view the data in the new database unless I add a datasource connection to the workstation  We have users across the company in multiple locations, and setting up the ODBC datasource on each workstation would be time consuming which I assume is why this type of connection was selected initially.  Does anyone know how I can view the new table's data without visiting every workstaton to set up a new datasorce?
Question by:kshumway
    LVL 84

    Accepted Solution

    You can relink tables in code with DSNless connections:

    Or you can build the DSN with VBScript. This post shows how to do that:

    Of the two, the DSNless connections would be my choice.

    Author Comment

    Thank you.  I am looking at the DNSLessLink article.

    Author Comment

    I have been playing with this by creating two small database (one table each) on each of the servers.  KSQLDB is the server where the database currently resides.  KSQL is the new server.  I am getting the following error on the line
    dbCurrent.Tabledefs.Append tdfCurrent:

    ODBC - connection to '(sql server)KSQL' failed. (3151) encountered.

    My connection string is:

    ODBC:DRIVER=(sql server);Database=TestKSQL;Server=KSQL;Trusted_Connection=YES;

    Do you know what I am doing wrong or what I need to do to get this to work?
    LVL 14

    Assisted Solution

    by:Bill Ross

    Replace the : with ; and the parens with brackets like below.

    ODBC;DRIVER={SQL Server};Database=TestKSQL;Server=KSQL;Trusted_Connection=YES;

    If the KSQL is on a different port than 1433 or has a named instance then you will need to include that in the server parameter.

    Server=KSQL,1234 (for port)



    Author Closing Comment

    Thank you both so much.  It works fine on my test database.  You made my day!

    Author Comment

    Just wanted to add that I have successfully updated the production database that needed to point to the new server.  Thank you again for the help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now