Table Exists

  • Thread starter Thread starter Lee T.
  • Start date Start date
L

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