tags:

views:

46

answers:

2

I have an access database with a table. This table keeps getting records deleted, and I can't seem to find where the delete command is coming from. Can I simply protect the records in some way so that NO ONE can delete them?

A: 

You could introduce key constraints, but I really believe that you should know what's actually going on there first.

thelost
How would I go about putting in key constraints?
Marc
e.g. referencing these rows from within other table by introducing a foreign key, but as I said this is not the right solution but understanding what's going on there and acting accordingly.
thelost
I understand, but this isn't SQL Server, so I can't use Profiler. How can I track down the delete command source?
Marc
I guess you should start by checking all queries / procedures and make sure that none causes you trouble. If you have an app that uses this DB indirectly, it's a bit more complex: you have to analyze that as well.
thelost
Well, I've found that some other Access .mdb is linking to this table, deleting the record, and trying to re-add it with additional information. I'm modifying the vba code which does the delete-insert and changing it to update. We'll see if that makes a difference.
Marc
You should tell whoever is responsible for that other MDB to stop doing that. That's by far the simplest solution.
David-W-Fenton
A: 

Delete triggers

Vidar Nordnes
In Access???? How is that done?
Marc
When you are in design view of your table, go to design and find "Create Data Macros". "Before Delete" is probably the one you'd pick
Vidar Nordnes
@Vidar Nordnes Not in Access it isn't. :)
Remou
@Remou - Thanks, I thought I was going crazy!
Marc
Yes you got that in Access. At least 2010, and I would guess 2007 too, since they use the same format
Vidar Nordnes
Access 2010 adds table-level data macros that can work as triggers, but that applies only to A2010 ACCDB files.
David-W-Fenton
No, it was not in A2007. This is one of the REALLY BIG new features of A2010, and while it's incredibly useful with ACCDB data stores, it was really introduced in order to support Access web apps running on Sharepoint with Access Services. This is a huge new area that opens up a whole host of new opportunities for creating better Access apps.
David-W-Fenton
I'm working in 2007, but the database is .mdb from 2003. Maybe that's why I'm not seeing this option?
Marc
The features does not exist in A2007 -- it was introduced in A2010. Secondly, it applies to the ACCDB format, not to MDB, so even if it had existed in A2007 (which it didn't), it wouldn't be available in an MDB.
David-W-Fenton
The question doesn't say anything about which version of Access he's using, nor which format :-)
Vidar Nordnes
While it's true the version isn't specified, if you're offering a solution that is available only in the very newest version of Access, you should say that in your answer. For example, had your anwswer read "If you're using A2010, you could use Delete Triggers.", then there'd be nothing wrong with it. As it stands, it's just as incomplete as the original question.
David-W-Fenton