Access over a network HELP NEEDED

[Ronny sigo]

Hello all,
A big part of my life I have been working with databases (as a non
professional) on a local machine. Apart from a small problem now and then,
for which I find the solution myself, or some of you guys provide me with
the solution, all goes well. But now, for the first time I am faced with the
porblem that I have to run Access over a network, and my program, which
worked so fine on a single machine does not function .... Here's the prob :
I have a bound form , and one of the controls is bound to a numeric field on
a table. On opening the form, the program goes to the last record entered,
gets this value and increments it by 1, then the control gets updated. Now,
I tried this out with another user. He opens the form, all goes well, then I
open the form on my machine. When I open it, the other user gets the message
that Access was not (or will not be) able to save the record, since a
primary key violation was encountered ..... Which, coming to think of it, is
normal, because the other user's fomr is still open ....
But, me not being used to networking .... how do I solve this problem ?
Any help appreciated ....
Thanks
Ronny

[Rick Brandt]

"Ronny sigo" <ro*********@toegrefnet7.be> wrote in message
news:3f**********************@feed0.news.be.easyne t.net...

Hello all,
A big part of my life I have been working with databases (as a non
professional) on a local machine. Apart from a small problem now and then, for which I find the solution myself, or some of you guys provide me with
the solution, all goes well. But now, for the first time I am faced with the porblem that I have to run Access over a network, and my program, which
worked so fine on a single machine does not function .... Here's the prob : I have a bound form , and one of the controls is bound to a numeric field on a table. On opening the form, the program goes to the last record entered, gets this value and increments it by 1, then the control gets updated. Now, I tried this out with another user. He opens the form, all goes well, then I open the form on my machine. When I open it, the other user gets the message that Access was not (or will not be) able to save the record, since a
primary key violation was encountered ..... Which, coming to think of it, is normal, because the other user's fomr is still open ....
But, me not being used to networking .... how do I solve this problem ?

You need to use a different event to assign the new number. I use
BeforeUpdate because this allows for only a very tiny time interval between
the calculation of the next number and the saving of the record. That is
crucial if you need multiple users inserting records simultaneously.

The only drawback to BeforeUpdate is that is can fire multiple times in the
life of a record so you need to put your number assignment inside an
If-Then block so it only assigns numbers to records that don't already have
them.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Larry Linson]

"Ronny sigo" wrote

. . . Access over a network, and my
program, which worked so fine on a
single machine does not function

Just as a start, download and view my presentation on Access in a Multiuser
Environment from http://appdevissues.tripod.com. There's also good
information on multiuser Access issues at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

You don't make clear what your use of the incremented value will be -- that
is, whether it is a value that needs to be handled by humans, and thus
should be in sequence without gaps. If its use, or primary use, is simply
for internal identification of your data within the database, using an
AutoNumber should avoid the problem you're having.

It also isn't clear whether both users are logging in to the same copy of
the front-end or monolithic database. They should not be, although this
isn't the cause of the problem you are experiencing. It might be the cause
of other problems, later, though.

Clarify your needs for the value you are incrementing, and we'll be better
able to comment and suggest.

Larry Linson
Microsoft Access MVP