Docmd.RunSQL problem (1 Viewer)

[geoffcodd]

I have the following query

DELETE CBUDPresG.*
FROM CBUDPresG;

Which I would like to run in VBA as a RunSQL command, as I have quite a few of these queries and thought it would be neater, but I haven't got a clue where to start.

Any help appreciated

Thanks
Geoff

 

[GJT]

Have you tried : ?????

CurrentDb.Execute("DELETE CBUDPresG.*
FROM CBUDPresG;
")

this will do much the same thing.

 

[yippie_ky_yay]

Hi Geoff,

sounds like by your title that you're already on the right track! If you wanted to run that very SQL statement, all you would have to do is add a command button to a form, open VBA, select the command button name (should be "command0" if you didn't rename it) from the drop down list above and then make sure that the next drop down list reads "click". VBA will automatically put in your declaration statement and the "End Sub" for you. In between write:
DoCmd.RunSQL("DELETE * FROM TableName;")
(you do know what this query does though right?)

Start with that and then you'll see that the neat thing about VBA is that you'll be able to run certain SQL statements under certain circumstances. ie say you have 2 tables - if the check box is selected then delete from table1, otherwise delete from table 2:

IF checkbox = true then
stSQLstatement = "DELETE * FROM TableName;"
ELSE
stSQLstatement = "DELETE * FROM TableName2;"
End If
DoCmd.RunSQL (stSQLstatement)
Notice how the "DoCmd.RunSQL" statement is run but we don't know what the exact statement will be until the program runs (because it depends on the check box)

Hope this was what you were looking for!

-Sean