checking database tables

[tomjowitt]

how can i check to see if a database table exists?

is there a simple expression to handle this or do i need to figure out some way of looping through all the tables in the database and checking them one by one?

[Andrew_Fryer]

You could trap any error that occurs - if one does then the table does not exist, i.e

On Error Goto notExist
strSql = "Select * from <table>"
db.execute strSql

resumeNext:

notExist:
tableNotExist = true
resume resumeNext

Andrew

[Iouri]

If you are using Access you can execute the following SQL

Select Name from MSysObjects where Type = 1

1-All tables
2-System Databases
3-Objects(Tables,Dtabase,Relationships,Forms,Modules,SysRelationships,Scripts)
4-ODBC linked tables
5-Queries
6-Access linked tables




Iouri Boutchkine
[email protected]

[EAK]

if still a problem, this worked for me,

'Load tables/Recordsets
With db
Err.Clear
set rsCatalog = .OpenRecordset(AP_rsCATLG, dbOpenDynaset)
If Err.Number = 3078 then
'add table
GetMissingTable "CatalogOrders"
set rsCatalog = .OpenRecordset(AP_rsCATLG, dbOpenDynaset)
Err.Clear
End If

[tomjowitt]

thanks people, i went for the error trap method in the end