BUG: RegisterDatabase Fails After ODBC Version 2.x Installed (126940)

CAUSE

The ODBC API function SQLConfigDataSource() was not correctly implemented in ODBC version 1.0. The RegisterDatabase function in Visual Basic version 3.0 was designed to use the ODBC version 1.0 implementation of SQLConfigDataSource().

SQLConfigDataSource() is now implemented correctly in ODBC version 2.x. If ODBC version 2.x has been installed you will find that an application that uses RegisterDatabase will fail with the error 3146 "ODBC call failed."

Visual Basic exhibits this problem when using RegisterDatabase because it was based on the original implementation of SQLConfigDataSource(). The underlying function that RegisterDatabase ultimately calls has been changed and Visual Basic's RegisterDatabase function will now generate the error if the ODBC version 2.x DLLs are installed.

WORKAROUND

You can code directly to the ODBC API and register your DSN with SQLConfigDataSource(). Below is a code sample that will replace the functionality of Visual Basic's RegisterDatabase function. It first tries to use RegisterDatabase, if that fails, it uses the ODBC API function SQLConfigDataSource. You can call this function instead of calling RegisterDatabase.

Step-by-Step Example

In Visual Basic, create a new module with the following declarations:

   Option Explicit
   Const ODBC_ADD_DSN = 1        ' Add a new data source.
   Const ODBC_CONFIG_DSN = 2     ' Configure (edit) existing data source.
   Const ODBC_REMOVE_DSN = 3     ' Remove existing data source.

   ' Enter the following three lines as one, single line:

   Declare Function SQLConfigDataSource Lib "odbcinst.dll"
      (ByVal hwnd as Integer, ByVal fRefresh as Integer,
      ByVal szDriver as String, ByVal szAttributes as String) As Integer

Create the following procedure.

   ' Enter the following two lines as one, single line of code:
   Sub RegisterODBCDatabase (dsn As String, driver As String,
      silent As Integer, attributes As String)

      Dim ret As Integer
      On Error GoTo errorhandler

      RegisterDatabase dsn, driver, silent, attributes
   Exit Sub

errorhandler:

   If Err = 3146 Then    ' ODBC Call Failed.
      Dim temp As String
      Dim spot As Integer

      While InStr(attributes, Chr(13))     ' Replace Carriage returns
         spot = InStr(attributes, Chr(13)) ' with nulls.
         Mid(attributes, spot, 1) = Chr(0)
      Wend
      attributes = attributes & Chr(0) & Chr(0) ' End of attribute section.
      temp = "DSN=" & dsn & Chr(0) & attributes

      ret = SQLConfigDataSource(0, ODBC_ADD_DSN, driver, temp)

      ' ret is equal to 1 on success and 0 if there is an error.
      If ret <> 1 Then
         MsgBox "SQLConfigDataSource call failed"
      Else
         MsgBox " SQLConfigDataSource call succeeded!"
      End If
   End If
   Exit Sub
   End Sub

To test this procedure, press the F8 key to single step. Then activate the Debug window from the Window menu. Type the following, and press the ENTER key:

RegisterODBCDatabase "Pubs", "SqlServer", "True, Att$

As a result, you will receive a message stating the new datasource was added successfully.

MORE INFORMATION

Steps to Reproduce Problem

Start a new project in Visual Basic. Form1 is created by defalt.

Add a Command button (Command1) to Form1 and add the following code to the Command1 button's Click event:

   Sub Command1_Click ()
      Dim Att As String, MyDb As Database
      Att = "Description = SQL Server on server Clinton" & Chr$(13)
      Att = Att & "OemToAnsi=No" & Chr$(13)   ' Build keywords string.
      Att = Att & "Network=DBNMP3" & Chr$(13)
      Att = Att & "Address=\\CLINTON\PIPE\SQL\QUERY" & Chr$(13)
      Att = Att & "Database=Pubs"
      ' Update ODBC.INI.
      RegisterDatabase "Clinton", "SQL Server", True, Att
   End Sub

Press the F5 key to run the program. If ODBC version 2.x has been installed, when you click the command button, you will receive this error: "ODBC Call Failed."