BUG: RegisterDatabase Fails After ODBC Version 2.x Installed

Last reviewed: February 26, 1996
Article ID: Q126940

The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SYMPTOMS

The RegisterDatabase function fails and returns error 3146 "ODBC call failed" after ODBC version 2.x has been installed.

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.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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."

REFERENCES

"Microsoft ODBC 2.0 Programmer's Guide and SDK Guide" Chapter 24.

Additional reference words: 3.00
KBCategory: kbprg kbbuglist kbcode
KBSubcategory: APrgDataODBC

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 26, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.