Solved: Table Exists: a better mousetrap

[stanl]

I make use of several Access tables, created mainly as middleware to quicky import ISAM data into tables via ADO.

When things get confusing, it's nice to know if a table already exists so as to choose between SELECT INTO or INSERT INTO.

In DAO you can query the tabledefs, in ADO the tables collection, or use openschema(), or use ADOX. Of course a simpler way would be

cTable='temp'
....
Select Name from MSysObjects Where Name ='" & cTable & "'"

then check for eof. However, even if you created the MDB, MSysObjects is likely to give a permissions error unless you go into User Permissions and check Read. My question is, can you do this programatically? Stan

[stanl]

My bad... the answer seems to be here. I'll mark this closed.

http://www.vbaexpress.com/forum/showthread.php?t=9595

[stanl]

Actually this is not solved. I coded according to the article I referenced in the previous post which is derived from an MSDN post. I set up my connection string and obtained object handles to an ADODB.Connection and an ADOX.Catalog. But trying to execute SetPermissions() I get an error: Object or Provider is Not Capable of Performing the Requested Operation

[CreganTur]

Why couldn't you use ADO and loop through the names of the tables in the database and have an If statement to see if the name of a table in the database matches your desired table name. Use it in conjunction with a boolean variable to determine if the table is there or not.

It's not fancy, but it would work.

[stanl]

Why couldn't you use ADO and loop through the names of the tables in the database

I think I mentioned I could already do that in the initial post - hence the 'better mousetrap' reference. I also mentioned OpenSchema() which is faster than ADO... now I'm just a little confused as to the ADOX error when I try to SetPermissions():dunno