Record-Locking not working!! Getting reeeeeaaally frustrated.
Jon22
staff can have their own front end with the back end stored somewhere on our
small office network. I thought I'd create a small test database first,
because some months back, I did some testing with it split and I was having a
lot of trouble trying to get the database to lock out other users from
records being edited by someone else. Well I'm still having the same problem.
I've read up on various web sites (including microsoft sites) all about
sharing Access databases and as far as I can tell, I have all the settings
configured correctly. I keep reading how the record gets locked with the
little lock symbol displaying on the record selector and la de da de da but
that perfect little world aint happening for me! I've even tried running code
to requery the form on the Before Insert event (no luck) then tried the same
on the On Dirty event, then After Insert event. Requery-ing on the After
Insert event got me the closest as it updated the records in the underlying
table but I still don't get that locked symbol appearing and I'm still able
to create records with the same unique default value. I had the back end
sitting on a network attached storage device which I thought might be the
cause so I moved this to a shared folder on my own hard drive and linked the
front end to that but still no luck.
My testing setup is nice and simple. I have my desktop workstation with the
back end sitting in a shared folder on this. I have a copy of the front end
sitting on the desktop of this same machine, and another front end sitting on
the desktop of my laptop which is wirelessly connected to the same network.
My testing database contains a form (lets call it [QuoteEntry]) which has
three fields, lets call them [nmb1], [txt2] and [txt3]. Lets say the form's
Record Source is a Table named [Quotes] which contains the fields also named
[nmb1], [txt2] and [txt3]. On my form [QuoteEntry], I've set the [nmb1]
Default Value property to generate a value which I'd like to keep unique for
each record (for reasons that I won't go into now, I don't wish to make this
field a Primary Key field and I have several forms on my proper database
which will share this scenario). Lets say this Default Value property is set
to this: =Nz(DMax("[nmb1]","[Quotes]"),0)+1 which will always give the next
available number when a user goes to a new record.
In my testing, I've been able to create records with identical [nmb1] values
because one front end is not locking out the other front end from editing the
same record. This is obviously problematic.
I need my users to be able to use the same form simultaneously but have
unique [nmb1] values assigned to the record they are working on.
All three test databases (the two front ends and the back end) have the same
sharing settings: Default open mode is "Sahred"; Default record locking is
set to "Edited record"; "Open databases using record level locking" is
ticked; and the forms record locks property is set to "Edited Record".
Can anyone suggest what might be causing my record locking problem? Much
appreciated.
Jeff Boyce
Which version of Access are you using?
Regards
Jeff Boyce
Microsoft Access MVP
--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.
Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.
You can thank the FTC of the USA for making this disclaimer
possible/necessary.
"Jon22" <Jo...@discussions.microsoft.com> wrote in message
news:4FB998F9-3249-403E...@microsoft.com...
Armen Stein
<Jo...@discussions.microsoft.com> wrote:
>Lets say this Default Value property is set
>to this: =Nz(DMax("[nmb1]","[Quotes]"),0)+1 which will always give the next
>available number when a user goes to a new record.
Hi Jon,
You've got a couple of different things going on here.
First, your back-end Access database needs to be on a Windows
operating system. The NAS might not have the right record-locking
support to handle multiple users.
Also, just so you know, Access doesn't work well over a wireless
connection to an Access back-end (SQL is another story). You might be
setting yourself up for corruption.
But back to your problem.
You are using the DMax method to get the next available number.
However, when your user begins to edit a record, Access doesn't commit
the record to the database, so the DMax can't pick it up. You've been
trying to force it to happen, but this will continue to be trouble.
Especially since it isn't a primary key, but you said you didn't want
to explain that now.
Also, the DMax method, even in the best scenarios, has a loophole that
at the same instant, two different users could get the same "next"
value, because it is inherently possible for both user's DMax to read
the records before either of them writes one. So the DMax method,
although proposed by lots of people, is too simple to be used in
serious applications.
There's a better way.
You need another table, perhaps a standalone table with just one
record, to hold the NextAvailableQuoteNumber. Build a little function
called NextQuoteNumber() and in it, open an updateable recordset on
that table. Read the record, load the next number in to a variable,
increment the field in the table by 1, then update the record. Pass
the variable out as the result of the function. Now you have a
bulletproof next number generator that only one user can use at a
time.
This example is described in this KB article:
http://support.microsoft.com/kb/140908
Hope this helps!
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Jon22
"Armen Stein" wrote:
> .
>
Jon22
"Jeff Boyce" wrote:
> .
>