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

MORE INFORMATION

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.