ACC2: RegisterDatabase Method Does Not Modify ODBC.INI File

ID: Q132329

The information in this article applies to:

Microsoft Access 2.0

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:

"<Data Source> 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 OBDC.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.

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.

MORE INFORMATION

Steps to Reproduce Problem

Create a new module and type the following line in the Declarations section:
```
      Option Explicit 
```

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

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.

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 15, "Using Library Databases and Dynamic-Link Libraries," pages 367-371

"Microsoft ODBC Programmer's Reference and SDK Guide", version 2.0, Chapter 24, "Installer DLL Function Reference," pages 528-529

For more information about using the WritePrivateProfileString() API Function, please see the following article in the Microsoft Knowledge Base:

Q90988 How to Use Get, WritePrivateProfileString

For more information about registering a data source from Microsoft Visual Basic, please see the following article in the Microsoft Knowledge Base:

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


Keywords          : kberrmsg kbusage OdbcProb 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 22, 1999