Microsoft KB Archive/128878

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

Article ID: 128878

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q128878





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:

 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'

  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",  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.  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, "", _ "ODBC;DSN=MyServer;UID=MyUser;PWD=MyPWD;LANGUAGE=us_english; _         DATABASE=pubs",_ acTable, Forms!frmAttachTable!Attach, _ "Local" & _ Forms!frmAttachTable!Attach

End If

End Sub  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.</ol>

<div class="references_section">