Way to verify an Access table exists

[csiwa28]

Is there a way to find out if a table exists in Access before you try to pull records?

 

[PaulBricker]

In ASP, you can open an ADO connection and test for the table name using something like this. You would have to pass the database name and table name you were looking for to the procedure

Function GetTableName(ByVal db_name as String, tb_name as String)
Dim myFlg As Integer
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_name & ";"
    conn.Open

    
    ' Use OpenSchema and get the table names.
    
    myFlg = 0
    Set rs = conn.OpenSchema(adSchemaTables, _
        Array(Empty, Empty, Empty, "Table"))
    Do While Not rs.EOF
       If rs!TABLE_NAME = tb_name Then
       myFlg = 1
       Exit Do
       Else
       myFlg = 0
       End If
        rs.MoveNext
    Loop
    If myFlg = 1 Then
    MsgBox "The table exists"
    Else
    MsgBox "The table does not exist"
    End If
    rs.Close
    conn.Close
End Function

Paul

 

[csiwa28]

Thank you Paul. I tried the code and received the following error at the exclamation point. Any ideas?

=======================

Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

If rs!TABLE_NAME = "PhotoDate" Then

=======================

 

[csiwa28]

Aha! I placed TABLE_NAME in quotes and parenthesis and it worked.

I changed

If rs!TABLE_NAME = tb_name Then

to

If rs("TABLE_NAME") = tb_name Then

Thanks again.

 

[PaulBricker]

Sorry, I was out for lunch. I pulled that code out of a module in Access. It may be that rs("Table_Name") is the appropriate syntax to use when pulling up a recordset value in ASP.
Anyway, glad you got it.


Paul