Access Visual Basic Question

[Bill Johnson]

I want to test for the existance of several tables before
continuing with Visual Basic module.

I've tried to look at If Exist... something along those
lines, but I'm stiking out and the VB help isn't giving me
much to go on.

My module removes several temporary tables (that should be
present) but when they're not, it halts. If I can test
for their presence first, maybe I can avoid an error.

Thanks!

 

[Jonathan Parminter]

-----Original Message-----
I want to test for the existance of several tables before
continuing with Visual Basic module.

I've tried to look at If Exist... something along those
lines, but I'm stiking out and the VB help isn't giving me
much to go on.

My module removes several temporary tables (that should be
present) but when they're not, it halts. If I can test
for their presence first, maybe I can avoid an error.

Thanks!
.

Hi Bill,

have you considered using error handling in your procedure?
Trap the error number for tables that can't be deleted as
they don't exist. Then ignore this error number in your
error handler.

Luck
Jonathan

 

[Douglas J. Steele]

Easiest way is simply to set On Error Resume Next before you call the table,
and check where Err.Number is 0 afterwards. If it is, the table exists. If
not, the table probably doesn't exist (although it's possible that some
other error occurred, and that the table really does exist)

If you don't like that approach, you can write a function that goes against
the list of all tables and returns True if the one you want is found, or
False otherwise.

Using DAO, it would be something like the following untested aircode:

Function TableExists(TableName As String) As Boolean

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

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

End Function

If you don't want to use DAO, you could try something like the following:

Function TableExists(TableName As String) As Boolean
Dim objTable As AccessObject
Dim dbCurr As Object

Set dbCurr = Application.CurrentData
For Each objTable In dbCurr.AllTables
If objTable.Name = TableName Then
TableExists = True
Exit For
End If
Next objTable

End Function

 

[John Vinson]

My module removes several temporary tables (that should be
present) but when they're not, it halts. If I can test
for their presence first, maybe I can avoid an error.

Just delete the tables and trap (and ignore) the error:

On Error GoTo Proc_Error
....
db.Tabledefs("tmpThisTable").Delete
....
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Num
Case xxx ' the 'table not found' error number
Resume Next
Case Else
MsgBox "Error " & Err.Num & vbCrLf & Err.Description
Resume Proc_Exit
End Select
End Sub

 

[david epsom dot com dot au]

Or if you want to avoid exceptions on aesthetic grounds:
you can SELECT from the MSYSOBJECTS table to see if a record
exists for that table name,

or you can iterate through the DAO tabledefs collection
or through the database schema to see if the table exists.

(david)

 

[Bill Johnson]

Thank you.

I used this approach and it seems to work perfectly.
On top of that I learned something about error handling (I
Just never dealt with it before)