Create macro...

Hi.

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...



kavvisAsked:
Who is Participating?
 
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.

Chris
0
 
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.

Chris
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
        Next
'        Set btn = CommandBars.FindControl(Id:=2071)
'        btn.accDoDefaultAction
    End With

End Sub

Open in new window

0
 
kavvisAuthor Commented:
hmm.. how do I writ it lke this in MS acess 2007?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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)
            Options
            Trust Centre
            Trust Centre Settings
            MAcro Settings
            Disable All MAcros with warnings
      : Enable Trusted Locations
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            Trusted Locations
            Add a preferred location
            ENSURE YOUR FILE IS IN A TRUSTED 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'

Chris
0
 
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!
0
 
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.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.