How to detect if table exists??

[SAG]

How do I detect if table exists in current database?
in another database?

Thanks,

Steve

 

[Duane Hookom]

The easiest way is to open the database window and click on the tables tab.
If you had other requirements, you should probably provide them. We don't
know if you want to check with sql or code or?

 

[TC]

The easiest way is to open the database window and click on the tables tab.
If you had other requirements, you should probably provide them. We don't
know if you want to check with sql or code or?

Wow, I thought *I* was tough sometimes!

For the OP:

dim db as database, td as tabledef
set db = currentdb()
on error resume next
set td = db.tabledefs("MyTable")
if err.number <> 0 then set td = nothing
on error goto 0

if td is nothing then
' table did not exist.
else
' table exists, and its properties are in td.
...
set td = nothing
endif
set db = nothing

To check in some other database, use:
set db = dbengine.opendatabase ("path to other database")

and add as the second-last line:
db.close


HTH,
TC

 

[Allen Browne]

Method 1: SQL
See if this returns anything:
SELECT Name FROM MSysObjects
WHERE ((Name = "MyTable") AND (Type = 1));
Use IN between the FROM and WHERE clauses for an external database.

Method 2: DAO
See if you get an error referring to the TableDef, or loop through all
TableDefs of the CurrentDb(). Use OpenDatabase if it's a different file.

Method 3: ADOX
See if you get an error referring to the Table and check the Type, or loop
through all Tables of the Catalog. Open a Connection for an external file.

 

[SAG]

Thank you TC! Yes, I was surprised by the reply just
before yours. I'll take it from here now that I got your
friendly help.

Steve

 

[SAG]

Thank your Friend! You really sent me some good stuff!
Have a wonderful day!

Steve

 

[TC]

Thank you TC! Yes, I was surprised by the reply just
before yours. I'll take it from here now that I got your
friendly help.
Steve

Steve, don't be offended by the other reply. It is always a good idea to
provide as much information as possible, in your initial post

TC

 

[TC]

I agree entirely. I sometimes answer hopeless questions with the reply:

"My car doesn't work! What is the problem?"

But those (few) to whom I give that answer, seldom seem to get the point!
:-(

TC

 

[Duane Hookom]

I am sorry if I offended anyone. I just expect a little more information
from posters. At times it seems that posters with questions are not willing
to take the time to ask a complete question. Those of us that spend a fair
amount of time attempting to assist have to make assumptions and guess
regarding requirements, table structures, data values, etc.