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

MORE INFORMATION

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.