VB6 + Access2000 Question. Please Help

[Bongo]

Greetings,

please forgive by placing this Q into VB General and DB.
Maybe someone in VB General came accross this problem.

Is it possible to attach to an Access 2000 table a stored procedure or somthing else to log actions on a table.

eg.
an appliaction (VB) insert into the table 'Employee' a new record.

Now in a log table the action INSERT TABLE and TIMESTAMP or even the whole sql statement and timestring are now stored.

If it is possible and you know how could you please attach an example code?

Many thanks in advance
Manfred

[John McKernan]

Certainly is possible, and a couple of option come to mind:

1. The simplest: insert a TIMESTAMP field into your 'Employee' table and record the time the record was created in this field.
2. Create another table inside your mdb file to act as a log file. This table could be linked to the Employee Table via a Key value.
3. Create a separate mdb to hold the log file.

How to do it, depends alot on the data access method you are currently using:

1. Are you using databound controls?
2. Is your data connection ADO, DAO, etc
3. Where are you getting the information to create the new Employee record?

[Bongo]

John,

the thing with the employee was an example. Actually we talk about 20 Tables (partly related to each other, partly w/o relation)

I like to have a log table in my mdb which logs all record changes.

I send from a VB 6 app via

Set m_dbDatabase = DBEngine.Workspaces(0).OpenDatabase(App.Path & mcDB_Path, False, False)

m_dbDatabase.Execute(strsql)

the statements to the Access DB

so your option 2 would be my prefered choice.

Thanks in advance
Manfred

[John McKernan]

Manfred,

I'm not sure I agree with using either methods 2 or 3. The problem being:

It seems that you are trying to create an 'audit trial' of record changes throughout your data. I am assuming each of these tables has a different structure. Therefore, creating a separate table to store the changes would be problematic, in determining number and types of fields in which data could be changed.

Linking the log file to the data record would also be problematic in that you are dealing with a number of different tables, you would have to store table name and index - not a problem. Retrieving the record would be, since it would require a static and rather long Select Case structure to determine the proper table.

I would suggest modifying method 1. (Using the 'Employee' example):

Insert fields into each of your tables for 'Timestamp', 'Action' (ie: Record Created, Record Modified, etc as desired), 'Current' (to indicate the most recent record for each employee) and maybe for 'User Name' to record who made the changes. Then, when the Employee is created, a record containing all his info and the above fields is created.

When the data for that employee is changed, first edit the first record so that 'Current' = False. Then create a new record out of the original data and any changes and 'Current' = True. This way you will have a 'snapshot' of each record as it existed before being altered. When working with current data for each employee, you could of course, filter your records on 'Current = True'

Hope this makes sense.

[csar]

In my ideal , I think Bongo try to make MS Access act as expensive RDBMS with feature log file for any changed data.

we may code in VB app. to do that but I think it's trouble! Because , we must have code to insert into log file every point that changing happen..