Problematic or Programmatic ODBC


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 FilesCommon FilesODBCData 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:DesktopPubs.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.

  1. Knowledge of which Registry Keys are required

  2. API calls to read and write to the Windows Registry

  3. 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_USERSoftwareODBCODBC.INIPubs]
“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_USERSoftwareODBCODBC.INIODBC 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 = “HKCUSoftwareODBCODBC.INIPubs”
Const cRegKey2 = “HKCUSoftwareODBCODBC.INIODBC 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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles