Hi Guys,

How do I create a temporary table and destroy it afterwards. This temporary table will have a recordset coming from another table and will also have other information coming from a user input. I am using ADO Connection. If someone can please me a sample code or give me a book or site I can look at so I can create this temp table. I appreciate all the help you can extend me.

Cheers,
Judy

what DBMS are you going to use?


... there is always more to learn...

I will be using ACCESS 2000.

I was able to look at some sample Create table codes. After adding this create table code I am getting this error code:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in CREATE TABLE statement.
/MainServer/Main.asp, line 371


below is my create table code. I am using Microsoft.Jet.OLEDB.4.0 ACCESS 2000. Please someone, I need help on this. Is it because Jet OLEDB 4.0 does not support this create table? If not then what should I do? Any suggestions? Thanks.

set rsTempTb = Server.CreateObject("ADODB.Recordset")
sqlString = "CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20), DateOfBirth DATETIME, CONSTRAINT MultiConstraint UNIQUE(FirstName, LastName, DateOfBirth) )"
rsCART.Open sqlString, objconn, adOpenKeyset,adLockOptimistic

I am sorry I did a Typing error when I posted the create table code.

The .open statement was suppose to be :

rsTempTb.Open sqlString, objconn, adOpenKeyset,adLockOptimistic

Thanks,
Judy

Okay. After playing with it some more I was finally able to create it. My mistake was I was creating a table and a recordset at the same time. What I did was :

sqlString = "CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20), DateOfBirth DATETIME, CONSTRAINT MultiConstraint UNIQUE(FirstName, LastName, DateOfBirth) )"
objconn.Execute (strSQL)

This created a table. Now I have to figure out how to delete or destroy this table after session is over.

Thanks,
Judy