Append query creating to many records (1 Viewer)

[sphynx]

A Brief background

I have a Projects database to create and track new product development, BE on the server, FE as an MDE application for users

I have the following setup within the FE

• A Form to enter project specifics, i.e customer, product etc

• Append query on save to create a separate task list related to the above project via Product ID, from a set predefined list of tasks held in a table

This works fine on a single machine but when scaled and used on more machines simultaneously I am getting duplicate task entries for one product ID (Unfortunately this happened during the first training session )

Dependent on the number of machines open and entries made at approximately the same time defines the number of repetitions in the tasks I am seeing per product ID

i.e 3 x FE running, 3 x Product records entered at approximately the same time = 3 x each task enter for each ID

I could get round this by creating a delete query to remove the duplication but would rather understand how, why and fix said problem at the source

Any help would be greatley appreciated

 

[Atomic Shrimp]

Is your application structured so that new records are entered into a temporary holding table before appending into the main data table?

If so, it's probably that everyone is using the same temporary table in the back end (so when one of them commits the data in it, they're also committing copies of someone else's records too).

Possible solutions:
- Keep the temporary holding table in the local front end (still appending to the main table in the back end as appropriate). This won't work if your users are all running copies of the same front end, directly from a shared volume, but that's not a great idea anyway.

or...

- Store the userID in the shared temporary table - make sure that each front end only ever interacts with records for its own user ID. This will only work if there is some unique ID to each session/workstation/user that you can use to tag each group of records.

 

[sphynx]

Thanks for the quick response.

Is your application structured so that new records are entered into a temporary holding table before appending into the main data table?

No

If so, it's probably that everyone is using the same temporary table in the back end.

Everyone is using the same table in the back end


Its a steep learning curve, especially when your teaching yourself!!

So if I understand you correctly

• Create Temp holding tables within the FE application (everyone will have there own stand alone FE application on there C:\ Drive)

• Once the temp FE tables are populated correctly append the data to the appropriate BE tables

 

[Atomic Shrimp]

Not necessarily... for something like an invoicing application, where the user enters potentially lots of lines, before committing them to a main table, a temporary holding table can be a great idea.

If they're just adding rows singly to the main table, maybe not - and the duplication of records might have a different cause... (not sure what though)

 

[sphynx]

The main Entry form post 1 line to a Product table & 1 line to a history Table

The append query run on saving the Entry form posts 281 lines to a task list table

So I might go temp tables in the FE based on what you have said.

Thanks for the advice