In-Depth

Create a DSN-Less Database Connection

Increase your application's flexibility and make the installation simpler by connecting to the database without generating a DSN in the ODBC Data Source Administrator. Instead, use VB code to configure the connection.

• By Irina Segalevich
• 07/01/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).

In the second example, you create and delete the DSN connection using the SQLConfigDataSource API function. Open a default form with two command buttons—cmdCreate and cmdDelete. Declare the constants and function in the Declaration section of the form (see Listing 2).

In this third example, you use a DSN-less connection to connect to the data source. In this type of connection, you specify all necessary information (the driver, the server, the database name, and the user ID) in the connection string. Use the native OLE DB provider for SQL Server instead of ODBC because of its performance and reliability.

Make sure you set up a reference to the Microsoft ActiveX Data object in your project. Use this code to open a Connection object:

Dim cnMyConnection As ADODB.Connection
Dim sConnString As String 
   Set cnMyConnection=New ADODB.Connection

      sConnString = "Provider=SQLOLEDB.1;" & _
         "Persist Security Info=False;UserId=sa;" & _
         "Initial Catalog=MyDatabase;" & _
         "Data Source=MyServerName;Password=MyPassword;"   
   With cnMyConnection
         .ConnectionString = sConnString
         .ConnectionTimeout = 0
         . Open
   End With  

   ???

If your application accesses multiple databases with the different settings, you might find it helpful to use a universal data link (UDL) file with the saved connection information. In this case, your connection string looks like this:

sConnString="File Name=" & <Path to UDL file> & _
   "\" & UDLFileName & ".udl;"

It's always good practice to close the object variables explicitly after you're done with them:

If not cnMyConnection is Nothing then
   cnMyConnection.Close
   Set cnMyConnection=Nothing
End if

Check out the MSDN article, "Creating and Configuring Universal Data Link (.udl) Files," to learn how to create the UDL files. You can find a list of connection strings for the different drivers at www.connectionstrings.com.

The code in these examples lets you connect to a database without preliminary generation of a DSN in the ODBC Data Source Administrator. You configure this connection instead through Visual Basic 6 code.