REPOST: Saving Deleted Entries

  • Thread starter Thread starter John C.
  • Start date Start date
J

John C.

I have a database which allows users to delete entries
they have previously made.

I want to transfer these records from the current DB to
another DB to archive/preserve these entries and offer an
audit trail of who did what when.

I'll trap this in the Form_AfterDelConfirm routine.
The deleted DB will have the same fields, plus two new
ones, to record userid and machine id of who deleted the
record.

The DB will be located in the same folder/directory as the
originating DB.

(1) I have not referred another DB outside of the current
one in use. How do I get there?

(2) Is there a global command to transfer all the fields
of the deleted record at once?
 
Attach the table(s) you need to use from the auditing database:
File | Get External | Link
This lets you treat them as if they are local tables.

Alternatively, if you create an Append query (Append on Query menu in Query
Design), Access gives you the option to specify the database that contains
the target table. You can then switch the query to SQL View (View menu) and
copy the example Access created there, and Execute it as a string in your
code.

The more significant problem is that you cannot get information about the
records being deleted in the AfterDelConfirm event. You can in the form's
Delete event, but you do not know if the deletion will proceed. Further,
users can select multiple rows for deletion (in a datasheet or continuous
form), and so there may be multiple rows that need to be archived.

For a discussion of these issues and a suggested solution, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
 
(1) I have not referred another DB outside of the current
one in use. How do I get there?
Click to expand...

The simplest way IMO would be to use File... Get External Data... Link
to link to the tables. In fact, you should really consider using the
Database Splitter Wizard to have *all* your tables in a separate
backend database, if you have a multiuser system!
(2) Is there a global command to transfer all the fields
of the deleted record at once?
Click to expand...

An Append query; just select all the fields or use the * field option.
 
Sorry I missed this repost earlier

The connection string:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLE.4.0;Data Source=see
below;"

after the = add the path statement to the new database
you want to connect to. I.E.
C:\My Databases\DeletedRecords.mdb

Note the double quotes before Provider and after the path
statement

After the connection you can execute a valid SQL
statement - in your case APPEND - with the following;

cnn.Execute (yourSQLstatement)

Let me know if this helps.
 
Back
Top