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

ID: Q232379

The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.

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


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.


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:

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: 


    5. 
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:


    3. 
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.



REFERENCES

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

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe

Additional query words: 


Keywords          : kbdta AccessCS 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        :

Last Reviewed: June 10, 1999