Detect table existence

[Guest]

When I send out a new program version, I have a form that allows the user to
import up to 3 tables of data from his old version. To detect the presence
of the imported table, I have a dlookup control that looks for a required
field in the imported table. If the table isn't there, the field shows
#Error. With a macro, I detect that #Error with "not IsError" and take proper
actions. When I convert the macro to code, the "If IsError(me.controlName)"
command is ignored, as if it didn't exist. Is it my program or "just the way
it is"? Is there another way to detect the existence of a table? Access 2000
W2000. Thanks in advance.

 

[Dirk Goldgar]

When I send out a new program version, I have a form that allows the
user to import up to 3 tables of data from his old version. To
detect the presence of the imported table, I have a dlookup control
that looks for a required field in the imported table. If the table
isn't there, the field shows #Error. With a macro, I detect that
#Error with "not IsError" and take proper actions. When I convert
the macro to code, the "If IsError(me.controlName)" command is
ignored, as if it didn't exist. Is it my program or "just the way it
is"? Is there another way to detect the existence of a table?
Access 2000 W2000. Thanks in advance.

I'm not sure what's going on there, but there are certainly simpler ways
to determine if the table exists. Here's one:

'------ start of code ------
Function fncTableExists(TableName As String) As Boolean
On Error Resume Next
fncTableExists = (TableName = CurrentDb.TableDefs(TableName).Name)
End Function
'------ end of code ------

It's worth noting that the above simple function doesn't actually test
just whether the table exists -- it tests whether you can get at it and
read its name, which is not quite the same thing. Any error in this
process will be expressed by the function as a False return value, which
could be misleading. Some people insist that you should loop through
the TableDefs collection instead, so that any error that is raised is a
real error that should be reported; for that you would use code like
this:

'------ start of code ------
Function fncTableExists(TableName As String) As Boolean

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb

For Each td in db.TableDefs
If td.Name = TableName Then
fncTableExists = True
Exit For
End If
Next td

Set db = Nothing

End Function
'------ end of code ------

While I recognize this approach as "purer", I haven't run into a
situation in my own work where the first approach wouldn't do well
enough.

Yet a third possibility is to interrogate the system table Access uses
to keep track of these things. For example,

fncTableExists = _
Not IsNull( _
DLookup("Id", "MSysObjects", _
"Name='" & TableName & _
"' AND Type In (1, 6)"))

However, I don't think it's really necessary in this case to build code
on an assumption about the internal structures used by Access.