BUG: RegisterDatabase Fails After ODBC Version 2.x Installed

• Microsoft Visual Basic Professional Edition 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

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




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




3. 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$

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

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



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




3. 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 query words: 3.00

Keywords          : kbcode kbDatabase kbODBC 
Version           : 3.00
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: June 17, 1999