Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DSN Connection

Posted on 2012-08-21
6
Medium Priority
?
718 Views
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?
Linked-Table-Manager.doc
0
Comment
Question by:kshumway
  • 4
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38318732
You can relink tables in code with DSNless connections:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

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

http://blogs.technet.com/b/heyscriptingguy/archive/2004/11/10/can-i-create-and-delete-a-dsn-using-a-script.aspx

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

Author Comment

by:kshumway
ID: 38319888
Thank you.  I am looking at the DNSLessLink article.
0
 

Author Comment

by:kshumway
ID: 38322001
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Assisted Solution

by:Bill Ross
Bill Ross earned 1000 total points
ID: 38322886
Hi,

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)
Server=KSQL\InstanceName

Regards,

Bill
0
 

Author Closing Comment

by:kshumway
ID: 38324410
Thank you both so much.  It works fine on my test database.  You made my day!
0
 

Author Comment

by:kshumway
ID: 38325712
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!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

580 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