Check if a Table exists

  • Thread starter Thread starter Kevin D.
  • Start date Start date
K

Kevin D.

I'd like to check my database to see if "TableA" exists, and if it does exist
exit the routine. If it does not exist I want to run Query "Create TableA"
then exit the routine.

Any help is greatly appreciated.

Kevin D.
 
Hi Kevin,

You could loop through the TableDefs collection:

Dim boolFound As Boolean
Dim tbl As TableDef

boolFound = False
For Each tbl In CurrentDb.TableDefs
If tbl.Name = "TableA" Then
boolFound = True
Exit For
End If
Next tbl
If Not boolFound Then
DoCmd.OpenQuery "Create TableA"
DoCmd.Close acQuery, "Create TableA"
MsgBox "TableA created."
End If
Set tbl = Nothing

Hope that helps,

Clifford Bass
 
Thanks Cliff

Clifford Bass said:
Hi Kevin,

You could loop through the TableDefs collection:

Dim boolFound As Boolean
Dim tbl As TableDef

boolFound = False
For Each tbl In CurrentDb.TableDefs
If tbl.Name = "TableA" Then
boolFound = True
Exit For
End If
Next tbl
If Not boolFound Then
DoCmd.OpenQuery "Create TableA"
DoCmd.Close acQuery, "Create TableA"
MsgBox "TableA created."
End If
Set tbl = Nothing

Hope that helps,

Clifford Bass
Click to expand...
 
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
 
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
Click to expand...

This should really have an On Error GoTo 0 after the line that
produces the error. And I'd say On Error Resume Next is one line too
early.

I'd never write code this way, myself. I'd always use an error
handler that does the expected thing for the expected error but can
also recover from any *unexpected* error.

I've posted all three versions of this function before, but here
they are again:

Public Function TableExists(strTableName As String, _
Optional ysnRefresh As Boolean, _
Optional db As DAO.Database) As Boolean
' Originally Based on Tony Toews function in
' TempTables.MDB, http://www.granite.ab.ca/access/temptables.htm
On Error GoTo errHandler
Dim tdf As DAO.TableDef

If db Is Nothing Then Set db = CurrentDb()
If ysnRefresh Then db.TableDefs.Refresh
Set tdf = db(strTableName)
TableExists = True

exitRoutine:
Set tdf = Nothing
Exit Function

errHandler:
Select Case err.Number
Case 3265 ' Item not found in this collection.
TableExists = False
Case Else
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists()"
End Select
Resume exitRoutine
End Function

Public Function TableExists(strTableName As String, _
Optional ysnRefresh As Boolean, _
Optional db As DAO.Database) As Boolean
On Error GoTo errHandler
Dim tdf As DAO.TableDef

If db Is Nothing Then Set db = CurrentDb()
If ysnRefresh Then db.TableDefs.Refresh
For Each tdf In db.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf

exitRoutine:
Set tdf = Nothing
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists()"
Resume exitRoutine
End Function

Public Function TableExists(strTableName As String, _
Optional db As DAO.Database) As Boolean
On Error GoTo errHandler
Dim strSQL as String
Dim rs As DAO.Recordset

If db Is Nothing Then Set db = CurrentDb()
strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
strSQL = strSQL & "WHERE MSysObjects.Name="
strSQL = strSQL & Chr(34) & strTableName & Chr(34)
strSQL = strSQL & " AND MSysObjects.Type=6;"
Set rs = db.OpenRecordset(strSQL)
TableExists = (rs.RecordCount <> 0)

exitRoutine:
If Not (rs Is Nothing) Then
rs.Close
Set rs = Nothing
End If
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists()"
Resume exitRoutine
End Function
 
Back
Top