Deleting Records

[Vel.]

Hello,

I'm currently trying to work on generating a function that will delete
records from a table and add them to a new table for storage until a later
date. Specifically, something like this:

Move current record from tblActivity to DELActivity (i.e. cut from
tblActivity and paste to DELActivity).

Preferably, I would like to send only the table name to the function, but
I'm not sure of how to actually tell Access to cut from one table and paste
to the next. I imagine the best way to do this would be through the use of
SQL, but I can't figure out the statement.

I was thinking something like

"INSERT INTO DEL" & stTable & " [Current Record] AND DELETE FROM tbl" &
stTable & " [Current Record]"

Now, I know I'm probably way off base in which Methods I should use, but I
don't generally use SQL in this manner unless it's in the design view of a
Query; in which case I don't write it myself.

 

[John Vinson]

Hello,

I'm currently trying to work on generating a function that will delete
records from a table and add them to a new table for storage until a later
date. Specifically, something like this:

Move current record from tblActivity to DELActivity (i.e. cut from
tblActivity and paste to DELActivity).

Well, forget the concepts of "cut and paste". This is foreign to
Access' way of thinking. Instead, you'ld need an Append query to add
the new record; and then - AS A SEPARATE QUERY - a Delete query to
delete the record.

However, there may be a better way altogether. Consider putting a
Yes/No field, indexed for efficiency, named Active into the table. Set
it to Yes for active records, and to False for "stored" records;
toggle it back to reactivate. Base your Forms and Reports on queries
which select just Yes for active records (or No for archived records).
With an index on the field this will still be quick, and will involve
much less overhead than moving records back and forth from one table
to another!

Preferably, I would like to send only the table name to the function, but
I'm not sure of how to actually tell Access to cut from one table and paste
to the next. I imagine the best way to do this would be through the use of
SQL, but I can't figure out the statement.

I was thinking something like

"INSERT INTO DEL" & stTable & " [Current Record] AND DELETE FROM tbl" &
stTable & " [Current Record]"

Now, I know I'm probably way off base in which Methods I should use, but I
don't generally use SQL in this manner unless it's in the design view of a
Query; in which case I don't write it myself.

You can build the query in the design view, open the SQL window, and
copy and paste the SQL text into your VBA text string if you wish.
However as noted, you'll need two separate queries:

Dim strSQLAdd As String
Dim strSQLDel As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim ws As Workspace
Dim bInTrans As Boolean
bInTrans = False
strSQLAdd = "INSERT INTO [DEL" & stTable & "] SELECT * FROM [" _
& stTable & "] WHERE <unspecified conditions>;"
strSQLDel = "DELETE * FROM [" & stTable & "] WHERE <same conditions>;"
' for safety's sake create a Transaction to run both queries
' This will ensure that they either both run, or neither runs
Set ws = DBEngine(0) ' current workspace
Set db = CurrentDb
On Error GoTo Proc_Error
ws.BeginTrans
bInTrans = True ' for error trapping; you're now in a Transaction
Set qd = db.CreateQuerydef("", strSQLAdd) ' new nameless query
qd.Execute dbFailOnError ' run it
Set qd = db.CreateQuerydef("", strSQLDel)
qd.Execute dbFailOnError
' if no error occurs commit the transaction to finalize
ws.CommitTrans
Proc_Exit: Exit Sub
Proc_Error:
If bInTrans Then ' are we in a transaction?
ws.Rollback ' roll it back, i.e. cancel any pending changes
End If
<handle the error message reporting>
Resume Proc_Exit
End Sub


John W. Vinson[MVP]

 

[Vel.]

Thanks for the help. I consider myself well versed in Access, but I just
don't use enough SQL to figure things like this out without a great deal of
research (or some helpful advice from a NG poster).

Well, forget the concepts of "cut and paste". This is foreign to
Access' way of thinking. Instead, you'ld need an Append query to add
the new record; and then - AS A SEPARATE QUERY - a Delete query to
delete the record.

Yeah, I really didn't suspect there was a method that would 'cut and paste'.
I had tried using a couple of action queries, but I couldn't dynamically
change the table name, nor could I remember how to select all fields (*).

However, there may be a better way altogether. Consider putting a
Yes/No field, indexed for efficiency, named Active into the table. Set
it to Yes for active records, and to False for "stored" records;
toggle it back to reactivate. Base your Forms and Reports on queries
which select just Yes for active records (or No for archived records).
With an index on the field this will still be quick, and will involve
much less overhead than moving records back and forth from one table
to another!

I considered doing it this way, but chose not to for two primary reasons.
First, I am storing deleted records in an entirely different backend in order
to keep the size of the main database from getting out of control with
uneeded records. Second, I've already developed MANY reports, forms, and
queries which would all need to be altered to not include the 'inactive'
records. I originally intended to just let users with the appropriate
clearance the ability to delete records, however I've since decided that's a
bad idea, and would like to store deleted records temporarily for the
purposes of an 'audit trail'.

Anyway, thanks again for your help. I think I need to spend more time
expoloring the mysteries of SQL