472,780 Members | 1,787 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

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:

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 13584
32,534 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
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
  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
Nov 18 '10 #3
32,534 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
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
32,534 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
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
32,534 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

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

Similar topics

by: grahamkc | last post by:
Can anyone help? I have just upgraded from MS Access 2003 to 2007. When trying to link to Sybase tables via the usual ODBC driver method, I get to see the available tables in the database, but get an...
by: Cindy H | last post by:
Hi I am currently using Visual Studio.Net 2003 running on Windows Server 2000 operating system. I have used Visual Studio.net 2003 connecting to Access 2002 databases in the pass with great...
by: redman08 | last post by:
I have produced an Excel (2007) worksheet, with which I want to create a Pivot Table. This would all be done from a module run on an Access 2007 database. Please can anyone supply some simple...
by: ncsthbell | last post by:
I created a database using full blown access 2007. I have put it out for users to grab and test using Runtime Access 2007. They have entered data and now I need to go back into a table and change a...
by: mrzebo1 | last post by:
I have created an Access 2007 database that will be used by about 200 users. The front end will reside on the users desktop and the back end will be on a network folder. I used the database splitter...
by: mrzebo1 | last post by:
I have created an access 2007 database that is split into a frontend/backend. The frontend is located in the MyDocuments folder of each user. The backend is on a folder on a shared network drive. All...
by: ncsthbell | last post by:
I have an access 2007 database (ABC) and I want to import a form from a different 2007 access database (DEF). I go into ABC.mdb and I chose 'get external data' and I select 'access' and browse to...
by: rmurgia | last post by:
Does anyone know how to programmatically import an XML file into an Access 2007 database? I was thinking along the lines of DoCmd.transferText, but could not find an XML option. Thanks in advance.
by: john garvey | last post by:
I have a small web site running on Windows 7 with IIS7 it is written in classic ASP with an ActiveX dll that connects to and extracts/ writes/ deletes data from the database. The database is an accdb...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.