Select the product you need help with

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

Article ID: 232379 - View products that this article applies to.

This article was previously published under Q232379

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

Expand all | Collapse all

SUMMARY

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.

Back to the top | Give Feedback

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

Open an Access project connected to an MSDE or SQL Server database.
In the Database window, click Stored Procedures under Objects.
Click New.

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)
)

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

To INSERT and SELECT records from a temporary table

Complete the steps in the "To create a temporary table" section, and then in the same Access project, click Stored Procedures under Objects.

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

Save the stored procedure, and then run it.

Back to the top | Give Feedback

REFERENCES

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:

http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe

(http://download.microsoft.com/download/sql70/file/2/win98/en-us/sqlbol.exe)

Back to the top | Give Feedback