Table Exists

[Lee T.]

I am sure this is a "no brainer" but I cannot find it in
help. How do I determine in VB if a table exists in the
database or not?

tia

Lee T.

 

[Douglas J. Steele]

Easiest way is to try to use the table, and trap the error that occurs if it
doesn't exist.

If you don't like doing that, you can write a quick & dirty function that
loops through all of the tables, comparing to the name in question. Here are
two possibilities:

Function TableExists(TableName As String) As Boolean
Dim tblCurr As AccessObject

For Each tblCurr In CurrentData.AllTables
If tblCurr.Name = TableName Then
TableExists = True
Exit For
End If
Next tblCurr

End Function

Function TableExistsDAO(TableName As String) As Boolean
Dim dbCurr As DAO.Database
Dim tblCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tblCurr In dbCurr.TableDefs
If tblCurr.Name = TableName Then
TableExistsDAO = True
Exit For
End If
Next tblCurr

End Function

Note that if you're using Access 2000 or 2002, you'll need to ensure there's
a reference set to DAO for the second one to work, and if you're using
Access 97 or earlier, the first one will not work.

 

[Lee T.]

Douglas,

Thanks, I'll try your suggestion as well what I got to
work on my own. I am coding for A2K, AXP and A2K3 due to
our way of procuring software...

Here is what I got to work for now. Kind of a kluge
but since I only have 7 tables, it works okay.

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.FullName = "Main" Then
DoCmd.DeleteObject acTable, "Main"
End If
Next obj

 

[Douglas J. Steele]

May as well put an Exit For after the delete, to save a little bit of
processing.

On the other hand, you really don't need to loop. As I said before, you can
simply try to delete, and ignore whatever error arises.

On Error Resume Next
DoCmd.DeleteObject acTable, "Main"
If Err.Number <> 0 Then
' The table didn't exist (probably), or something went wrong
Else
' The table's been successfully deleted
End If

 

[Peter De Baets]

This should do the trick:

Private Function xg_TableExists( _
strTableName As String, _
Optional blnCheckCodeDB As Boolean = False _
) As Integer
'* Returns True if table exists
Dim db As DAO.Database
Dim i As Integer
If blnCheckCodeDB Then
Set db = CodeDb()
Else
Set db = CurrentDb()
End If
xg_TableExists = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
xg_TableExists = True
Exit For
End If
Next i
Set db = Nothing
End Function

Hope this helps,

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com