VB Forum / Database Access / November 2005

Trying to learn ado/adox
Creating a class to simplify calling code and assist in learning process.

example of a couple methods/propeties
.TableExists    (method to determine if table of given name exists)
.Tables    (property to return Tables collection of adox catalog of current
connection)
.Table    (property to return a table object of given name)
.Catalog (property to return adox.Catalog with current open ado.connection
as .ActiveConnection)

but even when the TableExists function returns true, the following call
fails to get the table object
assuming oDb is an instance of my class

 If oDb.TableExists(sTableName) Then
   Debug.Print "Table exists"  '(that prints in immediate so I know table
exists)
   'operation errors here
   Set oTbl = oDb.Tables.Item(sTableName)
'that line raises the following err.description
'Item cannot be found in the collection corresponding to the requested name
or ordinal.

in db.cls module

Function TableExists(sTableName As String) As Boolean
 Dim oTestTbl As Table
 On Error Resume Next
   Set oTestTbl = Me.Tables.Item(sTableName)
   If Not Err Then
     Set oTestTbl = Nothing
     TableExists = True
     Exit Function
   End If
 On Error GoTo 0
End Function

the .Tables Property:
(moCatalog is a member variable of type adox.Catalog object)
Property Get Tables() As Tables
   'ensure connection is open and catalog is set
     If Me.Catalog Is Nothing Then
        MsgBox "No catalog"
     End If

 If moCatalog.Tables Is Nothing Then
     MsgBox "moCatalog.Tables Is Nothing "
   Else
     Set Tables = moCatalog.Tables
 End If

End Property

the .Catalog property
(moConn is a member variable of type ado.Connection object)
Property Get Catalog() As ADOX.Catalog
 If moConn Is Nothing Then
   Exit Property
 End If

 If Not moConn.State = adStateOpen Then
   Exit Property
 End If

 If moCatalog Is Nothing Then
   Set moCatalog = New ADOX.Catalog
 End If

 If moCatalog.ActiveConnection Is Nothing Then
   Set moCatalog.ActiveConnection = moConn
 End If

 Set Catalog = moCatalog

End Property

not sure what I'm doing wrong here.
Thanks for any pointers
Mark