Requery a form on a work station

[bjackson]

Is it possible to requery a form on one pc from another pc connected on the network
at the moment the user has to click a refresh button to refresh the screen with up to date data.If a user on another machine makes changes is there any automation that would update all pcs that are on the network without any user action being required.

Any help much appreciated

 

[Mark Wild]

How about requerying on a timer?

 

[pono1]

Along the same lines as Mark's idea, assuming you are working with bound forms, you may want to look up "Refresh Interval" in Help. (Get to the actual setting by clicking Tools-Options-Advanced in A2K.)

Life's always a balancing act: the lower the refresh interval the more current your on-screen form data but the greater the network traffic.

Regards,
Tim

 

[bjackson]

thanks for the input guys

i have it set on a 30 second interval to requery but the user complains that its creating to much network traffic.(which it is)i wish these users would realize lifes a balancing act.They want it
both ways,speed and 100% bullet proof,even want buttons to tell
them if they ever make a mistake doing anything!!! connected to the database or just general thought process

Is there anyway that i could lock the table once one a user is
accessing the table,with maybe a message to tell them
to try again later.

I am not real up on locking tables and identifying which machine
is accessing the b/e db

 

[pono1]

While you can write code to set your own rules for record concurrency and record-locking, you're looking at a massive amount of work and, in one sense, throwing away a big benefit that comes with using Access, which can handle record-locking for you more or less automatically.

In the same screen where you set the Refresh Interval, there is a setting for record locking (in A2K). On your back end, set it to "Edited Record." Access will now only let 1 user at a time edit a record (first come, first serve) -- others may view it but they won't be able to make changes until the original user releases the lock by moving off the record.

The easiest way to give your users feedback as to whether a rec is locked or not is to set the forms' Record Selector property to "Yes." Many developers turn them off, claiming they look too industrial and heavy-handed and generally ugly -- they have a point -- and, too, because they are of little use in a single user DB. But in a multiUser environment the record selector will display a circle with a diagonal line through it if the current rec is locked and thereby give your users the feedback which they seem to be demanding. (I prefer complacent, dull-minded users to active, curious users. For friends and acquaintances, just the opposite -- for the most part.)

You will want to experiment a bit on your own and, of course, once you get it down, then explain to your users the first-come, first-serve rule and point out the lock symbol.

There's more info in Help on your options with "record locking" strategies.

Regards,
Tim

 

[Pat Hartman]

Exactly what is the business problem you are trying to solve? I'm having trouble figuring out what type of application requires such concurrency.

 

[bjackson]

Thanks Tim ,i will take your advice and learn more about access
locking strategies

Pat I Will try to explain what the Owner of The business wants

1. he wants to have 3 users on 3 pcs to be able to allocate
jobs to 3 different delivery runs,or maybe more in the future

2.as soon as 1 user makes a selection from the list of available jobs(a list based on a query of what is ready for delivery),he would like an instant refresh on the other 2 machines so they cannot pick the same job and put it in a different run.

i have it set up so that when a user selects from the list,the job is flagged as being not available and the sub form is requeryed on the users machine to display the up to date data.of course it is still displayed on the list on the other 2 machines.
i also have on the on click event for the list ,a routine that looks at the available flag for that job and tells the user that since they have started another user has selected the job that they have chosen
and requerys that pc and tells them to make another selection

i thought that should be ok, but his concern is what if 2 or more users made the same selection at exactly the same time

i dont have enough experience or knowledge to answer that
question and wonder at the chances of that scenaro happening,but of course you have to plan for every possible thing,
or is this guy going a bit over the top

Hope i have explained it enough for you and having read quite a few of your posts am sure you would know where the answer lies
thanks again for the support

 

[pono1]

Finally, the real question emerges.

Pat may offer a different perspective but to me the answer is obvious: install your app and test it with multiple users. If it fails, re-assess and adjust. Given the complexity of what you're doing, it will be next to impossible to know whether it will work or not until you give it a good going over.

Of the two routing apps I have been exposed to (at a major U.S. retailer), there was -- by rules of the app -- never more than one person routing for any one geographic area / business unit. So if you can get your app to work in an orderly fashion with multiple users, not only should you feel pretty good about yourself but there also might be a market out there for it...

Good luck.

Regards,
Tim

 

[bjackson]

Tim
I have tested at home on a network between 2 pcs and i thought
what i had done was sufficient,as if my daughter selects the same job as me a split second later she gets the message that
another user has selected that job and so it refreshes her screen to reflect the true state of the data.

We havent as yet been able to select the same job at exactly the
same instance and so testing becomes extremely difficult.Im not
sure how time is measured when a button is clicked but it must be
an extremely small value

However I cant answer my users question as i simply dont know,and im not even sure what error would be generated or
what error trapping i could use to catch that minute moment in time

Thankyou for your input
Regards
Bruce Jackson

 

[pono1]

Bruce,

On most all LANs (most networks use Ethernet), there are no ties.

Only one signal from one PC can be moving over the network wire at one time (think of "the wire" as a single cable looping through all PCs on the local area network). If there are two signals on the wire, there is a collision and both signals die. The competing PCs, with an ear to the wire, upon hearing their packets expire, reset for a random period and re-send.

If wiring is centralized with a switch (rather than a hub), the signals race directly over an individual wire, collision free, to the switch where they are processed. This is why a switch is faster than a hub, all things being equal -- no collisions.

That is, if Computer A and Computer B both send a signal bound to the server -- via the switch -- the switch will determine who goes first over the wire that runs from it to the server.

The server processes the signals it receives and sends them to the app. If the server is busy with other tasks, it's conceivable that two requests for the same record in the same app made at different times will queue up, as though they both arrived simultaneously.

As you can see it's ultimately the last link in the chain (the server) that determines what gets pushed to the app and when it gets pushed to the app, and then it's the app that deals with multiple requests from the server, via the switch/hub, via the workstation. Traffic from other apps on the network and server performance can influence net speed and reliability.

The point: Assuming I didn't just make all this up, networks are built to handle "simultaneous" access and, at least on paper, so is Access.

A thought: If your app works as advertized, you may see some frustration from the routing trio as a job they want to schedule goes away and then, perhaps (don't know if you built this in), later, comes back if one of them decides to throw it back into the bin -- sort of speak. That is, you must guard against jobs getting left behind if you already haven't.

Regards,
Tim

 

[bjackson]

Thanks Tim
Yeah there are sure a lot of possibilities and this partivular user
thinks its a simple task,His mind cant grasp that the human mind
has all these powers of what may occur built in
The computer Has not!!!,It has to be told(programmed)

I have set it up so that if a user leaves a job on the rhs of the screen(That is where their Selection Moves to) then if they exit the db with out printing their Delivery Run (The print action Triggers a Real Run Number) then any jobs that were not printed are put back in the pool so to speak.

the user Can Choose to Lock their selections and move around the db with out removing their jobs from the rhs,but if they are not printed when the db closes,then they are returned to the list

if one of the users throws their selection back to the lhs then
the next button click on any machine will refresh their data.
I can see some other possibilties,though remote but where
do you draw the line

thank you for the insight into Lans as the limited Knowledge that i have doesnt extend to networks.
This whole task started out as a small Favour to a friend and has grown into a monster,one which i am probably out of my depth but feel obligated to finish(Why?? i dont know)

 

[pono1]

Bruce,

You've obviously done a lot of work on this app. It's good you are trying to cover all your bases and think through all scenarios so that the app is optimized -- silly for me really to second-guess things from a distance (but that's the Internet).

And, yes, otherwise easy human tasks can turn into surprisingly complex programming chores.

Having worked on a couple of monster projects, day in, day out, details weighing me down, I know that you can not only get sick of the thing but also endlessly worry that you've thought of everything there is to think of. You rarely do -- at least not if there's someone else involved in the development, pitching ideas and asking for features.

Since you are doing this for a friend, hopefully he or she will cut you some slack when it comes to putting the program on the network. I would politely recommend to him or her that you put the DB in place on a trial basis, where you first train the users and then see how they respond and, too, how the app performs.

The better the users understand things up front, the better your chances of success. Expect bugs, expect user behavior you never anticipated, and making adjustments, if necessary, will be less painful.

Regards,
Tim

 

[bjackson]

Thanks Tim
It is comforting to Know That there are professionals such as your self and all the others who use this forum , who are willing to share their knowledge and experience

I only learnt how to use the internet last november and have learnt an enormous amount from forums such as this one, not just by asking questions, but by reading posts and all the answers that go with them, and sometimes answering some myself

i will let you know the final result of this if it ever gets finished(i am close i think)
Regards
Bruce

 

[bjackson]

Tim
I dont want to be a pain,but could i ask you another question

In access when you create a table,for any text fields access
sets the deafult of allow zero length to no.I assume there is a good reason.
I havent had a problem with that up until i linked to a db that a vb app used for data storage only.

i would get error messages when processing the data and the only way to fix it was to set my tables to allow zero length=yes

While that fixed things,I dont unserstand why you would store a zero length string

Could you explain What is the difference and when would
you store a zero length
Thanks
Bruce Jackson

 

[pono1]

The Wandering Thread

Bruce,

Yeah -- they're confusingly similar but completely different. A zero length string is essentially a string that has no characters, not even a space (it has body but no content), while null is simply nothing.

They both display as a "blank" in a form, as does a space (though you can see a space using the blinking cursor when a textbox has focus).

Probably best to think of null values and zero length strings as two different values that are available to you -- you the DB developer. Also, as you implied, it's probably best not to allow zero length strings unless you have good reason.

One catch: If you don't allow zero length strings and run a report that includes a null value, the null will not display as zero -- it won't display at all since nothing is there.

The fix? Use the Nz function (Lots on that in Help.)

Here's Microsoft's take on when you might want to allow both a zero length string and null: Null and Zero Length String Settings

May be other articles on the same topic in the knowledgebase as well as at msdn.microsoft.com.

Regards,
Tim