Problematic or Programmatic ODBC

September 14, 2000

The Problem with ODBC

One of my biggest frustrations in programming is when a critical assumption I've made fails to be true. This can happen, for example, when code written for your SQL Server database application depend on the user having set up an ODBC DSN (Data Source Name). For example, if you have a SQL Server with a Pubs database, I could send you a Pubs sample application that requires a DSN named Pubs. Now, maybe you have already created a DSN named Pubs that points to the Pubs database, and maybe not. If not, my program will fail.

Now, suppose you are responsible for distributing an application to your enterprise of 1000 users. Imagine the work involved with configuring those 1000 workstations with a custom DSN for your application. This task can be staggering and can become a maintenance nightmare. How can it be accomplished? Here are a few of the methods I have used:

Distribute a File DSN with your application.
Code your application to write the necessary Windows Registry Keys
Distribute a Registry file (.reg) that will make the necessary Registry entries.
Create and distribute a Windows Script File (.vbs) to insert the Registry entries.
Use a DSN-Less connection.

Distribute a File DSN

Distributing a file DSN with your application is easy and relatively painless. You first create the file DSN from the ODBC Data Source Administrator utility, which may be launched from the Control Panel. Select the File DSN tab and click the Add button. Follow the prompts to create the new File DSN and take note of where you decide to save this .dsn file. (The default is "C:\Program Files\Common Files\ODBC\Data Sources\", but you may save the file to any folder you wish.)

Next, you need to assign the File DSN to the Connect property of some database object, such as a linked table object is MS Access.

Function LinkToPubsAuthors()
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Authors")
tdf.SourceTableName = "Authors"

tdf.Connect = "ODBC;FILEDSN=C:\Desktop\Pubs.dsn"

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing
End Function

Finally, you are ready to distribute your File DSN along with your application. With Visual Basic, it is often easiest copy this File DSN to the application folder when the program is installed. From within code, you can use the VB App.Path method to determine the address of the file.

Write Code to Insert Registry Entries

Although I have used this method, it is somewhat kludgy, in my opinion, so I won't provide any code. The idea is to use API calls to write Windows Registry Entries that will create the User or System DSN entry. This method requires three things.

Knowledge of which Registry Keys are required
API calls to read and write to the Windows Registry
Code logic to know when it's necessary to write these keys.

The API calls and the Code Logic are beyond the scope of this article. The Registry Keys which define a User DSN are described under the next two methods, so I'll leave it to the reader to complete this solution, if programmatically writing to the Registry is the route you wish to go. (If you want to see this solution, write me and I'll either send you the code, or write it up if there's enough interest.)

Distribute a Registry File (.reg) to Create the Entries

Another solution I found to be a "quick fix" was to distribute a .reg file with all the Registry Key information. Reg files, when executed, write Registry Keys. You can create a .reg file by opening the Windows Registry Editor program (type regedit.exe in the Run dialog box), navigating to the Key in question and selecting Export Registry File from the File menu. If you right click the newly created .reg file, you'll see the Registry branch and keys that are required for an ODBC DSN.

(Copy this text to Notepad and save it as pubs.reg)
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Pubs]
"Driver"="C:\\WINDOWS\\system32\\sqlsrv32.dll"
"Description"="Pubs"
"Server"="c1082797-a"
"Database"="Pubs"
"LastUser"="sa"

The above information will create a new DSN to the SQL Server Pubs database. However, an additional Registry entry is necessary in order for this DSN to be exposed to users via the ODBC Data Source Administrator. (Add this text to your pubs.reg file.)

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"pubs"="SQL Server"

Combine these two scripts into one "pubs.reg" file and distribute it to your users with instructions to execute it and select "Yes" when prompted as to whether they wish to add this information to the registry. Presto chango, the user now has the correct DSN installed on their machine.

Distribute a WSH File (.vbs) to Create the Entries

Closely related to the reg file solution is the WSH file method.

Windows Scripting Host files use VBS code to access the exposed methods and properties of COM objects, such as the Windows Scripting Shell object. This object model includes methods to read from and write to the Windows Registry. Using the same Registry information provided above, a VBS file can be created to add the desired Registry Keys.

The advantage of using VBS is that, in addition to being able to distribute the pubs.vbs file to users, the code itself will run in any VB or VBA application such as Microsoft Word, Excel or Access thereby moving the responsibility for executing the code from the user to the application itself. Additionally, since this script is written in VBS, it will execute equally well from an ASP page.

(Copy this text to Notepad and save it as pubs.vbs)
Dim oWshShell
Const cRegKey1 = "HKCU\Software\ODBC\ODBC.INI\Pubs\"
Const cRegKey2 = "HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources\"

Set oWshShell = CreateObject ("WScript.Shell")

oWshShell.RegWrite cRegKey1 & "Driver","C:\\WINNT\\System32\\sqlsrv32.dll"
oWshShell.RegWrite cRegKey1 & "Server","GothemCity"
oWshShell.RegWrite cRegKey1 & "Database","pubs"
oWshShell.RegWrite cRegKey1 & "LastUser","Alfred"

oWshShell.RegWrite cRegKey2 & "Pubs","SQL Server"

set oWshShell = Nothing

Use DSN-Less Connection

Finally, you can open a connection to a database without creating or specifying a named data source. Connections made in this way are called "DSN-less", because they don't require the system administrator to create an ODBC DSN. Rather than relying on information stored in a file or in the system registry, DSN-less connections specify the driver name, and all driver-specific information in the connection string. Consider this example of a DSN-Less connection to a SQL Server database:

Function LinkToPubsAuthorsDSNLess()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Authors")
tdf.SourceTableName = "Authors"

tdf.Connect = strConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing
End Function

Conclusion

Connecting to a database can be problematic, but as you have seen, it doesn't need to be. There are numerous ways to ensure that when your application and its DSN are distributed to users, it will function properly, just as it does for you in your development environment!

I often see requests on the comp.databases.ms-access newsgroup for information about connecting to ODBC datasources such as SQL Server. Accordingly, I have created a Microsoft Access database application that demonstrates the process of collecting the necessary ODBC DSN arguments and using them to refresh table links. This tutorial utility was the inspiration for this article, but while preparing this article I decided that it wouldn't hurt to lay a foundation before moving on to the inner workings of that utility.

That tutorial will be the subject of my next article.

See All Articles by Columnist Danny Lesandrini

Problematic or Programmatic ODBC

The Problem with ODBC

Distribute a File DSN

Write Code to Insert Registry Entries

Distribute a Registry File (.reg) to Create the Entries

Distribute a WSH File (.vbs) to Create the Entries

Use DSN-Less Connection

Conclusion

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos