You are here:

Using MS Access/deleting records in Aceess DB

Advertisement


Question
QUESTION: Dear Scottgem,


I want to create a tab (macro or VB code) that deletes all records from database that are older than 7 days.

There is a column that contains date.

I know how to filter records using sql query , but not how to delete them.

Please guide.

ANSWER: You wouldn't do this with a tab. You would do it with a query. do the same thing you would do to create a SELECT query and test it to see that it returns the correct records, then just turn it into a DELETE query. The SQL would look like this:

DELETE * FROM table WHERE datefield < DateAdd("d",-7,Date());

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: Thanks for the answer. Can this query be run from excel?  Because I have stored db at shared drive and using excel as front end.
Can I simply use excel to delete old records. Plz guide.

Always thankful for your help.

ANSWER: Why would you use Excel as a front end? Excel is a spreadsheet. If you are managing data you do it with a database.

You should be able to use Office Automation to run an Access query from Excel, but why? You can also use Microsoft Query from within Excel.

It also begs the question of why delete records? Generally you don't delete records, but mark them as inactive.

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: Hey Scott,

Thanks for your kind reply. I will answer all your doubts one by one.

1. I am using excel as front end that is used by almost 15 ppl to work on daily file allocated to them. MS access is backend to be used for storing records at end of the day.
Workforce is comfortable with excel and not access.


2. Need to delete records is there so that after one month all the records that are not needed can be deleted and this will keep the size of database quite manageable.


So precisely the point where I request for your guidance is :

in Excel , I ask user to pick two dates , start date and end date ,and once I press tab (With macro ) it deletes the records between those two dates.

Request you , please help!!

Thanks

Answer
1. "Workforce is comfortable with Excel, not Access" That should not be an issue. The developer should create an interface that the users are comfortable with. Users don't need to know ANYTHING about Access to use an Access app. The fact of the matter is by using Excel you are giving users more direct access to the table data. Excel is not meant for this. Even if using Excel Forms you don't have the control over data validation you do with Access. Using Excel for this is using a screwdriver to hammer in a nail, its the wrong tool for the job.

2. I have no clue how many records you are dealing with, nor what these records are for. So I can't say whether deleting them is a good process or not. But I gave you the SQL statement that you need to use. Your problem is not within Access but within Excel. You need to run code within Excel to run a query against an Access table. And I don't know Excel VBA well enough to suggest the code. Sorry.

Again, I really think you need to rethink using Excel. To do this in Access is apple pie simple. You create a form with 2 textbox controls named txtStart and txtEnd. Add a button to the form with code like this:

CurrentDB.Execute "DELETE * FROM table WHERE datefield BETWEEN #" Me.txtStart "# AND #" Me.txtEnd & "#;", dbFailOnError

That's all you need. You can have a form with a button to open that form. The user doesn't need to know anything about Access.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.