delete records query / macro?

  • Thread starter Thread starter tradmusic
  • Start date Start date
T

tradmusic

Hi,

Is it possible to set up a query / macro that automatically deletes records
held in a particular query table?

For example, I wanted to have a Yes/No field in my database that would
basically be an Include / Delete option - if ticked, then that record would
be pulled into a query table of Deleted records.

Of course, they're not deleted, they are just marked as such - so is it
possible to then create a command that will delete out, maybe weekly or
something, all records held in the Deleted Records query table?

Hope that makes sense!
Nath.
 
You could ... in the AfterUpdate event of your tick box, do something like
this:

If Me.YourCheckBox.Value Then
CurrentProject.Connection.Execute "INSERT INTO
YourDeleteTable(strTableName, varUniqueID, strUniqueIDFieldName) VALUES('" &
TableNameHere & "'," & Me!YourIDFieldHere & ",'" & YourFieldNameHere & ")"
Else
CurrentProject.Connection.Execute "DELETE * FROM YourDeleteTable WHERE
strTableName='" & TableNameHere & "' AND varUniqueID=" & e!YourIDFieldHere
End If

Note that you would need a table to store this information, and you would
need to add the three fields referenced in the first If statement ...

To delete those records, use code like this:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM YourDeleteTable",CurrentProject.Connection

Do Until rst.EOF
CurrentProject.Connection.Execute "DELETE * FROM " & rst("strTableName")
& " WHERE & " rst("strUniqueIDFieldName") & "=" & rst("varUniqueID")
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Personally, I believe your life would be easier if (a) you'd add a blnDelete
field to each table and (b) bind that control to the recordset of each form.
Then, whenever desired, you'd just issue this statement:

CurrentProject.Connection.Execute "DELETE * FROM YourTable WHERE
blnDelete=True"

You could easily build a loop that would iterate through all your tables (or
only those you select) and fire this code.
 
Hi Scott. Thanks for replying.

I don't have an AfterUpdate event happening. I am using really simple
tables and queries.

My tick box is called "include" and, when the value is False for a
particular record, it appears in my "deleted records" query table. I then
wanted to create something that would, at a set time / day, delete all
records held in this query table.

I'm not into the ASP/VBScript at this stage, I just wanted this all to
happen in Access, and then I can set up my pages seperately.

Does that make sense?

What is blnDelete and how do I build that loop? That sounds like what I'm
trying to do - basically getting it to say, "ok, that field is false, so I
can delete that record". Is this a macro or something?

Thanks for your help
nath.
 
Back
Top