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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Thank you so much Tina, it worked perfectly!

tina said:
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
Click to expand...
 
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.
 
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"
 
I need to delete a table in code before running my program.
Click to expand...

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
 
Back
Top