Deleting tables on exit

[Dazzla]

Right, i have a database that creates tables using the make table query. What i want it to do is delete these tables when the exit button is clicked. The Vb i used will delete the tables if they are there, however if they have not been created i get an error message saying it cant find the object and wont close. Help!!!!! Seen this somewhere and tried it

If ObjectExists(acTable, "TABLE NAME") Then DoCmd.DeleteObject acTable, "TABLE NAME"
End If

But i get the error message saying "Sub or functioned not defined"

Is there a simple way round this as if its something that needs to be installed on access it could cause problems as when this goes live it will be used on many different machines using access.

 

[ghudson]

You are missing the ObjectExists() function. It is not built into Access, it might have come from the Northwind db. Search the forum for the ObjectExists() function and add that to your db and all should then work.

 

[Dazzla]

Im sorry if im coming accross as not being to bright, but VB has never been my strong point. I did as you said, found something that i vaguely understood and put this in.

Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
Dim db As Database
Dim tbl As TableDef

Set db = CurrentDb()
ObjectExists = False

If strObjectType = "Table" Then
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit Function
End If
Next tbl
End Function

when i click the exit button i now get the message "user defeined type not defined" and it highlights db As Database

 

[ghudson]

That function should be in a public module not a form module.

You are missing the DAO reference.

From the VBA window, click the menu items \ Tools \ References...

and then find then select the Microsoft DAO 3.x Object Library reference. Click the ok button and all should then work.

 

[Dazzla]

Thanks, sorry for being so simple but i am learning.

Your a genious

 

[ghudson]

Your welcome!

We all started somewhere.