SQL

Hi,

Is there a way to check if a table exists using SQL? I need to check if a table exists so I can drop it. Currently, I take care of it by capturing the error it gives me when the table doesn't exist on the Access database when I try to drop it. However, I would prefer not to do this by responding to an error.

Thanks all,

RS

No standard SQL expression for this. You can find this information in system table or use ADOX collection Tables

To retrieve all the tables something like...
set cnn = new ADODB.Connection
cnn.Open DB_CONNECTION

set rstSchema = cnn.OpenSchema(adSchemaTables, Array(empty, empty, empty, "TABLE"))

Do Until rstSchema.EOF
rstSchema.MoveNext 'get nect table
Loop



HTH

You should be able to query (make a sql on) MSysObjects table. Is a system table where you can find the names of all existing objects in your access Db. Look for field "Name" and put condition where Name = your_serching_for_Table.
If you do not see the table in Access, go to menu "tools", "options" and in tabs "View" check "system objects"

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

Thanks for your suggestions!

Here's a query you can run to check if a table exists on SQL Server, but I don't know if it will work for an Access Database.

if exists (select * from sysobjects where id = object_id('dbo.your_table_name') and sysstat = 99)
print 'it exists'
else
print 'doesn''t exist'

Thanks.