Microsoft KB Archive/126940

= BUG: RegisterDatabase Fails After ODBC Version 2.x Installed =

Article ID: 126940

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q126940



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.



Steps to Reproduce Problem
 Start a new project in Visual Basic. Form1 is created by defalt.</li>  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

</li> 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."</li></ol>

<div class="references_section">