docmd.runsql problem

[rincewind_wizzard]

Hi all, first post so be kind


I am looping through a series of tables deleting the data in them. That's the easy part and works fine. Now the bit I'm struggling with. Each and every table has a field called ID. If the value in this field is 1, I don't want those rows to be deleted. So, I have this:

Sub testit()

Dim d As Database, r As Recordset, mytable As String

Set d = CurrentDb
'qryMigration tables selects just the tables tat are to be emptied
Set r = d.OpenRecordset("qryMigrationTables", dbOpenDynaset)


r.MoveFirst
Do Until r.EOF

mytable = r!tablename
DoCmd.RunSQL "Delete * FROM " & mytable & "WHERE (((mytable.ID)<>1));"
r.MoveNext
Loop


r.Close
Set d = Nothing
End Sub


It works up to DoCmd.RunSQL "Delete * FROM " & mytable, but the criteria syntax after that is wrong and I can't find what to use.

Anyone?

Cheers

Paul

 

[JHB]

Try the below:

DoCmd.RunSQL "Delete * FROM " & mytable & " WHERE ID<>1;"

 

[rincewind_wizzard]

JHB spot on, works fine. Thank you

 

[JHB]

You're welcome.