Is there a way to test if a table exists in VBA

[Tony Girgenti]

Is there a way to test if a table exists in VBA before deleteing it ?
I use this code to delete a table "CurrentDb.Execute "DROP TABLE Customer"",
but if it is not there when that happens, it bombs the program with an
error.

Any help would be appreciated.

Thanks,
Tony

 

[Scott McDaniel]

Use inline error handling:

On Error Resume Next
Currentdb.Execute "DROP TABLE Customer"
On Error GoTo MyErrHandler

You don't really care if the table exists or not, all you care about is that
it is not present AFTER you issue this call.

 

[Joe Fallon]

You can use code to test if a table exists:

If TableExists("SomeTable") = True Then
'it exists
Else
' it does not exist
End IF

Put this code in a module:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Bonus:
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function

===========================================================
This may be the fastest technique. It relies on the use of the system
tables:
(Credit for this idea goes to Peter Miller.)

Function TableExists(sTblName As String) As Boolean
Dim rs As Recordset
Set rs = CurrentDb.openrecordset("Select id From msysobjects Where type=1
and name='" & sTblName & "';", dbOpenSnapshot)
If Not rs.EOF Then TableExists = True
rs.Close
Set rs = Nothing
End Function

 

[Tony Girgenti]

Thanks alot Joe. I'll give that a try.

Tony

You can use code to test if a table exists:

If TableExists("SomeTable") = True Then
'it exists
Else
' it does not exist
End IF

Put this code in a module:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Bonus:
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function

===========================================================
This may be the fastest technique. It relies on the use of the system
tables:
(Credit for this idea goes to Peter Miller.)

Function TableExists(sTblName As String) As Boolean
Dim rs As Recordset
Set rs = CurrentDb.openrecordset("Select id From msysobjects Where type=1
and name='" & sTblName & "';", dbOpenSnapshot)
If Not rs.EOF Then TableExists = True
rs.Close
Set rs = Nothing
End Function

 

[Greg Kraushaar]

Is there a reason why a simple

OnError resume next
DeleteObject acTable, strMyTable

Won't work?
If the table's not there, the error will be ignored

In my working code I trap for the actual eNoTable (= 7874) error as
there can be reasons why an existing table can't be deleted and I want
to know about those conditions and deal with them differently.

 

[Joe Fallon]

No reason at all.
Of course it will work.

Some people just migh tlike to use logic like:

If TableExists("SomeTable") Then
'do something
End If

The code is very readable.

 

[Rick Brandt]

Is there a reason why a simple

OnError resume next
DeleteObject acTable, strMyTable

Won't work?
If the table's not there, the error will be ignored

In my working code I trap for the actual eNoTable (= 7874) error as
there can be reasons why an existing table can't be deleted and I want
to know about those conditions and deal with them differently.

One problem though is that this error number is not consistent across Access
versions. IN Access 97 the number is 3011. I have no idea if it changed again
in versions after 2000, but if MS changed it once...

 

[Greg Kraushaar]

Which is why I use a global constant. When I move to a new version of
Access, I test them all (the ones I am interested in anyway) & adjust
the constants

If I was real smart, I would use something like

Select case AccessVersion
case 97
Define Access97 constants
case 2000
define Access2K constants
Case else
Err.Raise eUnknownAccess
end Select

& Yes I know you cant define constants this way >

 

[Greg Kraushaar]

To my mind, the "No table" handling belongs in the DeleteTable
function, not in the calling procedure. All the calling proc needs to
know is that the Table is gone when it gets control back

My actual code looks like
Cut & paste but with extra comments: '****

Sub DeleteTable(ByVal pstrTableName As String)
Const FNNAME As String = "basTableUtils.DeleteTable"
On Error GoTo ProcError
PushFunctionStack FNNAME

SetOwner pstrTableName, CurrentUser()
DoCmd.DeleteObject acTable, pstrTableName
DeleteTable = True
ProcExit:
On Error Resume Next
'******Put Cleanup code here
Exit Sub
ProcError:
Select Case Err.Number
case eAbort

'*******Special Handling goes here
Case eiTableNotFound
'Table doesn't exist, so we don't care
'*****End Special Handling

Case Else
HandleError Err.Number, FNNAME, Err.Source, Err.Description
Err.Raise eAbort 'Pass the error up the chain
End Select
Resume ProcExit
End Sub

This uses my standard error handler, and therefore has a number of
lines not used in this case. They are there because I use a tailored
EHT to generate the bones in EVERY procedure
In the days before EHT, I used to use an AutoKeys macro to achieve the
same end.

The Function Stack remembers the last 16 or so functions called, and
is written to a log along with any unexpected error. A great help in
tracking down elusive bugs.

I use
If fExitCondition then err.Raise eAbort '-------------------------->

whenever I want to exit a procedure early. so I keep the silent error
there all the time. This simplifies some otherwise very messy
constructs very nicely. I use the commented arrow to highlight exits.

I pass the Function Name to the error handler as the running function
is not necessarily the one at the top of the stack.


As a personal extension to the RVBA conventions:
e.... are all CUSTOM errors (Global Constants)
ei.... are internal Access errors that I am interested in. (ditto)
dtbl... are dynamic tables (Table definition can change)
zztbl... are temporary tables (Data can be cleared with impunity)

The first three are self explanatory in their origin.
The third was accidental, but it stuck.

I use the function as follows (with the error handler left out for
brevity)

Function MakeMyDynamicTable() as long
DeleteTable "dtblMine"
MakeMyDynamicTable = ExecuteSQL ("qmakMyDynamicTable")
end function

I trust this is suitalbly readable. >
'Execute SQL returns the number of rows affected by the query,
'or -1 if it fails

Actually,
I very rarely use Make queries.
I prefer to Append into Temporary tables
That way I get to define the table my way without following up the
qmak with a bunch of TableDef stuff.
I am most likely to use dynamic tables as a recordsource for a Report
or Form instance (Where I have identical reports open, all showing
different data). Under these conditions, I copy a template table to
the dtbl, and still use a qapp.