Temp table - multiple users

[RichM1]

I am working in Access 2003 (ADP) with SQL Server 2000. Before upsizing to an ADP I used a local table on each user's front end mdb to perform some minor record changes. Question: In an ADP how do I create a temporary table and what effects would a temp table have regarding size. Do I create a temp table like this - dim rs as new adodb.recordset? How do I dump the recordset?

thanks

 

[sfreeman]

no temp tables in Access. Try a query.

Sam_F
"90% of the problem is asking the right question.

 

[PHV]

no temp tables in Access
Really ?
What about something like this ?
DoCmd.RunSQL "CREATE TEMPORARY TABLE myTempTable (....


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ChuckWhite]

I would use T-SQL on the server, either through a SQL stored procedure or an Access Pass-Through query in T-SQL syntax.

In my experience this is fast, easy and SQL Server manages the temp tables on a per user basis.

SQL Server help has this to say:

"SQLOLEDB exposes the ITableDefinition::CreateTable function, allowing consumers to create Microsoft® SQL Server™ 2000 tables. Consumers use CreateTable to create consumer-named permanent tables, and permanent or temporary tables with unique names generated by SQLOLEDB.

When the consumer calls ITableDefinition::CreateTable, if the value of the DBPROP_TBL_TEMPTABLE property is VARIANT_TRUE, SQLOLEDB generates a temporary table name for the consumer. The consumer sets the pTableID parameter of the CreateTable method to NULL. The temporary tables with names generated by SQLOLEDB do not appear in the TABLES rowset, but are accessible through the IOpenRowset interface."

 

[cmmrfrds]

FYI, this is a duplicate thread.
Thread958-1020053