×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Deleting from an Access database

Deleting from an Access database

Deleting from an Access database

(OP)
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!

RE: Deleting from an Access database

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...

<CFSET EXPIRED=DATEFORMAT(DATEADD("d",-30,NOW()))>
<CFQUERY NAME="deleteoldinfo" DATASOURCE="mydb">
Delete from MyTable
Where Date < '#EXPIRED#'
</CFQUERY>

This query would work in SQL Server, the syntax for Access should be very similar.

Hope this helps.

Doug

RE: Deleting from an Access database

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.

<CFSET #six_weeks# = #Now()# - #CreateTimeSpan(42, 0, 0, 0)#>

<CENTER>
These dates are all earlier than :<BR>
<BR>

<CFOUTPUT>
<b>ODBC Format</b><BR>
#six_weeks#<BR>
<BR>
<b>Formatted</b><BR>
#DateFormat(six_weeks)#
</CFOUTPUT>

</CENTER>

<HR>
<HR>

<CFQUERY NAME="pull_history" DATASOURCE="Intranet">
SELECT History_ID, Punch_Info, *
FROM History
WHERE Punch_Info < #six_weeks#
ORDER BY History_ID
</CFQUERY>

<CFIF #pull_history.recordcount# IS 0>
<CENTER>
<h1>There is NO History to Archive</h1>
</CENTER>
<CFABORT>
</CFIF>

<CENTER>
<h3>The Following Records were archived</h3>
</CENTER>

<TABLE BORDER=1 WIDTH=300>
<CFOUTPUT QUERY="pull_history">
<TR>
<TD WIDTH=150 ALIGN=CENTER>History ID</TD>
<TD WIDTH=150 ALIGN=CENTER>Punch Date</TD>
</TR>
<TR>
<TD WIDTH=150>#History_ID#</TD>
<TD WIDTH=150>#DateFormat(Punch_Info)#</TD>
</TR>
</CFOUTPUT>
</TABLE>

<!--- History_ID, Process, Employee_ID, Punch_Type, Punch_Info, process_used,
evaluate_used, Evaluate, Minute_Cov --->


<cfloop query="pull_history">

<CFIF #pull_history.Evaluate# IS "">

<cfquery name="INSERT" Datasource="Intranet">
INSERT INTO history_old
(History_ID, Process, Employee_ID, Punch_Type, Punch_Info,
process_used, evaluate_used)

VALUES

(#History_ID#, #Process#, #Employee_ID#, '#Punch_Type#', '#Punch_Info#',
'#process_used#', '#evaluate_used#')
</cfquery>

<CFELSE>

<cfquery name="INSERT" Datasource="Intranet">
INSERT INTO history_old
(History_ID, Process, Employee_ID, Punch_Type, Punch_Info,
process_used, evaluate_used, Evaluate, Minute_Cov)

VALUES

(#History_ID#, #Process#, #Employee_ID#, '#Punch_Type#', '#Punch_Info#',
'#process_used#', '#evaluate_used#', #Evaluate#, #Minute_Cov#)
</cfquery>

</CFIF>

<cfquery name="DELETE" datasource="Intranet">
DELETE FROM History
WHERE History_ID = #History_ID#
</cfquery>

</cfloop>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close