A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

trying to minimize Write Conflicts in a multi-user database



 
 
Thread Tools Display Modes
  #11  
Old January 15th, 2010, 08:14 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

Tom,

It was your excellent article at

http://www.accessmvp.com/TWickerath/.../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


  #12  
Old January 15th, 2010, 08:22 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database


If you need help building that code, post back.


John,

YES, PLEASE! I'd never be able to figure it out on my own.

I've taken several runs at trying to understand DAO and ADO coding, but I
still struggle with it. The only MS Access book I ever found that explained
it in a way I could understand it was the manual for Access 2.0, which I
only borrowed temporarily. But I didn't use it right away, and didn't
manage to retain what I understood from it.

If you know of any Web sites that explain the basics of DAO and ADO, I'd
love to check them out.

And thanks for your help with this.

Paul


  #13  
Old January 15th, 2010, 09:07 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

Jack,

I tried using

Me.Combo = Me.Combo.Column(Me.Combo.ListIndex + 1)

and the combo box and form still open with no records.

I also tried assigning a number to Me.Combo.ListIndex,

Me.Combo.ListIndex = 0

but I got and error message saying I've "Used the ListIndex property
incorrectly.

As I've said in other replies in this thread, I've got the form opening with
no records initially, and as soon as I make the selection in the combo box,
the selected record appears in both the combo box and the form. What I'm
trying to accomplish is for both the combo box and the form to be populated
with the "first" record in the recordset (ListIndex = 0) when the form
loads. Please let me know if you have any ideas about how to accomplish
this.

I've asked the same question in my reply to Tom's last message, and I'm not
sure if it's proper newsgroup ettiquette to repeat the same question to
another participant in the conversation, but if not, I apologize for the
redundancy.

Paul


  #14  
Old January 16th, 2010, 01:11 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
GBA
external usenet poster
 
Posts: 174
Default trying to minimize Write Conflicts in a multi-user database

just a sanity check as to whether this string is purely theoretical or not.
Have done many many multi user applications with nary a write
conflict....Access out of the box works really well in this area and so I
wonder if the issue is theoretical or real....

"Paul" wrote:

I have a multi-user Access 2003 database and lately a number of our users
are running into the Write Conflict message gives them the choice to either
Save Record, Copy to Clipboard or Drop Changes.

The problem occurs in a tabbed form with subforms on the different pages.

I have been told by several developers that one way to minimize the
occurrence of the Write Conflict is to put the main form's controls into a
subform and remove the Record Source from the main form. You then set Child
and Master Field links in the subforms to the value returned by the record
selection combo box on the main form (stored in a text box on the main
form).

In effect, you'd only have one record open at a time from the parent table
instead of loading multiple records into the the main form at once.

Would this in fact help reduce the number of Write Conflicts? I ask because
there are a number of events in the main form the various subforms what
would have to be modified, and I'd like to confirm that it will accomplish
something before I spend the time making those changes.

Thanks in advance,

Paul


.

  #15  
Old January 16th, 2010, 12:42 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 68
Default trying to minimize Write Conflicts in a multi-user database

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.

I work in a very real state government agency. My colleagues are using it
to manage their projects, and I'll list (hey - you asked if it was real)
just a few of the features that make it more than a card filing system:

* Every night my VBA code runs 42 queries that append and update data in our
application from an Oracle database, SQL Server and another Access database.
They also upload different data to that other Access database.
* In addition to projects, it also manages leases, contacts, activity nd
documents.
* It uses the OS login name to distinguish between editable and read only
records, depending on whether the user is a team member of the project
* there are 5 user classes - user, admin, admin User, read only and a 5th
one that I can't recall at the moment - and depending on which class the
user belongs, different forms and different form controls will be presented
to the user.
* It's also a document processing file manager. My users process lots of
contracts and documents, and my application enables them to select from
hundreds of documents in Word, Excel, PDF and html from a shortcut menu
sysem, and it populates fields in the documents with data in the database.
It also saves the files into the project folder on the network drive, so the
user doesn't have to navigate through Windows explorer to find the project
folder.

We have conservatively estimated that the file management module I just
described is saving our agency and the taxpayer the time equivalent of over
$500,000 per year.

Maybe you don't have a problem because you designed your database better
than I did. I do have memo fields in two tables, and Tom Wickerath has
pointed out that could be the problem.

But yes, it's a real database, and I'm dealing with a very real problem.

Paul


  #16  
Old January 16th, 2010, 05:46 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Banana[_2_]
external usenet poster
 
Posts: 214
Default trying to minimize Write Conflicts in a multi-user database

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.
  #17  
Old January 17th, 2010, 01:07 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 68
Default trying to minimize Write Conflicts in a multi-user database

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@Republic wrote in message
news:4B51ED80.5080706@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.



  #18  
Old January 17th, 2010, 02:36 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Banana[_2_]
external usenet poster
 
Posts: 214
Default trying to minimize Write Conflicts in a multi-user database

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/ac...408601033.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.
  #19  
Old January 17th, 2010, 08:27 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 68
Default trying to minimize Write Conflicts in a multi-user database

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


  #20  
Old January 17th, 2010, 09:44 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default trying to minimize Write Conflicts in a multi-user database

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/.../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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.