check if table exists

[caronii]

I'm trying to write a module (Access 2000) to import tables from Excel. The imported tables need to overwrite the existing tables, so I want to check if the table already exists and if it does delete the table prior to importing the update.

I've tried this, but keyword "Exists" isn't valid in Access

Private Sub Update()

Dim BookingsTable As String
Dim FilePath As String

BookingsTable = "tblMens Bookings"
FilePath = "C:\Documents and Settings\kphillip\My Documents\Dollar General\Mens 1o1.xls"


If BookingsTable Exists Then
DoCmd.DeleteObject acTable, "tblMens Bookings"
End If

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, BookingsTable, FilePath, -1, "Bookings"

End Sub

KP

 

[Corticus]

Hi caronii,

The following function will test for the presence of a table (provided as a string argument). Paste the code into a module. Supply the table name in quotes as an arguement and the function will return T/F depending on the table's presence.

Function isTable(tblName As String) As Boolean

    Set db = CurrentDb
    
    isTable = False
    
    For Each td In db.TableDefs
        If td.Name = tblName Then isTable = True
    Next td

End Function

Use like:
isTable("myTable") and replace "myTable" with the table name in question.

 

[caronii]

That worked!

Thanks! ray: