How does one test if a table exists on web?

[chdavisjr]

I have a web app that will allow the user to "archive" older records in a table (TBL1) into a new table "TBL2".

I need a test first to see if the table exists.
If not, then I create it using the records selected from TBL1. If it exists, then I want to append to TBL2.

I have no problem running a "maketable" type sql when the table does not exist. I have no problem appending to a table I know exists.

I want the app to determine what to do; not someone manually checking and then running on of the sqll statements.
I tried this but I get this if the table is not there:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
cannot find the input table TBL2"

mysql = "SELECT * FROM TBL2"
recordset1.Open mysql, dbConn1, adOpenKeyset, adLockOptimistic

if not recordset1.EOF then
   code to append records to TBL2
else
   code to create TBL2 and add records
end if
{/CODE]
I would like this:

[code]
if not exist table then
   Create the table (TBL2)
   Add new records from TBL1:
   SELECT TBL1.ID, TBL1.FName, TBL1.LNAME, TBL1.someDate 
   INTO TBL2 FROM TBL1 
   WHERE (((TBL1.someDate)<Date()-120)) 
   ORDER BY TBL1.someDate DESC;
else
   open table (TBL2)
   Append new records from TBL1:
   INSERT INTO TBL2 
   (ID, FName, LName, someDate) 
   SELECT TBL1.ID, TBL1.FName, TBL1.LName, TBL1.someDate 
   FROM APAC WHERE (((tbl1.SOMEDate)<Date()-120)) 
   ORDER BY TBL1.someDate DESC;
end if

Thank in advance.
Chalmers

 

[Sheco]

You may find the .OpenSchema method of the ADO Connection object helpful. It will allow you to get a recordset of all table names in your database.


Look at this for starters:

http://support.microsoft.com/kb/q186246/

 

[Sheco]

Also this:

http://www.freevbcode.com/ShowCode.asp?ID=381

Google will have lots of stuff if you just search for OpenSchema