Does Table Exist? Need help

[Guest]

I am importing an excel file to a table. This does not work if the table
exists, so I am using the following: DoCmd.DeleteObject acTable, strFileName
Problem is if the table does not exist it get an error, how can I check to
see if the table exists before running the DeleteObject?

 

[Allen Browne]

Test:
IsNull(DLookup("ID", "MSysObjects", "([Name] = """ & strFileName & """)
AND ([Type]=1)"))

This one lets you look in any database:

Function TableExists(strTable As String, Optional strFile As String) As
Boolean
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM [" & strTable & "]"
If strFile <> vbNullString Then
strSql = strSql & " IN """ & strFile & """"
End If
strSql = strSql & " WHERE (False);"

On Error Resume Next
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
TableExists = (Err.Number = 0&)
rs.Close
Set rs = Nothing
End Function

 

[Guest]

Thank you so much this will be a great help
Also realy appreciate the code I can use for any database.
Cynthia

Test:
IsNull(DLookup("ID", "MSysObjects", "([Name] = """ & strFileName & """)
AND ([Type]=1)"))

This one lets you look in any database:

Function TableExists(strTable As String, Optional strFile As String) As
Boolean
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM [" & strTable & "]"
If strFile <> vbNullString Then
strSql = strSql & " IN """ & strFile & """"
End If
strSql = strSql & " WHERE (False);"

On Error Resume Next
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
TableExists = (Err.Number = 0&)
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am importing an excel file to a table. This does not work if the table
exists, so I am using the following: DoCmd.DeleteObject acTable,
strFileName
Problem is if the table does not exist it get an error, how can I check to
see if the table exists before running the DeleteObject?