Clearing a Database Project - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Clearing a Database Project

Jerry Fowler

4 years ago

I have done extensive google and you tube searches. I have been building a database project for a local non-profit and they have finally signed off on it.
Now what I would like to do is clear out all the testing data I have been using so they have a blank database to start out in. At that time I will import all their clients and current data they have in Excel tables.
I have found many ways to delete tables and queries but I just want to clear them out and if possible restart the auto numbering (if not it's not a huge deal).

Thanks

Jerry Fowler

	Kevin Robertson @Reply 4 years ago
The easiest way would be to open up each table individually, select all your sample data and hit delete on the keyboard. Another way would be to loop through all the tables and run a Delete statement, but that would require some programming. After your sample data is deleted run a Compact and Repair to reset the Autonumbers.

	Jerry Fowler OP @Reply 4 years ago
Oh well hoping for an easier way as there are a ton of tables. That might not take too long.

	Scott Axton @Reply 4 years ago
What ever you do - BACK UP YOUR DATABASE FIRST If something goes sideways you don't want to have to start over.

Scott Axton @Reply

4 years ago

OK this is pretty cheesy but I just tried it and it works.

Since this is more likely than not a 'one time' happening, it will save you bunches of time.

First Download a copy of the TechHelp database and try it out so you can see my steps and follow them below.

I cannot stress enough - have a good BACKUP of your database.  (Don't just assume that it is good.  Copy it to another directory and test it out. - OR better yet do these steps on the COPY.)

Step 1:   Highlight all of your tables.  Deselect any "helper" table that just has lookup data that you want to keep.  Press Ctrl + C and Ctrl + V.  (Copy and Paste)
Step 1a:  Choose "Structure Only" for each of your tables. As the dialog box comes up for each table.

	Scott Axton @Reply 4 years ago

	Scott Axton @Reply 4 years ago
Step 2: Re-select all of the tables that DON'T SAY Copy Of. Press the Delete Key.

	Scott Axton @Reply 4 years ago

	Scott Axton @Reply 4 years ago
Step 3: Starting at the top Rename any table that says Copy Of removing the "Copy Of".

	Scott Axton @Reply 4 years ago

	Scott Axton @Reply 4 years ago
Step 4: Go to Database Tools and Compact and Repair your database.

	Scott Axton @Reply 4 years ago

	Scott Axton @Reply 4 years ago
That's pretty much it. You now have empty tables that have the Autonumber reset. FYI your forms will probably error out until you 're-seed' some data in the tables. At this stage you are ready to import / append your real data to the tables and you should be off to the races.

	Scott Axton @Reply 4 years ago
Yeah Richard would probably knock out a program that would do all this in seconds. If I had a day or two I might even come up with something. This doesn't have any programming and it works - so shrugs BTW - LOL

	Richard Rost @Reply 4 years ago
That's a good method, Scott, but still kinda slow if you have to rename a lot of tables. You can do it with a little code and a few delete queries. I'll add this to the TechHelp list.

	Scott Axton @Reply 4 years ago
Richard Agreed. Kind of a pain to rename tables. Two things I was taking into consideration - #1 I see that Jerry's only at the Started Access Expert Series level and i didn't want to overwhelm him. #2 Being a one time shot I figured why do a lot of coding. This was the first solution I came up with.

	Richard Rost @Reply 4 years ago
You are absolutely correct on both accounts.

	Jerry Fowler OP @Reply 4 years ago
Thanks Scott it does seem like a rather easy method. Although I will forward to the Tech Help Video when you have time and it comes up. Have a great week

Kevin Robertson @Reply

4 years ago

Hi Jerry,

Your question really intrigued me. Here is a possible solution for you.
Where I ignore the state table in the code below replace with any tables you don't want cleared.

Private Sub ClearAllTables_Click()



    Dim db As Database

    Dim td As TableDef

    

    If MsgBox("Are you sure?", vbYesNoCancel + vbCritical, "Clear All Tables") <> vbYes Then Exit Sub

    

    Set db = CurrentDb

      

    For Each td In db.TableDefs

        ' ignore system and temporary tables and state table (helper)

        If Not (td.Name Like "MSys*" Or td.Name Like "~*") And _

            (td.Name <> "StateT") Then

            db.Execute "DELETE from " & td.Name

        End If

    Next

    

    Set td = Nothing

    Set db = Nothing

    

End Sub

Scott Axton @Reply

4 years ago

Kevin -
Short and sweet.  I like it.

The only thing about this is the danger.  To really flesh it out I'd add making a backup automatically.  I'd also make it require a password to make it a little more safe from accidental execution.

The solution I provided and yours will only work in a single file db.  It won't work for Split DBs.  You'd have to be in the backend to run it.

Jerry or anyone else reading this:
If you have done some programming and want to attempt this you need to exclude each and every table you don't want cleared.
Like this:



                    And _

            (td.Name <> "StateT") AND _

            (td.Name <> "SecondT") AND _

            (td.Name <> "ThirdT")

	Scott Axton @Reply 4 years ago
My thinking for something like this would be to make a table with all the table names to clear. Then feed that into your Sub.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View