delete macro to remove data in table

Hi Experts (access 2003)

Need a macro to delete all the data in tbl_output based on the field "level" - so delete everyone who is not a level 1.

the fields in the table are:-
Id
first name
last name
 Parent
level
route217Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
That is not a macro as you'd asked for... it is VBA code :-)

To fill in the blanks in the code:


sub tidyup()

dim strSQL as string
strSQL = "DELETE * FROM tbl_output WHERE Level <> 1"

Docmd.setwarnings false
Docmd.RunSQL strSQL   
Docmd.setwarnings true
end sub

Open in new window



Or my preferred method:

sub tidyup()

dim strSQL as string
strSQL = "DELETE * FROM tbl_output WHERE Level <> 1"
currentdb.execute strSQL, dbFailOnError

end sub

Open in new window

0
 
mbizupCommented:
Try this in a query:

DELETE *
FROM YourTable
WHERE Level <> 1

Open in new window


and if you need a macro to run it, use the RunQuery action.
0
 
mbizupCommented:
<<  use the RunQuery action. >>

Sorry - the action is actually "OpenQuery".  (just use the default options with that action)
0
 
route217Author Commented:
Hi mbizup

sorry new to access totaly lost. ...step by step please...

I have
sub tidyup()

Docmd.setwarnings false

Docmd.setwarnings true
end sub

s far.
0
 
mbizupCommented:
Just as an aside.., comparing macros and vba... while a lot is possible with macros, including this particular task, VBA in the long run is the way to go.

VBA is easier to maintain and develop, gives you (the developer) more control, and provides you with meaningful error messages when things go wrong.
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.