By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Check if table exits before delete query

100+
P: 365
Hello, me again

i am creating tables (and deleting them) based on the Public variable StaffID the reason for this is the information used in these tables is used to display staffing information and needs to be viewable on multiple terminals, so i am creating the tables with " & LogStaffID & " infront of the table/query name, but as to not clutter up the database i am deleting the tables on unload of the form, but to avoid errors i wonder if there is an if command i could use to check the table exist

such as

if "" & LogStaffID & "TableName" exists then do delete query

do create query

thanks if ya can help
Jan 2 '08 #1
Share this Question
Share on Google+
1 Reply


jaxjagfan
Expert 100+
P: 254
Hello, me again

i am creating tables (and deleting them) based on the Public variable StaffID the reason for this is the information used in these tables is used to display staffing information and needs to be viewable on multiple terminals, so i am creating the tables with " & LogStaffID & " infront of the table/query name, but as to not clutter up the database i am deleting the tables on unload of the form, but to avoid errors i wonder if there is an if command i could use to check the table exist

such as

if "" & LogStaffID & "TableName" exists then do delete query

do create query

thanks if ya can help
Here is a function I used for cleaning up import error tables and should work for you as well. Attach the CleanErrors on the form Unload event.

Expand|Select|Wrap|Line Numbers
  1. Public Function CleanErrors()
  2.  
  3. Dim tname As TableDef
  4. Dim db As Database
  5. Dim tdef As TableDefs
  6. On Error Resume Next
  7. Set db = DBEngine(0)(0)
  8. Set tdef = db.TableDefs
  9.  
  10. For Each tname In tdef
  11.     If tname.Name Like "*" & LogStaffID & *" Then
  12.     DoCmd.DeleteObject acTable, tname.Name
  13.     End If
  14. Next
  15.  
  16. End Function
  17.  
Jan 2 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.