Solved Append Query w/ multiple users - updating a junction table.

[AndyC88]

Hi all,

My first (and almost certainly last! ) database will soon be going 'live' where I work after many weeks (/months) of trial, error and pain...

The database tracks personnel, equipment and training data for my organisation. One of my forms allows a user to select multiple 'users' in the database, and select multiple pieces of 'equipment', and then runs a Query that adds all the selected equipment to all the selected users.

It is set up like this:

TblUsers	TblEquipment	TblEquipmentRequirements
UserID (PK)	EquipmentID (PK)	RequirementID (PK)
UserName	EquipmentName	UserID (FK)
IsSelected	IsSelected	EquipmentID (FK)

And then run the Query:

INSERT INTO TblEquipmentRequirements ( UserID, EquipmentID )
SELECT TblUsers.UserID, TblEquipment.EquipmentID
FROM TblUsers, TblEquipment
WHERE (((TblUsers.[IsSelected])=True) AND ((TblEquipment.IsSelected)=True));

The issue I've been contemplating, is if two users are using the database at the same time, and make different, independent selections, when the append Query is run, all the selections will be added to all users.

Currently, my workaround would be to limit the form to one person using at a time - but that doesn't feel like the right method. Is there a better of doing this?

Thanks for your help,

 

[Minty]

Put the selections into a local (temporary?) table and use that to drive the inserts.
Each user would only be using their local data, assuming your database will be split. (Which it should be)

 

[AndyC88]

Put the selections into a local (temporary?) table and use that to drive the inserts.
Each user would only be using their local data, assuming your database will be split. (Which it should be)

Ok, that makes sense. Yes, database will be split.
In terms of best practice, would you create the table when the form opens, (using a make table query), apply selections, and then delete table on form close?
(I’m assuming that when the database is split, a “make table” query creates the table locally and not on the back-end?)

Thanks,

 

[Minty]

If the table is going to be reused I would simply create it once and delete the contents and refill it.
Deleting and recreating a table many times can cause database bloat over time, reusing a table less so.

And yes - a make table query works in the local database.

The other option if you need to do this frequently is to create and store temporary tables in a "side database", this is created and deleted on demand by your front end and obviously removes any issues with bloat.
It is an extra level of complication if you aren't dealing with lots of data and a larger database though.

 

[AndyC88]

If the table is going to be reused I would simply create it once and delete the contents and refill it.
Deleting and recreating a table many times can cause database bloat over time, reusing a table less so.

And yes - a make table query works in the local database.

The other option if you need to do this frequently is to create and store temporary tables in a "side database", this is created and deleted on demand by your front end and obviously removes any issues with bloat.
It is an extra level of complication if you aren't dealing with lots of data and a larger database though.

OK, tracking and makes sense. We're talking in the realms of 10s and 100s in terms of order of magnitude and relatively infrequently, it sounds like a separate db is perhaps a step too far.

Yes, table is likely to be re-used. Just so I get this right...:

1. Create table (TblTempSelections) using "make table" query just the once.
2. Table (TblTempSelections) is filled with the most recent data when opening the assign equipment form (via update query)
3. TblTableSelections data is used to append the TblEquipmentRequirements
4. On form close, TblTempSelections is cleared of all data?

Instead of creating the TblTempSelections on database open (while checking for a new user) I'm guessing there's a way to check if the table exists, and if not, create it locally?

___________________________

EDIT: Like this? - And then if false execute make table query?

Public Function ifTableExists(tblName As String) As Boolean

If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

        ifTableExists = True

    End If

End Function

 

[Minty]

Yes check it exists, and if not create it. Spot on.

Tbh, Unless you expect someone to delete it in the front end once you have created it initially and distributed the new front end it should all work without the need to check.

 

[AndyC88]

Yes check it exists, and if not create it. Spot on.

Tbh, Unless you expect someone to delete it in the front end once you have created it initially and distributed the new front end it should all work without the need to check.

Ah, so I could split it, then create table on local FE then distribute? It's not 'live' yet so that's still something I could do...

And no - it will be completely locked down so they won't have any way to delete the table.

I'll do that instead!

 

[Minty]

Better plan !