ODBC DSN-Less Connection Tutorial

What is DSN-Less ODBC?

In a previous article, Problematic or Programmatic ODBC, I described the various ways I have used to distribute a DSN (ODBC Data Source Name) to users of my client/server applications. Towards the end of that article, I mentioned an Access Database Application I created to serve as a tutorial for people with questions about reconnecting linked MS Access tables to SQL Server using DSN-Less ODBC. This article will focus on the details of that tutorial, which is available for download.

DSN-Less ODBC simply means that instead of storing connection information (like server, database, etc.) in a file or the Windows Registry, you have plunked them into a connect string, which is then associated with some data object, like an Access linked table. Here is an example of the connect string generated by my tutorial:

    ODBC;DRIVER={SQL Server};SERVER=cliff;DATABASE=pubs;UID=sa;PWD=;

You will notice that there are five parameters that need to be populated:

    DRIVER={SQL Server}
    SERVER=cliff
    DATABASE=pubs
    UID=sa
    PWD=

In our example, the DRIVER parameter will always be SQL Server, so the Login form doesn't need to bother the user for that information. On the other hand, the other arguments are subject to change, so the user needs to be queried to supply the SERVER, DATABASE, USER and PASSWORD. In most cases, the user is required to supply some or all of these values. The Access form which I use to collect this information looks like this:

Making it Easier for the User

Since login screens can be intimidating for some users, I decided to add an optional "auto-populate" feature to the login form. Instead of supplying the user with values which need to be typed into the login form, the user simply supplies the path to a file (an old-fashioned ini file) containing that information. In our application, that path was hidden from the user and login was truly automatic. This idea worked well for us, since it was customary to create a common network folder where application setup and shared files could be accessable to all users. In order to automate the login process, the application adminstrator needs simply to edit the ini file with current data source information.



Next: Page 2