DSN is an acronym for Data Source Name. It's a simple and standard way to describe how to connect to a data source using an ODBC driver. More importantly, using a DSN means you can change the location of your data by updating the DSN, no update to your application required. Keep in mind that DSN's only describe ODBC connections, not OLEDB connections.

There are three types of DSN's; system, user, and file. System and user DSN's are registry based, while File DSN's are stored in the file system. You normally create and administer DSN's through the ODBC Data Source Administrator, found in Control Panel on NT/9x machines, or in Administrative Tools for Win2K machines. However, there is nothing magical about editing DSN's. You can use regedit to view/modify user or system DSN's, and notepad works fine for file DSN's. 

Most information about DSN's is stored in the registry. HKLM\Software\ODBC\ODBCINST.INI

contains a list of all ODBC drivers installed on your machine. HKLM\Software\ODBC\ODBC.INI contains all of your

system DSN's stored as separate subkeys. ODBC.INI has two other subkeys, ODBC Data Sources that

contains a list of all your system DSN's, and ODBC FileDSN, which contains the name of the folder where File DSN's are stored (C:\Program Files\Common Files\ODBC\Data Sources is the default). User DSN's are stored in Hkey_Current_User\Software\ODBC, but it has only an ODBC.INI key. No Odbcinst.ini key is

needed, as installed drivers are available to all users and so are stored in Hkey_LocalMachine.

Choosing which type of DSN to use depends on how your application works. In almost all cases I've 

found that a system DSN works well since any user using my app on that machine will still be connecting to the same datasource. It also makes installation a snap, since the application can be installed for 'All Users' and the DSN will be accessible to anyone logging in to that machine. If you use a user DSN, then you'll have to do some extra work to make sure the DSN gets created when a user new to that machine

logs in for the first time.

To a developer, all DSN's are not quite equal. Here is how a developer would open a connection to the Pubs database using each type of DSN:

As you can see, there is no difference in the syntax required to use either a user or a system DSN - in fact, some quick testing with ADO 2.6 reveals that if you have a system and user DSN of the same name, it will choose the user DSN. Another interesting point is that you can actually have a system, user, and file DSN

all with the same name. 

One thing you want to consider is how will you change the DSN on 500 workstations if you move your data? If you use a user/system DSN, you can create .reg file and apply it in the login script (regedit /s filename.reg) or perhaps just email the .reg file to the users with instructions to apply the change (good luck!). 

If you're using a File DSN, you can just copy the new DSN over the old one during the login script or again, email the updated file to your users. Another alternative for file DSN's is to change the default folder for file DSN's to point to a share available to all users. Then you can update the DSN at any time and the change is instantly available to your users. If you choose this method, please set the security on

the folder to read only!

Additional reading:

http://msdn.microsoft.com/library/periodic/period99/ntp9951.htm

http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/odch9hv7.htm