How to create and use temporary tables with Access 2002 client/server (287728)

SUMMARY

Using Access Client/Server, you can create temporary tables in both SQL Server and Microsoft SQL Server 2000 Desktop Engine. Even though you cannot see the tables in the Tables pane of the Database window, the temporary tables are available for use through stored procedures and other means.

NOTE: The previous version of Microsoft SQL Server 2000 Desktop Engine is named Microsoft Data Engine (MSDE).

When you create a temporary table in a database hosted by Microsoft SQL Server 2000 Desktop Engine or SQL Server, the table is stored in the tempdb database. However, an Access project will connect to only one back-end database at a time (generally, a user database). Because temporary tables and user tables are stored in different databases, Access Client/Server will not display both. However, temporary tables are accessible from a user database through stored procedures and triggers.

The following example demonstrates:

• How to create a temporary table through stored procedures.
• How to insert data into the table and display the data in the temporary table in the user interface.

back to the top

How to Create a Temporary Table

1. Open an Access project that is connected to a Microsoft SQL Server Desktop 2000 Engine or SQL Server database.
2. In the Database window, click Queries under Objects.
3. Click New. In the New Query dialog box, click Create Text Stored Procedure, and then click OK.
4. Type the following script into the new stored procedure:

   CREATE PROCEDURE CreateATable
   AS
   CREATE TABLE ##ThisIsATest
   (
       MyPK int IDENTITY (1,1),
       MyChar char(10)
   )
   					

5. Save the stored procedure with the default name of CreateATable, and then close it.
6. To execute the stored procedure, double-click on its name, and then in the Database window, click Tables.
7. Examine the table list, and note that the temporary table ##ThisIsATest is not in the list.

back to the top

How to INSERT and SELECT Records from a Temporary Table and Display the Data in the User Interface

1. After you have completed the steps in the "Create a Temporary Table" section, click Queries under Objects.
2. Click New. In the New Query dialog box, click Create Text Stored Procedure, click OK, and then type the following script:

   CREATE PROCEDURE InsertSelect
   AS
   SET NOCOUNT ON
   INSERT ##ThisIsATest Values('1stRecord')
   INSERT ##ThisIsATest Values('2ndRecord')
   SELECT * FROM ##ThisIsATest
   					

3. Save the stored procedure with the default name of InsertSelect, and then close it.
4. To execute the stored procedure, double-click on its name, and note that you are presented with a view of the temporary table ##ThisIsATest. It contains the two records inserted by the stored procedure.

back to the top