Microsoft KB Archive/232379

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

Article ID: 232379

Article Last Modified on 8/9/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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:


 * 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.</li></ol>

To INSERT and SELECT records from a temporary table
<ol> Complete the steps in the "To create a temporary table" section, and then in the same Access project, click Stored Procedures under Objects.</li>  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 </li> Save the stored procedure, and then run it.</li></ol>

<div class="references_section">