How can I check if a table exists in Access 2002 using VB

[Guest]

I need to delete a table in code before running my program. I need to check
if the table exists first, as my code fails if the table is not there.

 

[tina]

try using the following function, as

Public Function TableExists(ByVal strName As String) As Boolean

Dim tbl As TableDef

TableExists = False

For Each tbl In CurrentDb.TableDefs
If tbl.Name = strName Then
TableExists = True
Exit Function
End If
Next tbl

End Function

call it from your "delete table" code, as

If TableExists(strTableName) Then
DoCmd.DeleteObject acTable, strTName
End If

hth

 

[Guest]

Thank you so much Tina, it worked perfectly!

try using the following function, as

Public Function TableExists(ByVal strName As String) As Boolean

Dim tbl As TableDef

TableExists = False

For Each tbl In CurrentDb.TableDefs
If tbl.Name = strName Then
TableExists = True
Exit Function
End If
Next tbl

End Function

call it from your "delete table" code, as

If TableExists(strTableName) Then
DoCmd.DeleteObject acTable, strTName
End If

hth

 

[tina]

you're welcome!

Thank you so much Tina, it worked perfectly!

 

[David C. Holley]

Use

DCount("Name", "MSysObjects", "Name = " & [tableName] & " AND Type = 1")

The MSysObjects is the table which contains information on all objects
within an Access database. The Type Field indicates the type of object 1
= Table.

 

[Guest]

The methods posted will both work well; however, a faster approach would be
to turn error handling off, do the delete, then turn error handling back on.
That way, you don't have to take up cycles looking for it:

On Error Resume Next
DoCmd.DeleteObject acTable, "MyTableNameHere"

 

[Tim Ferguson]

I need to delete a table in code before running my program.

Actually, it's very rare to need to delete a table. What about just
emptying it -- which will reduce the mbd file bloat, be much quicker and,
at a guess, reduce the chance of file corruption.

db.Execute "DELETE FROM MyTable;", dbFailOnError
If err.Number <> 0 Then
' oops, the table wasn't there after all
db.Execute "CREATE TABLE MyTable (..."

Hope that helps

Tim F