Microsoft KB Archive/123714

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

Article ID: 123714

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q123714



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

</li>  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 </li> 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.</li></ol>

<div class="references_section">