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.
Back to the top
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.
|
Back to the top
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.
Back to the top
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Help and Support
