How to detect if table exists??

  • Thread starter Thread starter SAG
  • Start date Start date
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?
 
Duane Hookom said:
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?
Click to expand...


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
 
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.
 
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 said:
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
Click to expand...

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
 
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
 
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.
 
Back
Top