Checking for table exists situation

[The dude]

I want to run a peice of code (Access 97) that checks to see if a table exists and if so delete it, if not, move forward. ie;

Table "A" exists?
Yes: Delete it.
No: Run this query.

If the table gets made and the app crashes, the next time they run it the app will crash because the table already exists. If I just tell it to delete the table from the start and the table already exists, the app crashes.

Ideas?

Thanks

 

[Alexandre]

Create a public function:

Function DoesTableExist(pTableName) As Boolean
'Does a table exist in the Database
Dim db As Database
Dim tbl As TableDef
    
    Set db = DBEngine(0)(0)
        For Each tbl In db.TableDefs
            If tbl.Name = pTableName Then
               DoesTableExist = True
            End If
        Next tbl
        
    'db.Close
    Set db = Nothing
End Function

Then:

Dim db AS Database

Set db = DbEngine(0)(0)
If DoesTableExist (YourTableName)then db.Execute "DROP " & YourTableName
DbEngine.Execute YourQueryGoesHere, dbFailOnError
Set db=Nothing

Of course you don t need necessarily to create a function and could do all in one go, but you may find the useful ~to have the function at hand at any time.

Alex

[This message has been edited by Alexandre (edited 02-28-2002).]

 

[Capilano]

Thanks. I was able to modify your code to check for the Tables, and Delete them. Could you possibly assist me with a related problem...

How do you check to see if a drive is mapped and exists?

 

[Alexandre]

Before looking further, why wouldn t you just check the error number that occur in such circumstance, then write code to trap it and display a custom message and undertake whatever relevant action?

Alex