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 ??

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MangusDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin SparksCommented:
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

Chris MangusDatabase AdministratorCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kelvin SparksCommented:
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 SparksCommented:
By the way, a file dsn does not use/need registry settings. It is nothing more than a file.
Chris MangusDatabase AdministratorCommented:
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 AdministratorCommented:
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...
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
How about a DSN-less connection?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.