Initial Design....passing out unique records. (Overview of Design) 1

[eclipse33]

How would you set up the following database? (In Access 97 or 2000)

Just a quick overview needed (Parent/Subform for a main entry screen?? Problems forseen with multi-user requests etc)

Two tables (connected by callerID)

1) tblClients (callerID, name, address, phone numbers etc)
2) tblRecords (response, Date of Call, Time of Call, comments)

Backend to be on Server....frontend of 10 workstations.

tblClient needs to serve out one record after another 1..2..3..4 to whatever workstation requests a new record.

Two workstations can't get the same record.

10 workstations will be running at the same time and each workstation will probably request a new record every 30 secs - 2 minutes.

Thanks in advance
eclipse33

 

[thornmastr]

You can certainly use a parent/child relationship on your form. However, I would suggest adding a Boolean field to the client table which will indicate if this request has already been service or is currently being serviced.. Obviously, with the form you are discussing, you do not want to pass any records that have either been or are being serviced.

I would suggest each user pull one and only one record. Use a query something along the following guideline:

Select top 1 from yourtable where serviced = 0

Remember on the before update event of the form to set this value to true if the record has been serviced.

Robert Berman

 

[eclipse33]

Quick Question...

If the tblClients has approx 50 000 records and I used

Select * from yourtable where serviced = 0

instead of what you suggested
Select top 1 from yourtable where serviced = 0

Would that significatly increase the time Access took to update the underlying table?

And therefore two workstations may get the same "new" record???

(With...10 Workstations working at the same time...requesting a new record ever 30 sec - 2 min)

 

[thornmastr]

Why would you want to pass up to 50,000 records to each user per each user call. Your poor network may burp with that much to eat. Besides the probability that at least one record of that subset is already going to be updated is now very high.

The probability of two or more users getting the same record when only one record is requested is equal to the probability that all those user request are received and honored by the server simultaneously. If the user who receives the record immediately updates the serviced flag, the probability decreases proportionately. Just remember, if the request is cancelled, to set serviced back to zero. My suggestion……try your method, test the results and do the same with mine. Pick whichever meets your needs.

Robert Berman

 

[eclipse33]

I think YOU just found my major problem...no wonder I was getting 10% duplication of records.

My recorset was 50 000 each time someone made a new record request!!! I am stupid!! Oh well...mark it up to the experience of my first multi-user Access Application.

Thanks again...I will try this solution tomorrow

As simple as SELECT TOP 1....

Thanks!! Really

 

[eclipse33]

Ok...last quetsion...really! ;-)

I want to set the serviced boolean to True in the tblClients "as soon" as the main form is opened.

Would the best way be to...

1) Update the recordset when the form is populated
("Select top 1 from yourtable where serviced = 0&quot

rst.Edit
rst![serviced] = True
rst.Update

Forms![mainForm].ckServiced.Refresh


OR

2) Have the checkbox ckServiced physically "checked" in the code for the subform?

ckCalled = True

and then in the AfterUpdate of the checkbox put the code

Me.dirty = false

to force an update of the underlying record?


PS: Should I even be using Access to accomplish this project?

Thanks again Robert...
eclipse33

 

[thornmastr]

2) Have the checkbox ckServiced physically "checked" in the code for the subform?

ckCalled = True

and then in the AfterUpdate of the checkbox put the code

Me.dirty = false

to force an update of the underlying record?

Yes. This is what I do as necessary. It works fine and very quickly, just remember, if for some reason the user cancels the process to reset the service value to false to keep the record in the queue.


PS: Should I even be using Access to accomplish this project?

If it involves a database the answer to your front end is ACCESS. If you are dealing with a huge database and many users (I tend to set user limits at 40 for Access; although I’ve seen upwards of 100 users in an all Access environment) use SQL SERVER as your back end….but Access is a wonderful front end (rips the hell out of VB 6) and can quite adequately meet most needs for FE/BE processing all by itself.

Robert Berman