Delete All Records in a Table

[Guest]

In my database the user has the ability to pull a report based on one
employee # however if they need to pull mulitple employee #'s they have to
keep running the report. I have created a table called tblmtblempnum and a
form for the user to enter as many employee #'s as they need to run the
report. My question is I need a way to delete all the old records out of the
table before the new ones are added. I would like for it to delete them when
you open the input form or have a command button that will do this either way
is fine with me adn the enduses.

 

[Guest]

In the Open event of your form:
CurrentDB.Execute "DELETE * FROM tblmtblempnum"

 

[Douglas J Steele]

CurrentDb.Execute "DELETE FROM tblmtblempnum", dbFailOnError

 

[Guest]

Make a delete query that deletes * from that table:

DELETE tblmtblempnum.* FROM tblmtblempnum;

In Form_Open, run the query:

DoCmd.OpenQuery "ClearMyTable"

 

[PC Datasheet]

This is a bad idea! You will find that your database will quickly bloat in
size as you use the temp table. The better way to do this is to add a Yes/No
field called Selected to your table. Create a form where the users can check
the employee records they want in the report. After the report is run, you
then use an update query to reset the Selected field back to No.

 

[John Spencer (MVP)]

And what happens if two users are checking/unchecking records at the same time?

It is not necessarily a bad idea to use a temp table. You can compact the
database on close.
You can set up the temp table in the each user's front end if you are using a
split database.

Or you can create a temporary database and a temporary table in the temp
database. When you exit the app you can kill the temp database.

 

[Douglas J Steele]

Personally, I think using a temporary database to hold the temporary
table(s) is preferable. Tony Toews has a good example at
http://www.granite.ab.ca/access/temptables.htm

 

[John Spencer (MVP)]

Thanks Douglas. I should have referenced Tony's example myself.