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

Last reviewed: May 20, 1997
Article ID: Q123714
The information in this article applies to:
  • Microsoft Access version 2.0

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:

  1. Create a new query that is not based on any table.

  2. From the Query menu, choose SQL Specific and then choose Pass-Through.

  3. 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.

  4. 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
    

  5. Run the query to make sure that it returns one column called Name that lists the tables on the SQL Server.

  6. Save the query as SQL_Tables and then close it.

  7. Create a new module and then enter the following line in the Declarations section:

          Option Explicit
    

  8. 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
    
    

  9. 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.

REFERENCES

For more information about SQL pass-through queries, search for "pass- through query," and then "Creating a SQL Pass-Through Query" using the Microsoft Access Help menu.


Keywords : kbusage OdbcSqlms
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.