delete records

S

sandrao

can records be deleter through code.

all records from 01-jan-07 through 31-dec-07.

I realize that query can achieve this but what about code?
 
R

Rob Parker

Sure. Build the SQL string, and execute it. Here's an example:

Dim db As DAO.Database
Dim strSql As String
strSql = "DELETE * FROM YourTableName WHERE YourDateField Between
#01/01/2007# AND #12/31/2007#;"
Set db = CurrentDb
db.Execute strSQL, dbFailOnError
Set db = Nothing

And here's a simpler example:

Dim strSql As String
strSql = "DELETE * FROM YourTableName WHERE YourDateField Between
#01/01/2007# AND #12/31/2007#;"
DoCmd.RunSQL strSQL

The first method is preferable, since it eliminates possible confirm
dialogs, allows you to check how many records were affected, and allows you
to trap errors. For more details, see the following page on Allen Browne's
site: http://allenbrowne.com/ser-60.html

HTH,

Rob
 
J

John W. Vinson

can records be deleter through code.

all records from 01-jan-07 through 31-dec-07.

I realize that query can achieve this but what about code?
Click to expand...

Run a query from code:

Dim strSQL As String
strSQL = "DELETE * FROM yourtable WHERE datefield BETWEEN #1/1/2007# AND
#12/31/2007#;"
CurrentDb.Execute strSQL, dbFailOnError

Be sure your database is backed up of course!!!!

Note that if you're deleting records to make your database smaller, it won't
do so until you Compact. All that will happen is that you will have
permanently and irrevokably destroyed any chance to see any of your historical
data, probably annoying some auditor no end.
 
Top