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

Drop Table

P: 4
I KNOW this will sound silly asking and I promise I have read every possible post all over the internet, but for some reason I can not get my Drop Table command to work.

The Access '03 tables I want to delete/drop are automatically created when the import is initiated. Each table has "ImportErrors" verbaige in it's name, but the suffix of the name is a sequential running based on the number of files imported.

Exactly how would i automate the droping of these tables? Macro calling a module? Macro calling a SQL qry? Macro with a SQL stmt in it? Nothing I do seems to work. Please help me!!

Thank you in advance
Dec 19 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

You've surprised me. ;)
The following SQL statement drops table without any problem.
Expand|Select|Wrap|Line Numbers
  1. DROP TABLE <TableName>;
  2.  
Regards,
Fish
Dec 19 '07 #2

P: 4
But, I have multiple tables, that all have different endings to their table names, so I can't get the Drop Table to work with any type of "wildcard" characters
Dec 19 '07 #3

FishVal
Expert 2.5K+
P: 2,653
But, I have multiple tables, that all have different endings to their table names, so I can't get the Drop Table to work with any type of "wildcard" characters
The following procedure will do it.

Expand|Select|Wrap|Line Numbers
  1. Public Sub DropMultipleTables(strLike As String)
  2.  
  3.     Dim tbl As DAO.TableDef
  4.  
  5.     For Each tbl In CurrentDb.TableDefs
  6.         If tbl.Name Like strLike Then CurrentDb.TableDefs.Delete tbl.Name
  7.     Next
  8.  
  9.     Set tbl = Nothing
  10.  
  11. End Sub
  12.  
Dec 19 '07 #4

P: 4
ok...i must be close, because I'm not getting any errors...but I'm not seeing where anything is happening either. The tables I'm attempting to drop are still showing..

I know I'm missing something simple, it's because I've been working on this for TOO long!!

I pasted your code into a new module and set to view watch, but nothing is getting triggered.....any thoughts? Other than the obvious...I'm stupid! :o)

Thank you for helping me, I so greatly appreciate it.
Dec 19 '07 #5

FishVal
Expert 2.5K+
P: 2,653
:)

Do you include wildcards in the sub argument?
Like the following:
DropMultipleTables "Table*"

Certainly I've tested the code. It works just fine deleting [Table1], [Table2], [Table3].
Dec 19 '07 #6

jaxjagfan
Expert 100+
P: 254
Try this for the error tables. Its a Function I use in a module to drop tables with the "_InportErrors" in its name. You can call the function from your import routines.

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 "*_ImportErrors*" Then
  12.     DoCmd.DeleteObject acTable, tname.Name
  13.     End If
  14. Next
  15.  
  16. End Function
  17.  
Dec 19 '07 #7

P: 4
Thank you both so much!

JaxJagFan,
Your coding is "exactly" what I was trying to come up with and for the same purpose. It ran like a deer!!!

Thank you so very much.
P.S. Jax Jag huh? I believe we're in the same location
Dec 20 '07 #8

jaxjagfan
Expert 100+
P: 254
HTH

Have had same issue. I keep a lot of my previous Access projects in a folder I call Toolbox. Some date back over 10 years.

BTW - Jacksonville (Mandarin) and will be at the game on Sunday.
Dec 20 '07 #9

Post your reply

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