VBA ODBC DSN Installer

One of the most common questions we come across as MS Access developers is “How do I make sure every user can access the database?”. Often, and with good reason, a DSN-less connection is the way to go. However, DSN-less connections aren’t for every situation in Access. Sometimes they’re clunky, or some functionality of Access just doesn’t work with a DSN-less connection. I suspect this is because Access was designed specifically to use linked tables, which use a DSN by nature.

I ran across this problem a little while back. While I can’t remember exactly what it was that just wouldn’t work no matter what I tried, I did need a fairly robust solution for manipulating the Domain System Names via VBA. Not only did I need to install them, I needed to fix them when they weren’t set up correctly. So, for my case, a *.reg file just wouldn’t do. I had to code this.

The ODBC DSN’s are stored in the several different locations of the Windows registry. This is because DSNs can be 32 or 64 bit and either a User or System DSN. User DSNs are stored under the “HKEY_CURRENT_USER\” section of the registry, while System DSNs are stored under “HKEY_LOCAL_MACHINE\”. If you’re running a 64 bit system, you will find 64 bit connections under the “software\ODBC\odbc.ini” key, while 32 bit connections will be found under the “software\wow6432Node\ODBC\odbc.ini” key. Importantly, in order to actually make these entries visible in the ODBC Manager GUI, you have to add another key to the “\odbc\odbc.ini\odbc data sources” key.

Considering that I needed to write registry keys and values, I first needed a way to do that. I came across Steve McMahon’s cRegistry class. Mr. McMahon’s version was written for vb6, so I did have to make some modifications just to get it to work in vba, but nothing drastic. I later added some search functionality to it as well.  You can download my version of the Registry class from Google Drive.

From here I set about writing a DSN class that utilizes the registry class. There’s a lot of code in there that I am not going to get into here, but I will show you how to utilize it. This class gives us virtually full control of the DSNs on the machines of our users. Once you’ve downloaded both my version of the Registry class and my DSN class, import them into your vba project and same them as Registry and DSN, respectively. We can now add, edit, or delete SQL Server, SQL Server Native Client (10 & 11), and Oracle 11g DSNs.

Let’s begin with creating a new SQL Server NCL 11 DSN.

Private Sub createSQLNCL11()
' Creates a new ODBC Admin entry using SQL Server Native Client 11
    Dim d As New DSN
    With d
      .BitMode = DSN_64BIT
      .Driver = DSN_DRIVER_SQLSERVER11 'note that a lot of properties are unavailable until after you've set the driver 
      .DSNType = DSN_SYSTEM
      .NAME = "Data Source Name"
      .Server = "Server Name"
      .Database = "Database Name"
       Debug.Print .DriverFile
      .Create ' no registry keys are created until this is called
    End With
End Sub
.Create

will over write an entry unless you tell it not to. It accepts an optional parameter “overwrite”. If you wish to leave existing entries as they are, you need to call it as

.Create overwrite:=false.

(Yes, you can call

.Create false

, but it doesn’t read very well.)

We need significantly less information to delete an ODBC entry. We just need to know it’s name, whether it’s system or user, and it’s bit mode. The delete method already checks to see if the registry key exists, so it “fails” silently if it’s not there to begin with.

Private Sub deleteDSN()
    Dim d as New DSN
    With d
         .Name = "DSN Name"
         .DSNType = DSN_USER ' or DSN_SYSTEM
         .BitMode = DSN_32BIT ' or DSN_64BIT
         .Delete
    End With

Finally, the coup d’etat. We’re going to remove all instances of a DSN Name and leave only our correct version behind.

Private Sub correctDSN()
    Dim d as New DSN
    With d
         .Name = "DSN Name"
         .DSNType = DSN_USER  
         .BitMode = DSN_32BIT
         .Delete
         .DSNType = DSN_SYSTEM
         .Delete
         .BitMode = DSN_64BIT
         .DSNType = DSN_USER
         .Delete
         .DSNType = DSN_SYSTEM
         .Driver = DSN_DRIVER_SQLSERVER11
         .Server = "Server Name"
         .Database = "Database Name"
         .Create
    End With
End Sub
Advertisements

, , , ,

  1. #1 by The Special on August 11, 2016 - 6:28 pm

    Very nice…. any chance you’ll add SQL Server Login support? I was very excited to get this working…right up until I realized its for Windows Auth only. :*(

    Like

  2. #2 by Christopher J. McClellan on August 11, 2016 - 10:36 pm

    Thank you! Unfortunately, no, I won’t be modifying this class. I no longer have a personal need for the class. On the bright side, I hereby release this version of the code under the MIT license. Please feel free to modify and distribute the class. https://opensource.org/licenses/MIT

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: