From: Banana on
Paul wrote:
> No, this is a real issue for me. I've spent the last year working on a very
> real project management database in Access 2003. At the moment, I have 40
> users, and a week from Monday, I'm going to have about 100. Just within the
> past three weeks, my users have started to encounter the Write Conflict
> error I described in my first post.

Other posters has given you many excellent advices. I just want to rule
out one more cause of write conflict: It is possible that the write
conflict errors are bogus because of VBA coding stepping on itself or on
the Access. A good way to do this is to have VBA execute a separate
query that modifies the same record that is being edited in the form.
This comes out as two separate connection and of course the software
(whether it's Access or the backend RDBMS) has no idea that the two
separate connection are actually the same application/user and perceive
it as deadlock for this reason.

Therefore, if your VBA code behind the forms does use queries that
update the same record or maybe different record on the same table that
could be on the same page, this will cause write conflicts.

If your code doesn't have that, then you're probably good and want to
look at others' excellent solutions.

Best of luck.
From: Paul on
Interesting point, Banana. I am running several queries in the AfterUpdate
event of several controls that append or upate other records in the same
table. I wonder if the problem occurs when those records are on the same
page. Howevr, there are no memo fields in those tables, but then if I
understand it right, in those cases my option settings should enable Acces
to only lock the edited record, not a group (page) of records.



"Banana" <Banana(a)Republic> wrote in message
news:4B51ED80.5080706(a)Republic...
> Paul wrote:
>> No, this is a real issue for me. I've spent the last year working on a
>> very real project management database in Access 2003. At the moment, I
>> have 40 users, and a week from Monday, I'm going to have about 100. Just
>> within the past three weeks, my users have started to encounter the Write
>> Conflict error I described in my first post.
>
> Other posters has given you many excellent advices. I just want to rule
> out one more cause of write conflict: It is possible that the write
> conflict errors are bogus because of VBA coding stepping on itself or on
> the Access. A good way to do this is to have VBA execute a separate query
> that modifies the same record that is being edited in the form. This comes
> out as two separate connection and of course the software (whether it's
> Access or the backend RDBMS) has no idea that the two separate connection
> are actually the same application/user and perceive it as deadlock for
> this reason.
>
> Therefore, if your VBA code behind the forms does use queries that update
> the same record or maybe different record on the same table that could be
> on the same page, this will cause write conflicts.
>
> If your code doesn't have that, then you're probably good and want to look
> at others' excellent solutions.
>
> Best of luck.


From: Banana on
Paul wrote:
> Interesting point, Banana. I am running several queries in the AfterUpdate
> event of several controls that append or upate other records in the same
> table. I wonder if the problem occurs when those records are on the same
> page. Howevr, there are no memo fields in those tables, but then if I
> understand it right, in those cases my option settings should enable Acces
> to only lock the edited record, not a group (page) of records.

Well, depends on the context. For example, if you are using a continuous
view or datasheet view, then you are certainly locking a group of
records. As for the locking behavior, few points.

1) According to this:

http://office.microsoft.com/en-us/access/HP052408601033.aspx

"If you use record-level locking, Access locks only the record you are
editing. This becomes the default behavior for access to data through a
form, a datasheet, and code that uses a recordset object to loop through
records, but not through action queries, nor through code that performs
bulk operations using SQL statements."

IOW, record-level locking is only available on forms and not via
queries, so queries could then conflict with the forms' locking.

2) I also want to say that the setting you specify is a merely request
and not a request so there is always a chance that the locking could get
escalated by Access. However I was unable to find the supporting article
for this, so take the second assertion with a huge grain of salt.

Either way, I can tell you that I've been personally bitten by running
action queries against the same table in middle of editing. But by being
aware of how this works, we can certainly work with it. One posssible
example is to avoid running queries while a record is dirty. Assuming no
locks (or rather, more formally known as optimistic locking), running
queries in say, form's AfterUpdate is usually fine (but a control's
AfterUpdate may be insufficient because the record may be still dirty).

Be aware, though, that this is not a complete solution in fact that you
are just changing around the timeframe of when updates occurs and
actually may be increasing the risk of contention in a high-concurrency
scenario.

Alternatively, working through the form's model is probably the best way
to eliminate locking problems because that means you are now re-using
the same 'connection' to do updates on other stuff. You have the
Recordset property, RecordsetClone property, Bookmark property and
several methods for DAO.Recordset objects to accomplish all this, and
indeed, I do this for where I want to edit a related record on the same
table. For bulk operations, I prefer action queries anyway, so that's
back to the first suggestion.

HTH.
From: Paul on
Based on what you've said, and what I read in that Microsoft article you
referenced, it would probably be a good idea to replace some of my action
queries was DAO code, because I am performing action queries on continuous
(tabular) view subforms.

One approach I've already tried is to have the Timer event of the form save
the form's record every few minutes to prevent against people leaving an
edited record unsaved for long periods of time.
Thanks for the explanations, Banana.

P


From: Tom Wickerath AOS168b AT comcast DOT on
Hi Paul,

Thanks for the nice compliment on my Multiuser Applications paper!

> However, they, along with my combo box, are also empty. Is there a way I
> could get both the combo box and the form to display the first record in the
> recordset when the form first opens?

Sure, you just need to uniquely identify the record in question, and use the
appropriate WHERE clause. So, instead of using WHERE 1=0, use something like:

WHERE [PrimaryKeyFieldName] = NumericValue (for a numeric PK field)
or
WHERE [PrimaryKeyFieldName] = 'TextValue' (for a text-based PK field)

Notes:
Use the quotes surrounding the TextValue for a text-based value.
You can specify a different field as well, as long as the field has a unique
index, in order to retrieve just one record.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Paul" wrote:

> Tom,
>
> It was your excellent article at
>
> http://www.accessmvp.com/TWickerath/articles/multiuser.htm
>
> that launched me on this path in the first place. I've got two tables with
> a memo field, and I plan to isolate them into separate 1 to 1 tables in the
> coming weeks. I already took a run at it and quickly realized that I'll be
> spending several weeks debugging all the changes required by the new table
> structure. I thought that in the meantime, I'd take the step of loading one
> record at a time into the main form, instead of multiple records.
>
> I found your suggestion to set the RecordSource in the form's Load event to
>
> > SELECT * FROM table WHERE 1=0
>
> to be very helpful, because now when the form loads all of the controls and
> subforms are visible.
>
> However, they, along with my combo box, are also empty. Is there a way I
> could get both the combo box and the form to display the first record in the
> recordset when the form first opens?
>
> (If it requires DAO or ADO code, a dumbded-down answer in the form of the
> actual code would be most welcome).
>
> Thanks
>
> Paul
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13
Prev: não acho meu orkut
Next: maike yordano pirela vera