Deleting from an Access database

[dwhitzig]

I've created a simple database for our intranet that shows some timely information. Problem is, when the information is expired it has to be removed. Opening the database is how I'm doing it now, but we need to have a page that would allow others to remove the dated information as necessary. How do we make database deletions through Cold Fusion? Thanks for any help!

 

[DarkMan]

I usually use a cfquery and sql for my database manipulation. For example, if you want to delete everything that is 30 days old, you can use this format...<br>
<br>
&lt;CFSET EXPIRED=DATEFORMAT(DATEADD("d",-30,NOW()))&gt;<br>
&lt;CFQUERY NAME="deleteoldinfo" DATASOURCE="mydb"&gt;<br>
Delete from MyTable<br>
Where Date &lt; '#EXPIRED#'<br>
&lt;/CFQUERY&gt;<br>
<br>
This query would work in SQL Server, the syntax for Access should be very similar.<br>
<br>
Hope this helps.<br>
<br>
Doug

 

[jwoody]

What I do is archive old info. I created a var (that can be easily change one place and will update the code) that creates a timespan. This script archives any data that was found in the search. The search queries for data more than, in this case, six weeks old.<br>
<br>
&lt;CFSET #six_weeks# = #Now()# - #CreateTimeSpan(42, 0, 0, 0)#&gt;<br>
<br>
&lt;CENTER&gt;<br>
These dates are all earlier than :&lt;BR&gt;<br>
&lt;BR&gt;<br>
<br>
&lt;CFOUTPUT&gt;<br>
&lt;b&gt;ODBC Format&lt;/b&gt;&lt;BR&gt;<br>
#six_weeks#&lt;BR&gt;<br>
&lt;BR&gt;<br>
&lt;b&gt;Formatted&lt;/b&gt;&lt;BR&gt;<br>
#DateFormat(six_weeks)#<br>
&lt;/CFOUTPUT&gt;<br>
<br>
&lt;/CENTER&gt;<br>
<br>
&lt;HR&gt;<br>
&lt;HR&gt;<br>
<br>
&lt;CFQUERY NAME="pull_history" DATASOURCE="Intranet"&gt;<br>
SELECT History_ID, Punch_Info, *<br>
FROM History<br>
WHERE Punch_Info &lt; #six_weeks#<br>
ORDER BY History_ID<br>
&lt;/CFQUERY&gt;<br>
<br>
&lt;CFIF #pull_history.recordcount# IS 0&gt;<br>
&lt;CENTER&gt;<br>
&lt;h1&gt;There is NO History to Archive&lt;/h1&gt;<br>
&lt;/CENTER&gt;<br>
&lt;CFABORT&gt;<br>
&lt;/CFIF&gt;<br>
<br>
&lt;CENTER&gt;<br>
&lt;h3&gt;The Following Records were archived&lt;/h3&gt;<br>
&lt;/CENTER&gt;<br>
<br>
&lt;TABLE BORDER=1 WIDTH=300&gt;<br>
&lt;CFOUTPUT QUERY="pull_history"&gt;<br>
&lt;TR&gt;<br>
&lt;TD WIDTH=150 ALIGN=CENTER&gt;History ID&lt;/TD&gt;<br>
&lt;TD WIDTH=150 ALIGN=CENTER&gt;Punch Date&lt;/TD&gt;<br>
&lt;/TR&gt;<br>
&lt;TR&gt;<br>
&lt;TD WIDTH=150&gt;#History_ID#&lt;/TD&gt;<br>
&lt;TD WIDTH=150&gt;#DateFormat(Punch_Info)#&lt;/TD&gt;<br>
&lt;/TR&gt;<br>
&lt;/CFOUTPUT&gt;<br>
&lt;/TABLE&gt;<br>
<br>
&lt;!--- History_ID, Process, Employee_ID, Punch_Type, Punch_Info, process_used,<br>
evaluate_used, Evaluate, Minute_Cov ---&gt;<br>
<br>
<br>
&lt;cfloop query="pull_history"&gt;<br>
<br>
&lt;CFIF #pull_history.Evaluate# IS ""&gt;<br>
<br>
&lt;cfquery name="INSERT" Datasource="Intranet"&gt;<br>
INSERT INTO history_old<br>
(History_ID, Process, Employee_ID, Punch_Type, Punch_Info,<br>
process_used, evaluate_used)<br>
<br>
VALUES<br>
<br>
(#History_ID#, #Process#, #Employee_ID#, '#Punch_Type#', '#Punch_Info#',<br>
'#process_used#', '#evaluate_used#')<br>
&lt;/cfquery&gt;<br>
<br>
&lt;CFELSE&gt;<br>
<br>
&lt;cfquery name="INSERT" Datasource="Intranet"&gt;<br>
INSERT INTO history_old<br>
(History_ID, Process, Employee_ID, Punch_Type, Punch_Info,<br>
process_used, evaluate_used, Evaluate, Minute_Cov)<br>
<br>
VALUES<br>
<br>
(#History_ID#, #Process#, #Employee_ID#, '#Punch_Type#', '#Punch_Info#',<br>
'#process_used#', '#evaluate_used#', #Evaluate#, #Minute_Cov#)<br>
&lt;/cfquery&gt;<br>
<br>
&lt;/CFIF&gt;<br>
<br>
&lt;cfquery name="DELETE" datasource="Intranet"&gt;<br>
DELETE FROM History<br>
WHERE History_ID = #History_ID#<br>
&lt;/cfquery&gt;<br>
<br>
&lt;/cfloop&gt;