email article printer friendly

Create a DSN-Less Database Connection
by Irina Segalevich

Posted May 21, 2004

Technology Toolbox: VB6

You must have key connection information to connect any application with a data source (database), including Microsoft Access, SQL Server, Oracle RDBMS, and others. The connection information includes a data source name (DSN), the location of the server, the database name, the logon information, a password, and driver options. You can store this information in the Windows Registry where the DSN was created, or in the file DSN. You could also define this information in the Visual Basic code that specifies the connection string. Commonly, you can create the DSN with the ODBC Data Source Administrator, which you can access from the Windows Control Panel or Administrator Tools (in Windows 2000).

Specifying the DSN and connection to the database in VB increases the flexibility of your application and makes your installation simpler. This approach eliminates a few steps that require the user or system administrator to create a DSN on each computer. A DSN-less connection can be especially useful for Web applications. In this case, you don't need to re-create a DSN every time you change the location of your application from one server to another. The DSN-less connections are appreciably faster than the system DSN connections, which, in turn, are faster than the file DSN. The DSN-less connections pass up the calls to the Windows Registry. I'll show you different ways to create DSN and DSN-less connections in VB6.

Here you create the Windows Registry key for the DSN connection using the RegCreateKey, RegSetValueEx, and RegCloseKey API functions. Open a new Visual Basic project, and put a command button on the default form. This code goes to the General Declaration section of the form:

Option Explicit

   Private Const HKEY_LOCAL_MACHINE = &H80000002
   Private Const HKEY_CURRENT_USER = &H80000001

   Private Declare Function RegCreateKey Lib "advapi32.dll" _
      Alias "RegCreateKeyA" (ByVal hKey As Long, _
      ByVal lpSubKey As String, phkResult As Long) As Long

   Private Declare Function RegSetValueEx Lib "advapi32.dll" _
      Alias "RegSetValueExA" (ByVal hKey As Long, _
      ByVal lpValueName As String, ByVal Reserved As Long, _
      ByVal dwType As Long, lpData As Any, _
      ByVal cbData As Long) As Long

   Private Declare Function RegCloseKey Lib "advapi32.dll" _
      (ByVal hKey As Long) As Long

Now place code in the click event for the command button cmdCreateRegKey (see Listing 1).

This code creates the new DSN key in the Windows Registry. The result is shown on the list of DSNs in the ODBC Data Source Administrator, which is located in the Control Panel (Administrator Tools in Windows 2000). Use this function to delete the new DSN key: RegDeleteKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & sDSNName).