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

Test to see if an object (table) exists

P: 46
Hello,

I have a batch of code that imports data from an Excel file to an Access table. Sometimes the import creates a table with import errors and sometimes it does not. I would like the code to check to see if the table exists (call it "tbl_errors") and if it does, delete the table.

Seems like this would be an if statement but I am unsure how to approach. Any help would be appreciated.

-Brian
Sep 9 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Brian.

Though there are several ways to check table existance, it is really not needed if you know exact table name.
Just try to delete it while ignoring possible errors.
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. DoCmd.RunSQL "DROP TABLE <TableName>;"
  3. On Error GoTo 0
  4.  
Regards,
Fish
Sep 9 '08 #2

P: 46
Thank you! Out of curiosity, how would one check to see if an object exists, thus eliminating the need for the error handling?
Sep 9 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Thank you! Out of curiosity, how would one check to see if an object exists, thus eliminating the need for the error handling?
  • You may use Access hidden table [MSysObjects] where info about all Access objects (including tables is stored)
  • You may iterate CurrentDb.TableDefs collection

Regards,
Fish

P.S. "eliminating the need for the error handling" I will add to memories :D
Sep 9 '08 #4

P: 46
Thanks. For what it's worth, sometimes I prefer to get an actual error as opposed to a specified set of code that may or may not alert that something has gone awry. Thanks again.
Sep 9 '08 #5

ADezii
Expert 5K+
P: 8,638
Thank you! Out of curiosity, how would one check to see if an object exists, thus eliminating the need for the error handling?
The following code will let you know one way or the other if a Table exists, and will also prompt you prior to Deletion. Substitute your own Table Name for the Constant:
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As TableDef
  2. Dim intResponse As Integer
  3. Dim blnTableExists As Boolean
  4. Const conTABLE_TO_DELETE As String = "<Your Table Name>"
  5.  
  6. blnTableExists = False      'Initialize
  7.  
  8. For Each tdf In CurrentDb.TableDefs
  9.   If tdf.Name = conTABLE_TO_DELETE Then
  10.     blnTableExists = True
  11.       Exit For
  12.   End If
  13. Next
  14.  
  15. If blnTableExists Then
  16.   intResponse = MsgBox("DELETE the Table [" & tdf.Name & "]?", vbQuestion + vbYesNo, "Table Deletion")
  17.   If intResponse = vbYes Then
  18.     CurrentDb.TableDefs.Delete tdf.Name
  19.     RefreshDatabaseWindow
  20.   End If
  21. Else
  22.   MsgBox "Table [" & conTABLE_TO_DELETE & "] does not exist!", vbOKOnly + vbExclamation, "No Tableamundo!"
  23. End If
  24.  
Sep 10 '08 #6

Post your reply

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