Re: if table exist

Lyle Fairfield wrote:
"DFS" <nospam@xxxxxxxx> wrote in news:M8ZWf.593$68.204
@bignews7.bellsouth.net:

adil wrote:
Hi ,
does any knows how can i check the existence of a table in a ms
access database ?i need a sql statement that provide this function.
Thanks .

Create a new module. Add

Public Function ifTableExists(tblName as String) as Boolean

ifTableExists = False
If DCount("[Name]","MSysObjects","[Name] = '" & tblName & "'") = 1
then ifTableExists = True
Endif

End Function


It might be pretty slow in a query, depending on the # of rows.

What if there's another non-table object named tblName?


Good catch. Newbies do in fact name both their forms and tables 'Contacts'
or 'Recipes', etc.

So, let's make it a bit more spiffy, shall we?


Public Function ifTableExists(tblName as String, tblType as String) as
String

if tblType <> "local" and tblType <> "attached" and tblType <> "ODBC" then
ifTableExists = "Verify table type"
endif

ifTableExists = "not found"

dim objType as double
if tblType = "local" then
objType = -1
elseif tblType = "attached" then
objType = 6
elseif tblType = "ODBC" then
objType = 4
endif

If DCount("[Name]","MSysObjects","[Name] = '" & tblName & "' and [Type] =
" & objType & "") = 1 then
ifTableExists = "found " & tblType & " table"
Endif


End Function


Now it's twice as much work on the user, but somewhat more robust.

Whaddaya think? There are probably more gotchas to be found, but this will
cover the most common table types.




.