Solved

Quick Clearing of Access Data

Posted on 2008-10-07
15
179 Views
Last Modified: 2013-11-29
Is it possible to quickly clear all data in an Access Database without having to delete records on a table-by-table basis?
0
Comment
Question by:verpit
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 9

Expert Comment

by:CCongdon
ID: 22659779
Issue the following query:
TRUNCATE <table_name>
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22659801
Hello CCongdon,

1) Access does not support TRUNCATE

2) Even if it did, you would still have to issue that statement on a table-by-table basis

Regards,

Patrick
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 22659850
verpit said:
>>Is it possible to quickly clear all data in an Access Database without having to delete records on a table-by-table basis?

No, you would have to do it on a table-by-table basis.  The process can be scripted in VBA, though; here
is a routine I use to "purge" all records from all tables whose names start "tmp":

    Dim td As DAO.TableDef

    With DoCmd
        .SetWarnings False
        For Each td In CurrentDb.TableDefs
            If td.name Like "tmp*" Then .RunSQL "DELETE * FROM [" & td.name & "]"
        Next
        .SetWarnings True
    End With
0
 

Author Comment

by:verpit
ID: 22665702
Excellent!   so mine would look like the below pasted code?   Where and how do I put it into my database?

    Dim td As DAO.TableDef
 

    With DoCmd

        .SetWarnings False

        For Each td In CurrentDb.TableDefs

            If td.name Like "tbl*" Then .RunSQL "DELETE * FROM [" & td.name & "]"

        Next

        .SetWarnings True

    End With

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22666248
Looks OK to me...
0
 

Author Comment

by:verpit
ID: 22669825
Where and how do I put it into my database?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22670569
verpit,

Depends on how you use it.  The last time I used it, the front-end used a Switchboard for navigation, and
I modified the code Microsoft plugs in for the "close the database" selection to loop through all the tabledefs
and delete all records where the name began "tmp".  These were tables in the front-end files that I use to
temporarily hold results for reports.

Regards,

Patrick
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:verpit
ID: 22671113
I don't understand how to implement it?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22678336
verpit said:
>>I don't understand how to implement it?

Tell me how and when you would want that code to be launched, and I can advise you accordingly.

Patrick
0
 

Author Comment

by:verpit
ID: 22679088
I don't know how to answer that.  Exactly as the frmAddTasks behaves now, with the new tables would be great.  I just don't know how to do it.  My goal was to use Gray's new tables with the existing forms.  I just don't know how to tie it in and which of mine to delete subsequently.  I think his intention was to "show me" how.  I just don't understand how and can't use the changes he so carefully made with my current forms.
0
 

Author Comment

by:verpit
ID: 22679120
Ooops...  wrong response for this thread...  in response to you patrick...

"Tell me how and when you would want that code to be launched, and I can advise you accordingly.

Patrick"...

Thank you very much for this code.  I need to hold off on the actual implementation of it until the final changes have been made to the db.  I would like to know how to add a button to call it.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22680091
verpit,

The easiest thing to do, then, is to add a commandbutton to a form, and then put the code I supplied
into a Click event sub for that commandbutton.

Regards,

Patrick
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22691786
Patrick, v,

It may be worth mentioning that any existing relationships may need to be broken before running the code.
(Then obviously re-made when you are re-populating the tables)

If Referential Integrity is enforced, the code will not delete any records in a One table if there are any corresponding records in the Many table.

For example, if the code loops to the Customer Table before the Orders Table, then it will not delete any Customer that has Orders. (And you wont be alerted)

Jeff
0
 

Author Comment

by:verpit
ID: 22692398
I was just looking for an easy way to clear data in the tables.  It sounds like "there isn't one" is the correct answer as suggested above.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22692539
No, the code will work.

I just tried to point out a few issues you may encounter.
It all depends on what you call "Easy"

In your case you can simply try it and check the tables, if they are all blank, then no problem.
The worst case is that (if you don't want the break, then relink the tables) you might have to run the code a few times to wash out all the records.

Jeff
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sending email from List Data 2 51
the whoisactive update 12 38
Tags from access to excel 3 27
Run Time Error 3075 15 43
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now