Temporary Table with Multiple users processing

[LittleNick]

Hi,
I am not sure I post this in the right forum. Here is the problem I am facing:
I have an Order form that have records group by an unique order#. Whenever I select by double clicking a record, the entire set of records with the same order# will be copy into a temporary SQL table tblprocessing, and another form open displaying the set of record from tblprocessing for adding or editing data. Once I am done editing, the new updated data will be copying back to a tblOrdercomplete. Every time I double click to select a record, it also run a sql delete to clear the temporary table tblprocessing so that I do not have old data mixed with new one. The process is working fine if I am the only user. But if multiple users start to select a record from the Order form it will say something like "can't update while other user...". I can understand why it is displaying this message, because if I select a record, it then try to delete tblprocessing then copy new record into tblprocessing, but since the data in tblprocessing is currently used by another user I can not delete them. Now my question is how can I rebuilt this in a way multiple users can process the records (update and delete) without conflict.
I am currently trying to add the userid may in the tblprocessing so that if I delete records, I only delete the ones that have the corrresponding userid without trying to delete other ones, but don't know if this would work. I just want to see if anyone has any suggestion and advice on how to tackle the problem.
Any suggestion will be greatly appreciated.

 

[randy700]

Is your database split front-end/back-end?
If not, you need to split it.
Then, make the temp tables local to the front end.


Randy

 

[Golom]

randy700's suggestion is probably the better way to go but you can also create a unique temporary table for each user rather than use the same one for all users. Do your thing with it and then DROP the table when you are done with it.

Creating and dropping tables can cause some database bloat. If you compress at regular intervals you should be able to control that.

 

[dhookom]

I read the statement "copy into a temporary SQL table tblprocessing" as suggesting LittleNick is using SQL Server as the backend.

My suggestion was going to be adding a field with the user's login or ID. I believe there are also temporary SQL Server tables that are available only to the current connection.

Duane
Hook'D on Access
MS Access MVP

 

[LittleNick]

Thanks Randy and Golom for your replies.
I believe is split into front end is Access and back end is SQL (This is a .ADP file). Unfortunately, I do not how to create a temp table locally. Every time I try it tells me "your design changes will not be saved".

 

[Andrzejek]

When your app will work the way you want, how about this:

1. Used A double-clicks on the record, you take the info from tblOrdercomplete table into the tblprocessing table

2. User B does the same to the same record.

3. User A is done - all new info goes back to the tblOrdercomplete table

4. User B does the same - which info is saved? Do we loose info saved by user A by overwriting it with info from user B?


Have fun.

---- Andy

 

[randy700]

Design a table that meets the required specs in the front end but don't populate it. Replace your make-table query with a delete query followed by an append query. The delete query deletes everything from the table. The append query copies the same data the make-table query was building. Complete your processing and delete everything from the table after updating the main table. This also solves the bloating problem caused by creating and deleting tables.

You will, however need to address the possible problem suggested by Andrzejek.


Randy

 

[dhookom]

Design a table that meets the required specs in the front end

This is a .ADP file

You can't create tables in the "front end" in an ADP.

Duane
Hook'D on Access
MS Access MVP

 

[LittleNick]

Sorry Duane, I did not notice your post after I read Randy and Golom post.
Thanks for your and Andy replies. I was planning to do what Duane suggested, inserting a new userid field into the Temporary table, but did not get to test it yet.
To answer Andy questions, when user A double click on a record, A get to do the processing if no one is. Now if right after user A, user B double click on the record the processing form will open but when user B enter data and hit Enter, user B will receive a message similar to this "Cannot update while the record is used by another user".
As Duane mentioned, I do not know a way if possible to create the temp table locally in an Access ADP file.

 

[JoeAtWork]

I question why create a temporary table at all? Why not just have one Order table and that's what you bind to? When the order is complete you just set some status field to indicate that. If the user cancels, then you just delete that order record.

However, if you are absolutely against putting the data to a permanent table until the user "completes" the order, here's a way I might do it:

1. Dynamically create an ADO recordset with the fields for your "temp" data (this will be a disconnected recordset)
2. Create one new record in it, fill the fields with whatever default values are appropriate
3. Bind the recordset to your form
4. When the user "completes" the order, create a new record in the "permanent" table and copy over the information

However, I'm not sure if an ADP allows setting forms to disconnected recordsets. I never use them because I find they limit your options (an example being the aforementioned inability to create local tables).