469,271 Members | 1,702 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to delete tables using vba from within the access 2007 database?

167 100+
I am importing a lot of data from spreadsheets and 'import error' tables are being created. I need to be able to delete these tables using vba code in the database. Also, each of the tables are named differently so I would need to do a mask for the deletion. I have import error tables named:
"AdminSvcs$'_ImportErrors1"
"AdminSvcs$'_ImportErrors2"
"AdminSvcs$'_ImportErrors3"

I would probably need to use a mask to look for "*ImportErrors*".

I have never delete tables in the database using vba, just deleted data in the tables.
Any suggestions would be appreciated!!!
Nov 18 '10 #1
7 13024
NeoPa
32,171 Expert Mod 16PB
You can use the TableDefs collection from a database (EG. CurrentDb).

Loop through checking for whatever identifies the tables you want removed then call :
Expand|Select|Wrap|Line Numbers
  1. Call CurrentDb.TableDefs.Delete(TableName)
Nov 18 '10 #2
Mariostg
332 100+
Something like this. I did not test it though...
Expand|Select|Wrap|Line Numbers
  1. Public Function fncDocumentTables() As String
  2.     Dim tbl As DAO.TableDef
  3.  
  4.     For Each tbl In CurrentDb.TableDefs
  5.          If InStr(tbl.Name, "ImportErrors") Then
  6.             CurrentDb.TableDefs.Delete (tbl.Name)
  7.         End If
  8.     Next tbl
  9. End Function
  10.  
Nov 18 '10 #3
NeoPa
32,171 Expert Mod 16PB
Line #6 won't compile Mario.

Parentheses are only used when the procedure is used to return a value, or if the value is dropped explicitly by using Call. Otherwise it thinks it's dealing with an array reference.
Nov 18 '10 #4
Mariostg
332 100+
Yes sometime I tend to forget about parentheses rules. Interestingly enough, it compiled. So I just tried it with and without parentheses and dropped two tables... I am glad it was a dummy db.
But yes normally I don't use parentheses if I don't assign the return value to a variable.
Nov 18 '10 #5
NeoPa
32,171 Expert Mod 16PB
Shock! Horror! Don't tell me you don't have mandatory variable declaration in your projects? That code wouldn't compile in my setup (It gives Compile Error - Expected: =).

See Require Variable Declaration.
Nov 18 '10 #6
Mariostg
332 100+
Shame on me (more or less), I always added the Option Explicit manually. I did not know about the ability to have this set on by default. LOL. It is going to save me some typing.
I don't want to hijack the thread, but whether or not I use Option Explicit, with or with out parentheses at line 6, it compiles.
Nov 18 '10 #7
NeoPa
32,171 Expert Mod 16PB
Well, we're discussing code suggested as a solution so I'm happy we're on topic.

I have no idea what is different then between my setup and yours that it treats that differently. Never mind.

PS I'm glad the link helped.
Nov 18 '10 #8

Post your reply

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

Similar topics

reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.