ACC: How to Create a Customized "Attach ODBC Table" Form

ID: Q128878

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article describes how to create a form that gives you more control over attaching (linking) ODBC tables than the Attach Table dialog box gives you. One method you can use to gain more control over these tables in Microsoft Access 7.0 and 97 is to modify the AttachableObjects setting in the Registry file. In Microsoft Access 2.0, you can modify the MSACC20.INI file. A faster method, however, is to create a form that uses the TransferDatabase command to attach tables from an ODBC data source.

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

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following steps show you how to create the sample form:

1. Create a table or query containing table names on the server you want to

   attach to. On Microsoft SQL Server you can use the following syntax in
   your SQL pass-through query to return a list of all user tables. Run
   the query to make sure it returns the expected table names. Save this
   SQL pass-through query as Query1.

     SELECT name FROM sysobjects WHERE type='U'

2. Specify the ODBC connect string for the SQL pass-through query by

   clicking Properties on the View menu and setting the ODBCConnectStr
   property. The string should like similar to the one below:

     "ODBC;DSN=myserver;UID=myuser;PWD=;LANGUAGE=us_english;DATABASE=pubs",

3. Create a new unbound form and add an unbound list box to the form. Set

   the Name property to Attach. Set the RowSource property of the list box
   to Query1. Save the form as frmAttachTable.

4. Add a command button to the frmAttachTable form. Change the Name

   property of the command button to cmdAttachTable and the Caption
   property to Attach Table. Set the OnClick property of the command
   button to the following event 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 recreating this code in Access Basic.

      Sub cmdAttachTable_Click ()

      If MsgBox("Attach table " & Forms! frmAttachTable! Attach & "?") Then

          DoCmd.TransferDatabase acLink, "<SQL Database>", _
          "ODBC;DSN=MyServer;UID=MyUser;PWD=MyPWD;LANGUAGE=us_english; _
          DATABASE=pubs",_ acTable, Forms!frmAttachTable!Attach, _
          "Local" & _
          Forms!frmAttachTable!Attach

      End If

      End Sub

5. View the form in Form view. Note that the list box lists the table

   names. Selecting a table name in the list box and then clicking the
   cmdAttachTable button attaches the table.

REFERENCES

For more information about the Registry, search the Help Index for "Registry Editor," or ask the Microsoft Access 97 Office Assistant.

For more information about the MSACC20.INI file, search for "MSACC20.INI," and then "Customizing MSACC20.INI Settings" using the Microsoft Access, version 2.0 Help menu.

Additional query words:

Keywords          : kbusage FmsHowto 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998