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

Need to Delete a Table if Found to Exist

P: 1
Hello,
First, I must admit, that I am not trained in coding with VBA. I use MS Access macros and queries to build my application. I use some temporary import files, and need to either run a macro, or some VBA, to test if they exist, and then if they do, to delete them.

My table name is "TempImport1"

I've researched this via google searches and have come across some VBA that might work, but I am lost trying to figure out how to put the code into a module or a click sub button. I have cut/pasted VBA code under a button function in the past, and it worked, but I can't figure out why it's not working this time.

Honestly, I'm sure it's my lack of understanding of private vs public functions, as well as of course, the fact that I don't know VBA. Thank you for any advice you can provide.

Here's the code I'm trying to make work:
Expand|Select|Wrap|Line Numbers
  1. Function IsTable(sTblName As String) As Boolean
  2.     'does table exists and work ?
  3.     'note: finding the name in the TableDefs collection is not enough,
  4.     '      since the backend might be invalid or missing
  5.  
  6.     On Error GoTo TrapError
  7.     Dim x
  8.     x = DCount("*", sTblName)
  9.     IsTable = True
  10.     Exit Function
  11. TrapError:
  12.     Debug.Print Now, sTblName, Err.Number, Err.Description
  13.     IsTable = False
  14.  
  15. End Function
May 7 '17 #1

✓ answered by NeoPa

Let's take it one step at a time. We'll start with a simple procedure that is self-contained and will ensure that if a named table exists then it is deleted. That's my reading of what you're after. Please let us know if I have that wrong.

We can move on later to other questions, posted in separate threads, which cover some of the other basics.
Expand|Select|Wrap|Line Numbers
  1. Private Sub LoseTable(strTable As String)
  2.     On Error Resume Next
  3.     Call DoCmd.DeleteObject(ObjectType:=acTable, ObjectName:=strTable)
  4. End Sub
NB. It's not at all rare to see piles of code where only a couple of lines are required. This comes from either the developer having a limited understanding of the requirements or the developer getting code from somewhere where the requirements are different from their own in some way(s).

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,418
Let's take it one step at a time. We'll start with a simple procedure that is self-contained and will ensure that if a named table exists then it is deleted. That's my reading of what you're after. Please let us know if I have that wrong.

We can move on later to other questions, posted in separate threads, which cover some of the other basics.
Expand|Select|Wrap|Line Numbers
  1. Private Sub LoseTable(strTable As String)
  2.     On Error Resume Next
  3.     Call DoCmd.DeleteObject(ObjectType:=acTable, ObjectName:=strTable)
  4. End Sub
NB. It's not at all rare to see piles of code where only a couple of lines are required. This comes from either the developer having a limited understanding of the requirements or the developer getting code from somewhere where the requirements are different from their own in some way(s).
May 7 '17 #2

Post your reply

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