I have a SQL 2008 backend running with an Access frontend. I have them connect via linked tables and linked views. The form in question holds a student's previous classes from their transcript. To add to this specific form I have an instead of insert trigger written and the same for the update.

However, I need to have a way to delete a specific record from this form. My problem is that the student ID can't be used in the WHERE clause due to only needing to delete one record based on it not all the student's classes. Also, they can have duplicate course names because of having retaken them. I would just delete it based on the grade but I have students that have failed the same course twice in the same year (it is an online school so students work at their own pace. The class can literally be taken in the same month and both records look exactly the same).

Now is there any way to get the record ID field from somewhere? Like would something along the lines of this work?

I wasn't sure if the deleted ID would be the same as the transcript ID. I would normally do this on the replicated server and just find out but currently it is down due to one of my coworkers changing fields in a table. My supervisor wants this taken care of before the replication for some reason.

Thanks

Got the replication server running. This ended up working after all.

You may want to take a second look at how you are doing this.

It sounds like you have written triggers to accomplish inserts, updates and deletes.

Triggers, in general, should rarely be used. They can seem invaluable in certain circumstances but they are like the old GOTO statement, prone to misuse, as it sounds like in this case.

I say this because I have written hundreds of systems with Access as a front end and SQL Server as a back end. Each of those systems contains dozens, if not hundreds of tables, and each of those systems contains dozens, if not hundreds of forms, and I have never used a trigger for basic data manipulation. NEVER!!!

Yes, I have written triggers to do some pretty unusual stuff--usually some testing or utility functionality--but for everyday inserting, updating and deleting, absolutely not.

Am I missing something here?

Well the main reason I use the triggers is because I have views in the backend. I converted to SQL from an Access backend. I had never seen SQL and barely ever used Access yet my work thought that I could "figure it out." There was no one there who had used SQL so most of my coding is based on Google searches. I have gone through and cleaned up code that was hashed together from when I started though.

I began to use views because they seemed to make Access run much faster than having queries run in the front end. There are usually about 25-30 people logged into a network drive with the front end residing on it and the views really sped it up.

Now, I use the triggers because I would get the message saying cannot insert, update, or delete because it affects multiple base tables. What I would really like to do is actually restructure the tables because that would alleviate some of the problems that happen and need the triggers. However, I am only part time and I have deadlines (start of school for example) that all these things needed to be finished by. So, being the only DBA made it rather difficult to get all these ideas through. (My first post makes mention of coworkers. They maintained the Access database before I took over but my supervisor made me give them permissions to SQL. This has helped but also caused a lot of headaches when they make changes.)

I hope that helps to explain why I am using triggers. It's really for the multiple base table situation, but if there are better ways that this can be done I would like to hear it. If they are difficult I unfortunately don't have the time to implement them. I'm supposed to train some people on how the database is built because I am leaving to start my internship in December.

If you are working with an existing product and no one wants to spend the time to money to do it right, then what you are doing is okay--bottom line, you have to get it done.

But in terms of doing it correctly, there are many ways to skin this cat, of deleting from multiple tables. You can do it from Access with programming on the OnDelete event. You can do it from SQL by putting the entire delete logic in a stored procedure and simply pass the stored procedure the unique ID which it can then use to tie to all of the dependent tables.

Good luck.

Thanks for the heads up. I will keep that in mind when I go to work on other projects, but for here it is a "let's get this done."

If they ever want it done "right" I will look to those methods and implement them.

Thanks again.