Checking Table Existence (2000)

[grovelli]

In the Module1 module of the attached file I've prepared DAO (CheckTables) and ADO (CheckTable) versions of a function that determines whether a table exists.
I've run them against the Italgen table but only the DAO version correctly determines that the table exists.
What's wrong with the ADO version?
<font color=red>
Function CheckTables(strTable) As Boolean
On Error GoTo ErrorHandler
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
CheckTables = True
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 3265 Then
CheckTables = False
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function
</font color=red><font color=448800>
Function CheckTable(strTable) As Boolean
On Error GoTo ErrorHandler
Dim cat As New ADOX.Catalog
Set cnn = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
CheckTable = True
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 3265 Then
CheckTable = False
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function</font color=448800>

[rory]

Hi,
You missed out:
cat.ActiveConnection = cnn
Hope that helps.

[grovelli]

Thanks Rory,
your input sparked my imagination and I've noticed that I can leave out
Set cnn = CurrentProject.Connection
if I type
cat.ActiveConnection = CurrentProject.Connection
instead of
cat.ActiveConnection = cnn
<font color=ff69b4>Ciao</font color=ff69b4>

[grovelli]

I've done the same for queries but why doesn't the function detect crosstab queries such as FQuery1 query in the attached file?

Function CheckQuery(strQuery) As Boolean
On Error GoTo ErrorHandler
Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Set cnn = CurrentProject.Connection
Set tbl = cat.Views(strQuery)
CheckQuery = True
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 3265 Then
CheckQuery = False
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function

[Francois]

Because a crosstab query is a procedure and not a view.
Set tbl = cat.Procedures(strQuery)
will not generate an error.