We help IT Professionals succeed at work.

How to create Global DSN

Hi Experts,
I am trying to create a DSN link between MS Access 2003 and SQL 2000 (link the tables)
I want to create a DSN(not a system DSN) where its not system restricted anyone can use that DSN from different machine .
I also tried using file DSN but I dont see that from different machine
any idea ??

Thanks
Comment
Watch Question

Database Administrator
BRONZE EXPERT
Commented:
If you create a file DSN just copy it to C:\Program Files\Common Files\ODBC\Data Sources on each client machine.

There is no way to create a DSN that can be globally accessed from different machines.
BRONZE EXPERT
Commented:
If you are on a network, place the file dsn on a network share that is available to every user. There is no need to deploy the dsn to each pc as they use the system


Kelvin
Chris MangusDatabase Administrator
BRONZE EXPERT

Commented:
They won't be able to get to that DSN.  The only file DSNs that a machine can "see" will be in the folder I specified, unless you do a reg hack on the machine.  Then, you'll likely break something else.
BRONZE EXPERT
Commented:
Rubbish! I have used this technique for years in a large financial corporation. I you create a file dsn you have a simple file with a dsn esxtension. You can place this or move it anywhere you want (including network drives). From Access you then create a link to ODBC tables, navigate to the file you have created & link to the tables.

Distribute the Access front end to each user & provising they can see the network drive, they are connected to the SQL Server.

Kelvin
BRONZE EXPERT
Commented:
By the way, a file dsn does not use/need registry settings. It is nothing more than a file.
Chris MangusDatabase Administrator
BRONZE EXPERT

Commented:
I know it's just a file.  But the location of file DSN's is determined by a registry key.

For my edification, how are you pointing to a file DSN on a network location?
Chris MangusDatabase Administrator
BRONZE EXPERT

Commented:
Ok...I see how you're doing it now.  FWIW, the reg key describes the *default* location of file DSN's.

I now see how you're pointing to a network location for file DSN's!

Thanks for the education...
Top Expert 2007

Commented:
This has helped me with connection strings in the past:

 1. In Notepad, or any other text editor, create a file named Temp.udl  

 2. Leave the file empty and save it to the Desktop

 3. Double-click the Temp.udl file

 4. The Data Link Properties dialog box appears

 5. In the Data Link Properties dialog box, click Provider. Select the appropriate provider from the list

 6. Click Connection and select the corresponding name of the server and the database
    Type the User Name and the Password
 
 7. Click Test Connection to test the connection

 8. Click OK

 9. Right-click Temp.udl and click 'Open With'
   
    If Notepad shows up as an option, click it, otherwise click 'Choose Program' and navigate
    through the list to notepad and select it

10. Copy the connection string to your application

Commented:
How about a DSN-less connection?  http://www.accessmvp.com/djsteele/DSNLessLinks.html

Explore More ContentExplore courses, solutions, and other research materials related to this topic.