User Defined Type

[George R]

I get an error message: "User-defined type not defined" when I use the
following code to determine if a table exists:

Code Courtesy of
' Dev Ashish
'
'
Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function

Thank you for your consideration.
George R

 

[Marshall Barton]

I get an error message: "User-defined type not defined" when I use the
following code to determine if a table exists:

Code Courtesy of
' Dev Ashish
'
Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function

Most likely that's because you do not have a reference to
the DAO library. A200 and A2002 referenced the ADO library
by default, so, if you are working on a new database with
the default references, you should open the references
window (Tools menu) and select the DAO library. If you do
not intend to use the ADO (ActiveX Data Objects) library,
you should uncheck it so it won't get in the way. If you
need to use both libraries, then be sure to disambiguate
your declarations. E.g.

Dim rs As DAO.Recordset
or
Dim rs As ADODB.Recordset

 

[George R]

Thank you Marsh. I will follow your instructions.
George

 

[TC]

Hmm:

Function fExistTable(strTableName As String) As BOOLEAN
dim s as string
on error resume next
s = dbengine(0)(0).tabledefs(strTableName).name
fExistTable = (err.number = 0)
end function

HTH,
TC

 

[George R]

Thank you, TC.
I came up with a rather inelegant approach which is working, for now:
Since I only needed to know if the table existed so that I could delete it,
I just put the deletion in the Exit part of the procedure and preceeded it
with "On Error Resume Next."
When time permits I plan to try both your suggestion as well as Marsh's.