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

• 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 AttachTable_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 AttachTable 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.

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