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
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
(Yes, you can call
, 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