Quick Clearing of Access Data

Is it possible to quickly clear all data in an Access Database without having to delete records on a table-by-table basis?
verpitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CCongdonCommented:
Issue the following query:
TRUNCATE <table_name>
0
Patrick MatthewsCommented:
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
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

verpitAuthor Commented:
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
Patrick MatthewsCommented:
Looks OK to me...
0
verpitAuthor Commented:
Where and how do I put it into my database?
0
Patrick MatthewsCommented:
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
verpitAuthor Commented:
I don't understand how to implement it?
0
Patrick MatthewsCommented:
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
verpitAuthor Commented:
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
verpitAuthor Commented:
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
Patrick MatthewsCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
verpitAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.