Dealing with marked records and multiple users (1 Viewer)

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
In the past, my db has tended only to be used by one person at a time.

So when a user wants to mark a group of contact records to be dealt with for some reason, I have just had a "fldMark" checkbox field on each record that the user can check either individually on the forms or in groups by queries; then do whatever operation on "qryMarked" and then uncheck them all.

I am now getting to the stage where I am likely to have multiple users wanting to work with different batches of marked records - so all trying to use the same checkbox is obviously a non-starter.

I am planning to address this by creating a "tblMarked" with fldContactID and fldUserID as a dual key (to avoid any risk of duplications).

Then when the user selects ("marks") the contact by whatever means, this will be recorded by writing new records to tblMarked, which will then be stored, regardless of what other users do, until they are deleted when the user "unmarks" the records.

I can think of a number of other ways of going about this, but all involve too many tables or checkboxes, so I think this will be the best method.

If that all makes sense, am I on the right lines?

Many thanks,

George
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
Could you not just add the userid to the table and then return all checked for that particular userid. Reset the userid to Null when the checkbox is cleared.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:26
Joined
May 21, 2018
Messages
8,463
Assuming each user has there own front end, tblMarked could be a local table on the front end. You will be doing a lot of adding and deleting which could bloat your database. It is more complicated to compact and repair a backend then just compact on close the front end. No risk of compacting a front end.
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
Could you not just add the userid to the table and then return all checked for that particular userid. Reset the userid to Null when the checkbox is cleared.?

Add a "UserId" field to the Contacts table, you mean?

The problem with that is that two users might want to mark the same person at the same time for different reasons.

(Eg: one user might mark a small batch of users for personal contact reasons that she might want to keep marked for a few days, whilst another might, in the meantime, want to mark a large batch (say, all members of a club in a certain region) send one email and then unmark them minutes later. The two groups may well involve the same people.

Or would you, in that case, add more then one UserID to the same field, separated by a delimiter?
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
Assuming each user has there own front end, tblMarked could be a local table on the front end. You will be doing a lot of adding and deleting which could bloat your database. It is more complicated to compact and repair a backend then just compact on close the front end. No risk of compacting a front end.

Ooo. Hadn't thought of that option. Interesting.

Initial reaction is that I can foresee a case or two where one user might want to let another user see their marked set, which might be more complicated with a local marked table?

No clue about how many actions, how quickly are needed to cause "bloat". We're talking about a relatively small db. Maybe 10k contacts in total?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
Well in that case I would be thinking of populating a table with the ID of the record to check and the userid if in the back end or use MajP's observation and just have a table in the front end with the ID of the record to check as that would be local and unique to each user.?

This is all theory to me, mind you, as I have not had to work that way yet.
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
Well in that case I would be thinking of populating a table with the ID of the record to check and the userid if in the back end

I think that's the approach that I was trying to portray in the OP. MajP has suggested that constant adding and deleting records in the BackEnd could be problematical, though (although, it's a pretty small db).


This is all theory to me, mind you, as I have not had to work that way yet.

It's all theory to you??? It's incomprehensible arcane lore to me!!! :confused:
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:26
Joined
May 21, 2018
Messages
8,463
No clue about how many actions, how quickly are needed to cause "bloat". We're talking about a relatively small db. Maybe 10k contacts in total?
It is not the amount of records, but the amount of inserts and deletes over time. With lots of users inserting and then deleting from tblMarked you could do that many times more than the number of records over a given period of time. MS Access doesn't free up space used by records even after they are deleted (unless you compact and repari).
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
So Gassman's suggestion of replacing chkMark with a fldUserID might be a better approach (allowing more than one userID per field by use of a delimiter?)?

As an aside, is there any risk to compacting and repairing "too often"?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
So Gassman's suggestion of replacing chkMark with a fldUserID might be a better approach (allowing more than one userID per field by use of a delimiter?)?

As an aside, is there any risk to compacting and repairing "too often"?

No, have a record for each RecordID and UserID that is being marked.
So if a record is marked by 3 different users, you would have 3 different records. Then very easy to add another users marked records if you give the users the option to see another users marked records.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:26
Joined
May 21, 2018
Messages
8,463
Code:
As an aside, is there any risk to compacting and repairing "too often"?
Things can go wrong in compact and repair. For large operations most people backup the db before compacting and repairing. The point is that it is much more complicated to compact a shared multi user backend. You need to ensure everyone is out. There are lots of different strategies to do this, but takes some work.

For what you are doing and its scale it may be no big deal. You manually do this by ensuring everyone is out and backing up your DB. If you want to automate this with lots of users, then it is more complicated.
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
Code:
As an aside, is there any risk to compacting and repairing "too often"?
For what you are doing and its scale it may be no big deal. You manually do this by ensuring everyone is out and backing up your DB. If you want to automate this with lots of users, then it is more complicated.

6 users, all in the same building, so it shouldn't be a problem to make sure that everyone is signed off, hopefully.
 

Dreamweaver

Well-known member
Local time
Today, 16:26
Joined
Nov 28, 2005
Messages
2,466
I would go with the extra table with RecordID, userID And maybe Reason both Id's Would be keys.


Once the issue was addressed the record could be deleted or datestamped with a complete but that would mean another date field.


As to compacting you should have a process in place to maintain your systems so bloting was not an issue I.E Weekly, twice weekly depending on usage.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 28, 2001
Messages
26,999
In the database where I had over 35 users (varied to as many as 45 over a span of a few years) and as many as 10-15 users active simultaneously during prime shift, I used a junction table that others could see (via program).

When someone wanted to mark a record for follow-up, the entry went into the junction table. Then when that list was about to be used, I ran a "uniqueness check" that would pop up warnings showing that one or more other users had also marked but not yet released certain records - and the users had a way to see which ones and by whom.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Feb 19, 2002
Messages
42,970
I agree with your initial thought.
One BE table with rows identified using UserID + recordID. You could date these records to help identify old stuff but I would not put this table in the FE because that will interfere with your ability to push out new FE updates.
 

George-Bowyer

Registered User.
Local time
Today, 16:26
Joined
Dec 21, 2012
Messages
177
I agree with your initial thought.
One BE table with rows identified using UserID + recordID. You could date these records to help identify old stuff but I would not put this table in the FE because that will interfere with your ability to push out new FE updates.

Thanks.

My plan was that whenever the user has completed whatever they marked the records for, they would click the button to "unmark all", which will delete the relevant records in tblMarked, so I don't think there should be a need for a date.

It will lead to a lot of creating and deleting of records in that one table, though - but if I've followed the conversation above, regular compact and repair should stop that from being a problem.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Feb 19, 2002
Messages
42,970
I understand the plan. That doesn't mean you might not need to issue a FE update when the user is in the middle of doing something. A third option is to put this particular table into a separate "template" database. That way, each time the process starts, the app can copy the master template and overlay the current working version (each user has his own working version). That eliminates the c&R issue.
 

Users who are viewing this thread

Top Bottom