[RESOLVED] [Access SQL] Best way to Save 'Edited' Multi Table records. (Many-to-many)

I have started to run into an issue where I have tables with multipule Many to Many relationships that I need to be able to allow users to edit.

How do you manage saving the changes to tables like this?

A user could edit the record and add/remove Individual Sales Items from the Many to Many table.

Is the best way just to go through each record in the (Many to Many) table that matches the Main SalesRecordID, and if it isn't in the Listview on the form, mark it as deleted (Or delete it), and add any new items?

Is there any SQL specific tricks/techniques?

Quote:

---

Originally Posted by rack

and if it isn't in the Listview on the form, mark it as deleted (Or delete it), and add any new items?

---

If it was me, I would prefer to actively track what they delete or delete then refresh as they do it. I 120% do not like the idea of removing records from a database based off what is no longer displayed on a ListView.

Pitfalls:
1. You indexed your list wrong and thus never displayed one of the records but since not there you deleted it.
2. You have some ListView limitation that prevented X number of records being displayed, thus they get deleted.
3. Program screws up, you delete records.
4. You write your query wrong and thus delete records not in a list but you fail to limit to other necessary criteria.

That is my quick take on it, I am running on caffeine and no sleep so sorry if I post is completely off base.

I would probably create a Stored Procedure flagging these records to be deleted, then when the session is over delete the flagged ones or something.

Thank you BillBoBaggins,

You make a great point. There could be any number of situations that could arrise that could make the listview show the incorrect items, and thus as you stated, delete records only on the basis of them not being present in the listview.

Rephrasing what you said to make sure I understand:
It sounds like your saying on each "remove/delete" the user does, flag that record in the database table with an Editing flag. (Either update or delete Maybe U or D).

Then, when they are all done deleting specific items from the listview, and adding new sales items to that list view, I would go through the database and review any of the items that are U/D.

Update any that need updating (Based off the listview) and delete any that have a (D).

I would then also add any of the new sales items that were in the listview (Have a column in the listview called New, or perhaps just store -1 in the RecordID Column).


To your comment about stored procedures:
I didn't think one could do Stored Procedures in Microsoft Access?

Quote:

---

It sounds like your saying on each "remove/delete" the user does, flag that record in the database table with an Editing flag. (Either update or delete Maybe U or D).

---

Yep. Using a Stored Procedure.

Quote:

---

Then, when they are all done deleting specific items from the listview, and adding new sales items to that list view, I would go through the database and review any of the items that are U/D.

---

Yep. You could display them to the user and show other details for them to confirm the deletion. If you want.

Quote:

---

I would then also add any of the new sales items that were in the listview (Have a column in the listview called New, or perhaps just store -1 in the RecordID Column).

---

For Access, I would probably (depending on the number of records) commit the NEW records and refresh the display so you can get an ID.

Quote:

---

I didn't think one could do Stored Procedures in Microsoft Access?

---

http://www.stardeveloper.com/article...1050101&page=1

I guess I thought a stored procedure was something more....

Ok, so I simply have the query saved rather than doing an insert statement with DAO/ADO or docmd.

Question about the stored procedure:

If you have a split frontend/backend database with multipule users. Would you put this Stored Procedure in the backend file, or the users frontend file?

Are there any issues with multipule users accessing the same Stored Procedures in a backend file (With access)?

Does this mean that the amount of data that is sent accross the network would drop ?

Quote:

---

Ok, so I simply have the query saved rather than doing an insert statement with DAO/ADO or docmd.

---

Yep. That would merely be my preference though, if you prefer to store SQL in your code go for it in an Acces environment. If this was compiled code, I would definitely use a Sproc.

Quote:

---

If you have a split frontend/backend database with multipule users. Would you put this Stored Procedure in the backend file, or the users frontend file?

---

That depends. How are you handling data communication now? Linked tables or ADO calls to your backend? I don't really have a definitive answer here, I would go with what your familiar with to start then consider the drawbacks, or the easiest way for you to trap and deal with issues/fail codes.

Quote:

---

Are there any issues with multipule users accessing the same Stored Procedures in a backend file (With access)?

---

I can't give you a 100% guarantee. But my best assumption is that there would be no drawbacks to multiple clients triggering the same Sproc. In theory I would think they would just run in the order they get issued.

Quote:

---

Does this mean that the amount of data that is sent accross the network would drop ?

---

I can't think of where this would drop or increase network traffic. Thoughts?

Quote:

---

Originally Posted by BillBoeBaggins

That depends. How are you handling data communication now? Linked tables or ADO calls to your backend? I don't really have a definitive answer here, I would go with what your familiar with to start then consider the drawbacks, or the easiest way for you to trap and deal with issues/fail codes.

---

Linked Tables to the backend database.

Is it better to do direct ADO calls, without linked tables?

I know that with linked tables I read I can't do transactions, which is frustrating. However, if I did it all with ADO, the reports would have to be sent a SQL Statement instead of being bound to a Saved Query.

Quote:

---

Originally Posted by BillBoeBaggins

I can't think of where this would drop or increase network traffic. Thoughts?

---

The reason I asked this is that I've read a few places that say that Access is not a true database server structure, so when a call for data is placed, generally a large amount of each table is sent accross the network, before joins, HAVING, and the WHERE condition is examined. (I have not confirmed this, just what I was reading)

I was hoping that if it was a Stored SQL Statement in a Query in the Backend, you'd be sending it parameters, and it would hopefully only be returning to you locally the rows/columns you requested.

Not sure.



Thanks again for your insight on this, always trying to improve and learn =)

Sorry for the late reply.

Quote:

---

Is it better to do direct ADO calls, without linked tables?

---

I am a control freak so I would do pretty much everything in ADO. I would use linked tables for showing limited quick Select/results queries.

Quote:

---

I was hoping that if it was a Stored SQL Statement in a Query in the Backend, you'd be sending it parameters, and it would hopefully only be returning to you locally the rows/columns you requested.

---

I have no proof to support the incoming statement. I don't believe there is anyway to bypass this problem with MS Access. Other servers are independent apps so they can do their own processing, but issuing a query to a remote back end Db in Access from a local copy still requires the client to do all the thinking and decision making.

Thank you for all your help,

This provides me with the information and direction I needed to continue.