[RESOLVED] How to test if table exists

[chilling]

Access, VB

There must be a simpler way to test if a table exists in Access rather than having to loop arround all the objects ... isn't there? My code is:

Dim catLocal As ADOX.Catalog
Dim tblLinked As ADOX.Table
Dim lintTable As Integer
Dim lblnTable as boolean
 
' Create a connection to QuoteWerks
Set catLocal = New ADOX.Catalog
catLocal.ActiveConnection = lcnnQW
 
' Loop arround all the objects
For lintTable = 0 To catLocal.Tables.Count - 1
    If CStr(catLocal.Tables.Item(lintTable).Name) = lstrQWTable Then
        lblnTable = True
        Exit For
    End If
Next ' Table

(PS I know I posted this as part of another post but did not get a reply ... see How to add ODBC linked table in an Access database where I still have a problem with creating the primary key to a linked table)

[manavo11]

Here is a function I have used with MySQL. I would assume the errors would be the same :

Function TableExists(sName As String) As Boolean
    On Error GoTo ErrHandler
    
    TableExists = True
    
    Rs.CursorType = adOpenStatic
    Rs.CursorLocation = adUseClient
    Rs.LockType = adLockPessimistic
    Rs.Source = "Select * From " & sName & " Where 1=2"
    Set Rs.ActiveConnection = Mysql_Connection
    Rs.Open
    Rs.Close
    
    Exit Function
    
ErrHandler: If Err.Number = -2147217865 Then
                TableExists = False
                Err.Clear
            ElseIf Err.Number = -2147217900 Then
                TableExists = True
                Err.Clear
            Else
                MsgBox Err.Number & " - " & Err.Description
                Err.Clear
            End If
End Function

[kaffenils]

You can check if there exists a record in table MSysObjects (it's a hidden system table) where the Name column is your table name and column Type=4 (tables).

[Hack]

I think manavo11's way of running a query against the table is probably the easiest, most straightforward way of checking. If it doesn't exist and error will be returned which has been trapped for and bada bing bada boom, you know immediately if it is there.

[chilling]

Thanks guys.... for that. Any thoughts on my problem for not being able to create a Primary Key index when creating a linked table in access: See How to add ODBC linked table in an Access database

[Hack]

Well, I don't know how to do it using ADOX, but this is how I do it using ADO. This, by the way, is pulled directly out of one of my projects that is currently in production.

gsSql = "CREATE TABLE CUSTOM_SETTINGS ("
gsSql = gsSql & "  ROW_ID INT PRIMARY KEY,"
gsSql = gsSql & "  KEY_SECTION VARCHAR(65) NULL,"
gsSql = gsSql & "  KEY_NAME VARCHAR(65) NULL,"
gsSql = gsSql & "  STRING_SETTINGS VARCHAR(256) NULL,"
gsSql = gsSql & "  BOOL_SETTINGS INT NULL,"
gsSql = gsSql & "  LONG_SETTINGS INT NULL"
gsSql = gsSql & "   )"
gdB.Execute gsSql