ACC2: How to Attach All the User Tables on a SQL Server (123714)

SUMMARY

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

This article describes how to use a SQL pass-through query to obtain a list of all the user tables located on a SQL Server, and then attach those tables.

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

MORE INFORMATION

To create a SQL pass-through query to list and attach all the tables on a SQL Server, follow these steps:

Create a new query that is not based on any table.
From the Query menu, choose SQL Specific and then choose Pass-Through.
In the SQL Pass-Through Query window, type the following line:

SELECT name FROM sysobjects Where type = 'U';

NOTE: If the SQL Server you will be using was installed with case sensitivity, you may have to type "Name" and "u" in uppercase letters.
From the View menu, choose Properties. Enter a complete ODBC connect string in the ODBCConnectStr property, or choose the Build button to select an ODBC data source. The following is an example of a valid ODBC connect string:

ODBCConnectStr: ODBC;DSN=opus;UID=john;PWD=steelers;DATABASE=pubs
Run the query to make sure that it returns one column called Name that lists the tables on the SQL Server.
Save the query as SQL_Tables and then close it.
Create a new module and then enter the following line in the Declarations section:
Option Explicit

Enter the following code in the module.

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 in Access Basic.

      Function attach_all ()
          Dim DB As Database
          Dim RS As Recordset
          Dim QD As QueryDef

          Set DB = DBEngine.Workspaces(0).Databases(0)
          ' Set the QueryDef variable to the pass-through query.
          Set QD = DB.OpenQueryDef("sql_tables")
          Set RS = DB.OpenRecordset(QD.name, DB_OPEN_SNAPSHOT)

          Do Until RS.EOF
              DoCmd TransferDatabase A_ATTACH, "<Sql database>", _
                   QD.connect, A_TABLE, RS!Name, RS!Name
              RS.MoveNext
          Loop

          RS.Close
          QD.Close

      End Function

To test the function, choose View from the Immediate window, type the following line in the Immediate window, and then press ENTER:

? attach_all()

All the tables on the SQL Server will be attached to the current database. The amount of time this process requires depends on the number of tables on the SQL Server.

ACC2: How to Attach All the User Tables on a SQL Server (123714)

SUMMARY

MORE INFORMATION

REFERENCES