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
2010-08-04 16:51:21
How would I go about putting in key constraints?
Marc
2010-08-04 16:52:31
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
2010-08-04 16:56:49
I understand, but this isn't SQL Server, so I can't use Profiler. How can I track down the delete command source?
Marc
2010-08-04 17:07:46
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
2010-08-04 17:10:29
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
2010-08-04 20:55:05
You should tell whoever is responsible for that other MDB to stop doing that. That's by far the simplest solution.
David-W-Fenton
2010-08-05 19:57:54
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
2010-08-04 17:30:56
Yes you got that in Access. At least 2010, and I would guess 2007 too, since they use the same format
Vidar Nordnes
2010-08-04 18:12:10
Access 2010 adds table-level data macros that can work as triggers, but that applies only to A2010 ACCDB files.
David-W-Fenton
2010-08-04 18:15:55
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
2010-08-04 18:17:25
I'm working in 2007, but the database is .mdb from 2003. Maybe that's why I'm not seeing this option?
Marc
2010-08-04 18:28:12
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
2010-08-05 19:57:25
The question doesn't say anything about which version of Access he's using, nor which format :-)
Vidar Nordnes
2010-08-05 20:06:39
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
2010-08-06 18:11:19