ACC2: RegisterDatabase Method Does Not Modify ODBC.INI File

• Microsoft Access version 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."

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

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:

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

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

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

1. Create a new module and type the following line in the Declarations section:

         Option Explicit
   

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

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

   ARTICLE-ID: Q90988
   TITLE     : How to Use Get, WritePrivateProfileString