Any Suggestions Are Welcome!!

[Salvatore]

I am building a project that uses an Access 97 database as the background table...

The Managers in my call center will be inputting scores for their respective staff into the access database...The way it goes is that for one representative, their could be up to 3 Managers providing scores every month...

So I needed the database to hold just one record per representative, rather than 3 seperate records...so I created the Access form, and limited the editing process....I also inserted parameters within the query that the form is bound to, so that when the Managers open the form it will prompt them for the rep's specific ID...

I thought this way once a Manager is on the record, if another Manager cam in to add scores for the same rep, they would have to wait till the record becomes available...

Well her is the snag, I was testing it, and found that all 3 Managers could very well open the form at the same time, and their could be 3 seperate records again???? I know the probability is high , but just the chance could screw up the results in my vb project...

SO if anyone can think of any other options, please pass them along ....I need to iron this out fairly quick..

THANK YOU!

[leinad31]

What's the DB design? Relevant table design?

[Salvatore]

What exactly do you mean about the table design?

The form in the access it boiund to a query that is actually a joined query of 2 tables??

I'm sorry if I am unsure of your question

[Dave Sell]

Originally posted by Salvatore

I thought this way once a Manager is on the record, if another Manager cam in to add scores for the same rep, they would have to wait till the record becomes available...

MSAccess does not do record locking by default. Alot of multi-user setup hoops must be jumped through in order to set this up.

It may be easier to try a "JIT" addition of whatever value the manager wants to add.

If worker A has 0 points, and Manager A wants to add 2 points to his score the same time Manager B wants to add 1 point, the way you have it set up is that whichever Manager sends his UPDATE last will set the value to either 2 or 1.

But if you send the Manager's value as an addition to whatever is in the field, then it should yeild 3 in either case. I hope that made a little sense.

[leinad31]

So I needed the database to hold just one record per representative,

If the representative ID is unique then you can catch the error later on and prevent 3 records from being added. Edits will use the last saved values though.

[Dave Sell]

well I was thinking you could use VBA to capture the event on update.

Instead of simply REPLACING the old value with the new value you could ADD them.

[Pasvorto]

I have a couple similar situations. I created a "Control" table. When someone goes to a form that will update a record, it checks the control table to see if the record is in use (in a 'locked' state). If it is, I send them a message to try again later, or, if they know that it is wrong, they can overrride the message and continue on. If it is not, I update the control table ('lock' the record) and then let them do their thing. When they are done, I set the Control table back to an 'unlock' state.

[Salvatore]

Pasvorto,

I like your idea, and I think that it would fit well with what I am trying to accomplish...

Would you mind sending me an example of how you built that Control table...maybe the VBA code you used?

Anything would be useful..

Thank you.

[RobDog888]

Access 97!!!



You may run into issues down the road. "Some" bug fixes are
better in the newer versions of Access, but always still limited to
7-10 concurrent connections before issues start happening.
Access dbs always are prone to corruptions under multi-user
enviroments.

Read about it here third paragraph down.

HTH

[Pasvorto]

'check that nobody else is adding orders
rsControl.Open "Control", CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdTable
rsControl.MoveFirst
If rsControl![OrderEntry] = "X" Then
glbReply = InputBox("Orders are already being added by someone else. Do you wish to continue?", , "NO")
If UCase(Trim(glbReply)) = "NO" Then
rsControl.Close
GoTo carryon
End If
End If
rsControl.Close

[Salvatore]

Pasvorto,

How is your "Control" table set up, what I mean is I need to first build the Control table, I am just curious if there was anything special that you had within this table and how it realted to the other tables it is controlling??

Thank you very much!

[leinad31]

On the assumption that the processing is ideal... what if you dont reach the code for removig the control entry due to a hang, power failure, etc.

[leinad31]

For the editing then alter the algorithm, each manager won't update the whole record but rather just their respective fields (the score they entered).

As explained earlier, you can catch an error with multiple uniqeu, so if 2 or more were doing an Add or Insert then the 2nd and 3rd manager will encounter an error. Error handling would be to switch from an insert to an update of their respective field since the ID hence the record already exists.