Microsoft KB Archive/132329

= ACC2: RegisterDatabase Method Does Not Modify ODBC.INI File =

Article ID: 132329

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132329





SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use the RegisterDatabase method in a custom function to supply connection information for an ODBC data source, Microsoft Access does not modify the ODBC.INI file and may display the following error message:

" is not an existing data source name."

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Building Applications" manual.



CAUSE
The RegisterDatabase method in Microsoft Access version 2.0 is designed for ODBC version 1.0. If you installed the ODBC Fulfillment Pack version 2.0, you have a newer, incompatible version of the ODBC.DLL file, ODBC.DLL 2.00.1912.



RESOLUTION
To work around this problem, use one of the following methods to register an ODBC data source if you installed the ODBC Fulfillment Pack version 2.0.

Method 1
In Access Basic, you can use the WritePrivateProfileString API function to add connection information to the ODBC.INI file for an ODBC data source. The custom function adds the data source to the ODBC Data Sources section of the ODBC.INI file, and then creates a new section for the data source as follows:

  [ODBC Data Sources] MyServer=SQL SERVER

[MyServer] Driver=C:\WINDOWS\SYSTEM\sqlsrvr.dll Description=MyServer Data Source Server=MyServer Network=DBNMP3 Database=pubs Language=us_english OemToAnsi=No

For details on using the WritePrivateProfileString API function, please refer to the Microsoft Access Developer's Toolkit "Advanced Topics," version 2.0, Chapter 2, "Creating a Custom Setup Program," pages 32-34.

Method 2
In Access Basic, you can use the SQLConfigDatasource API function to write connection information for a data source to the ODBC.INI file. To create and test this function, follow these steps:

  Create a new module and type the following lines in the Declarations section.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

     Option Explicit Declare Function SQLConfigDatasource Lib "odbcinst.dll" (ByVal _                      Hwnd%, ByVal fRequest%, ByVal lpszDriver$, _                       ByVal lpszAttributes$) As Integer

Global Const SQL_Add_DataSource = 1 Global Const SQL_Edit_DataSource = 2 Global Const SQL_Delete_DataSource = 3   Type the following procedure.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

Function New_DataSource (DataSourceType$, DataSourceName$, _              ServerName$, DatabaseName$) As Variant

Const MB_ICON_INFORMATION = 64

Dim DName As String Dim DSName As String Dim Server As String Dim DDatabase As String

Dim RetVar As Variant

DName = DataSourceType & Chr$(0) DSName = "DSN=" & DataSourceName & Chr$(0) Server = "Server=" & ServerName & Chr$(0) DDatabase = "Database=" & DatabaseName & Chr$(0)

RetVar = SQLConfigDatasource(0&, SQL_Add_DataSource, DName, _            DSName & Server & DDatabase)

If RetVar = 1 Then MsgBox "Added Successfully.", MB_ICON_INFORMATION, "" Else MsgBox "There is a problem trying to register new data source." _            & Chr$(13) & Chr$(10) & "Please make sure everything is set _             correctly.", MB_ICON_INFORMATION, "" End If

New_DataSource = RetVar End Function  To test this procedure, type the following line in the Immediate window, and then press ENTER:

Print New_DataSource("SQL Server","TestSQL","MyServer","Pubs")

Note that you receive a message stating that the new data source was added successfully.

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

<div class="moreinformation_section">

Steps to Reproduce Problem
  Create a new module and type the following line in the Declarations section:

<pre class="fixed_text">     Option Explicit </li>  Type the following procedure:

Sub Try_New_DataSource

Dim Attribs As String Attribs = "Description=SQL Server on server MyServer" & Chr$(13) Attribs = Attribs & "OemToAnsi=No" & Chr$(13) Attribs = Attribs & "Network=DBNMP3" & Chr$(13) Attribs = Attribs & "Address=\\MYSERVER\PIPE\SQL\QUERY" & Chr$(13) Attribs = Attribs & "Database=NWIND.MDB" DBEngine.RegisterDatabase "MyServer", "SQL Server", 0, Attribs

End Sub </li> To test this procedure, type the following line in the Immediate window, and then press ENTER:

Try_New_DataSource

Note that you receive the following error message:

<MyServer> is not a valid data source name.

</li></ol>

<div class="references_section">