469,127 Members | 1,327 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

Need to Delete a Table if Found to Exist

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).

1 1731
NeoPa
32,162 Expert Mod 16PB
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.

Similar topics

3 posts views Thread by Michael Magg | last post: by
3 posts views Thread by vijaykokate | last post: by
2 posts views Thread by Scott | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.