Add/Update/Delete record in frontend vs backend

[calvinle]

Hi,

I have a hard way to accomplish the following scenario.
My database has front-end (stored form) and back-end (stored table).
I used to let the user work directly with the back-end, but we had a lot of disconnection issue within our LAN, so I decide to download the data from the back-end to the front-end local table, then once the work is done, update the data back to the back-end table.

It would be easy if it's a 1 on 1 update.
But in my case, a user can add new record to the local table, but also delete the record in the local table.

How should I do so the table in my back-end to update the data from local, and if the record on local is deleted, then delete that record in the back-end, and also, if new record has been added to the local, then add that new record to the back-end.

I have a table that has 1 record per teacher, and each teacher can add student.

tblTeacher (id, teacher_name)
tblTeacherLocal (id, teacher_name)
- To update record from tblTeacher to tblTeacherLocal is easy as it's a one to one.

tblStudent (id, teacher_id, student_name)
tblStudentLocal (id, q_id, teacher_id, student_name)
- I created the q_id to pull the id from the tblStudent so it can update back to that specific record.
- If the user add a new student to tblStudentLocal, the q_id will be blank as it's data from Local, so that data needs to push added to the tblStudent.
- If the user delete a student in tblStudentLocal, the tblStudent needs to know that missing q_id and delete that record.

Any help will be appreciated.

Thanks

 

[Mark_]

In theory, one way is to have a table called "Tbl_Edits".
Table format is
Edits_ID.....AutoNumber.... Primary Key
UpdateTbl...String............. What table was updated
EditType.....String............. Add/Edit/Delete
Update_ID..Number.......... Foreign key based off of UpdateTbl

You would then have a procedure that looks through your tbl_Edits and does what you need to each table on the back end based on the type of edit.

Warning, this will cause some issues if you have more than one person doing edits for the same person. I would ONLY use this if you are SURE end users will not be working on the same set of sub-records.

For a more elaborate version I'd make sure that when a record is edited/deleted you make sure to remove any OTHER entries in the table for that record. Delete before edit, edit before add.

In your code to update the back end, if it is a delete, simply delete the record. If it is an update, select the record first and if the record does not exist add the existing record.

When done delete the entry in your Tbl_Edits.

NOTE: When adding records you MAY run into IDs that don't match. This can happen if person one adds a record to their front end, person two adds a record to THEIR front end, and both then try to update the back end. Both may have the same unique ID so you will have to do some coding to keep the IDs straight.

In all since you are coding around a problem on your network I'd talk to your Net Admin FIRST. See if you can fix this or come up with a solution that avoids having local copies of possibly redundant data. If you absolutely cannot get your network fixed then I'd work on doing a sync between front/back end.

 

[calvinle]

Thanks.
I was thinking the same. But by adding a new column in each of the local table:
“Change_Update” which will tell if that record is “Update”, “Add”, or “Delete”.
When a user delete a record from that local table, it will mark it as “Delete”, and filter it out from the subform. It wont actually be deleted from the local table.
So when looping data from that local table to the backend, it will process accordingly.

No issue for multiple user accessing the same record as when someone download data from backend to local, it will stamp that record, so no one else can access.

Thanks

 

[Mark_]

Only down side to adding fields to the table is then your definitions do not match. You'd have to remember this if you need to change data format.

I've only ever used a secondary file to track these types of changes to avoid issues with format change. Also allowed for a generic system to handle changes rather than one for each table as formats would ALWAYS match from front end to back.

In all, still a better idea to have the network folks fix their issue so you don't have to code around it.