PCFormat Forum Archive

I need to be able to capture the user's details (name) every time they edit a record in a table. I would also like the date and time.

We now have a few users that are deleting information in fields. We need to be able to track who the idiot is and deal with them. The data is stored in SQL tables. Is there a way that I can build it into SQL or do I have to do it through a Query in Access?

This is rather urgent as we need to sort this out asap. Most users are using Windows authentication to log in.

Tribble wrote:I need to be able to capture the user's details (name) every time they edit a record in a table. I would also like the date and time.

We now have a few users that are deleting information in fields. We need to be able to track who the idiot is and deal with them. The data is stored in SQL tables. Is there a way that I can build it into SQL or do I have to do it through a Query in Access?

This is rather urgent as we need to sort this out asap. Most users are using Windows authentication to log in.

You could turn transaction loggin on but seing as you are using Acces and not SQL Server I doubt it is possible. I would say work it into the queries.

I will look into that. Working on the forms rather as queries seem more complex.

Tribble wrote:I will look into that. Working on the forms rather as queries seem more complex.

depends on the queries.

I have queries based on queries that are based on queries

Tribble wrote:I have queries based on queries that are based on queries

Then consolidate them. I know it is a big job but the simpler the better.

just to toss my dodgy penny into the pot... when users are deleting critical data, this says "design flaw" - time to relook the input approach.

User Level Security - i'm no access fundi, but you should be able to prevent unauthorised deletion ...

after a brief scan, it would appear that user logging and user object references are only available once ULS is installed, not before then.

The problem is the user we suspect of deleting data is the one who is supposed to have full access rights. She is not deleting complete records - just clearing out fields. She does leave some data but many of the fields are wiped clean. Not sure how she is doing this accidentally as it involves going into each field and pressing delete or space. She then says that as the record was added last year - she could really not have deleted the data. I need to track who was the last to edit a record and the date and time of that edit.

OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it

• 1. Creates a 'back-up of the deleted data
  2. Captures the users' info etc?

As I said - question

doo_much wrote:OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it

• 1. Creates a 'back-up of the deleted data
  2. Captures the users' info etc?

As I said - question

Problem is that when you use a trigger you don't know who the user was that made the change to the record. Also I am not 100% sure if you can add triggers to tables in Access.

RuadRauFlessa wrote:

doo_much wrote:OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it

• 1. Creates a 'back-up of the deleted data
  2. Captures the users' info etc?

As I said - question

Problem is that when you use a trigger you don't know who the user was that made the change to the record. Also I am not 100% sure if you can add triggers to tables in Access.

Tribble wrote:The data is stored in SQL tables.

Oooh thank you - I will have a look at that.

doo_much wrote:It seems this CAN be done!

SQL is a language...

SQL Server is a platform

MS Access, (with its own flavour of SQL), does provide some of this functionality, but only if USL is installed - without it, there is no reference to the editing user, (although i haven't dug to deep)

from my very sketchy knowledge, access does allow triggers, and runs a rollback instance for each session - perhaps have a trigger trap, inspect and rollback the essential fields...

In that case go and create 4 triggers for each table in your SQL DB

I coded something similar in SQL Server a few years back in a project. Basically I used the database itself to do user validation so I could track who was doing what, and then mirrored every user action into a log table. IDK if it will translate well to Access since I don't know what its limitations are compared to Server, but if you want I can email you the code and you can see if there's anything in there you could use. I could also probably talk you through it on chat or something, if you want.

Mina.

rustypup wrote:

doo_much wrote:It seems this CAN be done!

SQL is a language...

SQL Server is a platform

Ahh Thanks!

and thank goodness for my n00bness disclaimer!

ROTFL - never.

It is going to be easier to write a function in the forms that captures the date and time and user's name. Besides - my SQL programming knowledge is non existent. I am still struggling with VBA as it is

mina.magpie wrote:I used the database itself to do user validation so I could track who was doing what

SQL is already doing all of this... those monster logs are there for a reason, (SQL actually uses these logs for many rollback actions...)

access is server's 'special' cousin, twice removed...

i still say the design is flawed. a proper, coded, front-end is the preferred approach to data capture. no user should be entering data directly into a production database using forms. ever. it's just begging for precisely this sort of issue...

now all we need to hear is "there are no backups" and i run screaming for the hills...

@Tribble - have you considered a coded front-end?

Tribble wrote:It is going to be easier to write a function in the forms that captures the date and time and user's name. Besides - my SQL programming knowledge is non existent. I am still struggling with VBA as it is

Cool beans. As long as you can be sure your user-base only has access to the db via your forms application, you could easily just pipe it all into a text file. If you do end up requiring something more robust though, just shout.

Mina.

it would appear access!=triggers, so there goes that option... time to bite the bullet

rustypup, Yeah but she only uses Access as a front end with the forms it provides. They use a SQL Server 2005 SP1 for the back end where the data is actually maintained.

rustypup, I really have no idea what you mean by a coded front end. I have a users table which controls access and rights to the database. The SQL server also controls who is allowed to access the database and what rights they have. They use their Window's Authentication. I have three people who have to be allowed to add, edit, delete. I am the only one that can create. There are others that have viewing rights only.

The problem is - one of the three people is causing the problems we have. I need to work out which one so that we can prevent further problems.

We do daily backups which are kept for 14 days. We also do monthly backups and those are kept for 3 months. All are offsite.

no user should be entering data directly into a production database using forms. ever. i

What do you mean by this? How can we update the certifications of the brokers if we cannot edit their data? How can we add new employees - check their supervision requirements if we cannot add the required information? You have me very confused puppy.

Tribble wrote:rustypup, I really have no idea what you mean by a coded front end. I have a users table which controls access and rights to the database. The SQL server also controls who is allowed to access the database and what rights they have. They use their Window's Authentication. I have three people who have to be allowed to add, edit, delete. I am the only one that can create. There are others that have viewing rights only.

The problem is - one of the three people is causing the problems we have. I need to work out which one so that we can prevent further problems.

We do daily backups which are kept for 14 days. We also do monthly backups and those are kept for 3 months. All are offsite.

no user should be entering data directly into a production database using forms. ever. i

What do you mean by this? How can we update the certifications of the brokers if we cannot edit their data? How can we add new employees - check their supervision requirements if we cannot add the required information? You have me very confused puppy.

What he means is that you should have an application which does data integrity checks. Although SQL Server already caters for foreign indexes you should still check weather the data entered is acceptable. Say you have a description field and someone tries to update the said field you have to check that the new data entered for the field is not blank/zero length/spaces

I have that already - it doesn't help if they accidentally replace data with a space. But we cannot refer to the old data in order to control the new. One chap moves from one Province to the other, to a different region or branch. We need to be able to capture the data - no matter whether it is completely different to what was there before or not.

How do you prevent spaces from being added?