Create macro...


I trie to make a macro...

What I want to do is to Delite all the tabels in the databas.

So when I run my macro then I make my database empty...

Who is Participating?

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

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.

Chris BottomleySoftware Quality Lead EngineerCommented:
See the following snippet.  Basically it deletes the tables BUT the display will not refresh until compact is run.  If this is the requirmeent then uncomment the three lines and once the module is saved then it will close/compact and re-open.

Sub nomoreTables()
Dim intTable As Integer
Dim strSQL As String
'Dim btn As Object

    With Application.CurrentDb
        For intTable = .TableDefs.Count - 1 To 0 Step -1
            On Error Resume Next
                strSQL = .TableDefs(intTable).Name
                Debug.Print strSQL
                .Execute "DROP TABLE " & strSQL
            On Error GoTo 0
'        Set btn = CommandBars.FindControl(Id:=2071)
'        btn.accDoDefaultAction
    End With

End Sub

Open in new window

kavvisAuthor Commented:
hmm.. how do I writ it lke this in MS acess 2007?
Chris BottomleySoftware Quality Lead EngineerCommented:
To Create a macro:

Alt + F11 to open the macro editor

  For User Code:
     Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, ('Module1' or similar)

Close the Visual Basic Editor.

Check Security as appropriate:

In the application select Tools | Macro | Security
2003 and Earlier : Select Medium
2007 : Outlook - Warnings for all Macros
     : Others - Enable a trusted location and inhibit macros otherwise so do both!
      : Disable Macros
            Office Button, (top left of the screen)
            Trust Centre
            Trust Centre Settings
            MAcro Settings
            Disable All MAcros with warnings
      : Enable Trusted Locations
            Office Button, (top left of the screen)
            Trust Centre
            Trust Centre Settings
            Trusted Locations
            Add a preferred location
            Re-open to ensure it is recognised if the above has been changed
Select OK

To run a macro:

Alt + F8
Select the macro
Select 'Run'

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris BottomleySoftware Quality Lead EngineerCommented:
The question was:

"What I want to do is to Delite all the tabels in the databas.  So when I run my macro then I make my database empty"

I posted an answer that works in my tests therefore what is wrong with the answer given since to me "Learning how to write in macro fields" has no bearing on the question as posed.


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
kavvisAuthor Commented:
Sorry.. Haven´t woke up get! :)

your solution worked very well.
What I ment was that I didn´t know just to write macro in acess 2007... never used it..  thank you for you rhelp!
Chris BottomleySoftware Quality Lead EngineerCommented:
Glad to help and sorry I was 'difficult' but I had put the time into the activity, (pretty much the full hour to get it right) so was a bit miffed at the idea of the Q being deleted.

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 Applications

From novice to tech pro — start learning today.