ACC2000: How to Create and Use Temporary Tables with Access Client Server

You can create temporary tables in both SQL Server and the Microsoft Data Engine (MSDE) using Access Client/Server. Even though you cannot see the tables in the Table pane of the Database window, temporary tables are available for use through stored procedures and other means.

When you create a temporary table in a database hosted by the MSDE or SQL Server, it is stored in the tempdb database. 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. Temporary tables are accessible from a user database through stored procedures and triggers, however.

The example below demonstrates the following:

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

To create a temporary table

1. Open an Access project connected to an MSDE or SQL Server database.
2. In the Database window, click Stored Procedures under Objects.
3. Click New.
4. Type the following script into the Stored Procedure Designer, and then save it:

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

5. Run the stored procedure, and then in the Database window, click Tables.
6. Examine the table list and note that the temporary table ##ThisIsATest is not displayed.

To INSERT and SELECT records from a temporary table

1. Complete the steps in the "To create a temporary table" section, and then in the same Access project, click Stored Procedures under Objects.
2. Click New and type the following script into the Stored Procedure Designer:

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

3. Save the stored procedure, and then run it.

For more information about temporary objects, refer to SQL Server 7.0 Books Online, which is available for download from the following Microsoft Web site: