Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Shared Workbook (2000/SR1)

    I'm new to this company. They have a massive Excel workbook that they use for scheduling all of their work. It really should be in Access but I'm not ready to suggest that. Here's the problem, most people need to be able to do updates on the schedule but they don't have to do the updates all the time. Only one person can do updates at a time, this is probably a good thing. Unfortunately, whoever opens it first is the only one that can do the update and if they walk away from their computer, and most everyone password protects their PC's, no one else can get at it to do updates. They can view the existing information, just no updates.

    Question #1 - can it be set up so that multiple people can do updates at the same time?
    Question #2 - can it be set up so that when the first person opens it, it doesn't lock out everybody else, or is there a way to "shut down" the one person remotely.

    Thanks for any help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared Workbook (2000/SR1)

    I do NOT recommend this, but excel "allows" the workbook to be shared (tools - share workbook...)

    I have NEVER done had a need to do this and have heard TERRIBLE things about this feature.

    I think access might be a better choice if you need to do this.

    You could put a macro in it to save and close it down after so many minutes of non-use, though this could get sluggish.

    This go in the thisworkbook object.

    <pre>Option Explicit
    Private Sub Workbook_Open()
    Close_Time = Now + TimeValue("00:30:00")
    Run_Time
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If Close_Time Then
    Application.OnTime _
    EarliestTime:=Close_Time, _
    Procedure:="Close_WB", _
    Schedule:=False
    Close_Time = Empty
    End If
    Close_Time = Now + TimeValue("00:30:00")
    Run_Time
    End Sub</pre>


    These go in a module:

    <pre>Option Explicit
    Public Close_Time
    Sub Run_Time()
    Application.OnTime _
    EarliestTime:=Close_Time, _
    Procedure:="Close_WB", _
    Schedule:=True
    End Sub

    Sub Close_WB()
    Application.DisplayAlerts = False
    ThisWorkbook.Close (True)
    End Sub</pre>


    The macro sets macro to run after 30 mins to close and save the file. If you make a change to the file, it will cancel the timer and start a new one (hence the sluggishness).

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook (2000/SR1)

    Hi Steve

    I'll pass your post (the deterrant part, rather than the solution part <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) on to my friend, to try to sway his development efforts away from Excel. He also told me that he's heard that even Access has its difficulties with multiple user updates to a shared database. I know too little of Access to comment though.

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared Workbook (2000/SR1)

    I very rarely use access, so I claim no expertise in that area. I have not heard of problems in this area (I think it is MORE what it is meant to do than excel is).

    My IMPRESSION (how I imagine it works- I don't know HOW it actually works in excel or access, so these are ILLUSTRATIONS):

    when you open excel, there is the SAVED copy on the drive and a separate copy on your computer. What you do to the copy on your computer does NOT affect the SAVED copy until you save it (if you SAVEAS with a new name) you make NO changes to the original!

    In Access it doesn't seem to work this way (and any ACCESS experts, please correct my mistakes). It actually WORKS with the COPY that is SAVED. When you make a change, the file is updated. You can not work for (eg) 15 min in access changing things and say, "I am going to quit without saving" and it will be like it was 15 minutes ago (like you can in excel). Those changes WERE made and it IS ALREADY saved. You would have to go to a BACKUP copy you made (IF you made one) to undo the last 15 mins.

    SO when multiple people are using Access, it is NOT as difficult to keep track of all the changes. Some "sections" might be being used exclusively (at the moment) in access, but all changes by all users are happening at nearly the same time so the file is "ALWAYS" up to date.

    But the SHARED excel has the SAVED copy on the network drive, and each indvidual user has a SEPARATE copy of it open. When any of them tries to SAVE, excel must keep track of all the changes they did with what the next person is doing.

    Imagine User1 opening it and making MAJOR revisions for 30 mins. His copy could be drastically different than the one User2 opened 15 mins after user1 started. User1 works 5 mins and saves (but continues) and user3 opens (after User2 saves). Now user3 works for 10 mins.

    At this time you have the SAVED copy which has USER2 changes after 10, User1 has 30 mins of change from the ORIGINAL (never seeing User2 changes), User 2 has made more changes to his copy, and user3 made changes to his copy. Now at the same time they all try to save. Excel has to keep track of ALL these changes (and to be honest) I have no idea how it could even try to figure out what the final "merging" should be. If you gave me all 4 of these files, I would hard pressed in figuring it out in many cases. All three could make different edits to the same record.

    It is not that difficult to see (at least as I imagine) that sharing in excel could have "problems".

    In the scenario I imagine with access, since there are NOT SEPARATE copies to keep track of, only the one copy being updated as we go, there should be a lot less problems. I also think access PREVENTS 2 from editing the SAME record at the same time so there is some exclusivity (in the sharing) that helps to prevent the "simultaneous" editing of the same data.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook (2000/SR1)

    Thanks for all that explanation Steve. I'll certainly pass that on. I think, form what you're saying, that Excel doesn't have any of the "standard" shared database capabilities that Access presumably does. I'm also unfamiliar with Access, but have had a fair stint with Oracle. It's the scheduled transactions that I mentioned in the other thread on this (together with associated read/ write locks, checks on dirty reads/ writes etc.) that distinguish a "real" shared database from what you describe as Excel's "sharing" philosophy.

    I must admit to being very wary when I began reading about all the revision histories of the various users, and wondered like yourself, how on Earth the final "correct state" could be established. I can only imagine that it's ultimately up to the "owner" to decide what constitutes the correct/ final state, since no user would be aware of any updates being made "simultaneously" by any other user. It seems more like an audit trail is left by all users, and it's up to somebody to wade through it and decide what to keep and what to regard as overwritten. I can certainly see the problems and feel the headaches already <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>.

    cheers

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shared Workbook (2000/SR1)

    Steve's description is correct.

    Access queries and forms have a RecordLocks property that can be set to:
    <UL><LI>None: two users can edit the same record at the same time; when the first user saves the record, either explicitly or implicitly (by moving to another record or by closing the query/form), the second user gets a message; (s)he can decide to keep the other user's changes or to override them, or to copy the changed record to the clipboard.
    <LI>Edited record: when a user is editing a record, it is locked for editing (not for viewing) for all other users; other records can be edited. As soon as the user saves the record, either explicitly or implicitly (by moving to another record or by closing the query/form), it can be edited by another.
    <LI>All records: when a user is editing a record, ALL records in the table(s) behind the query/form are locked for editing (not viewing) for all other users. As soon as the user saves the record, either explicitly or implicitly (by moving to another record or by closing the query/form), the lock is released.[/list]As Steve remarked, Access saves changes to a record directly to the database itself, not to a local copy for each user.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared Workbook (2000/SR1)

    Thank you for the verification on my "imaginings and speculations" and also for the additional info on the recordlocks property. It is good to know the intuition still is working.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook (2000/SR1)

    Thanks for the extra info Hans. It seems like an implementation of the fairly standard levels of locking I'm familiar with (attribute/ record/ table) but the "None" level has me baffled. When you say that User2 can decide whether to keep or override changes made by User1, are we still talking about the single copy of the dbase table? Or are these local user views or something? If the former, who gets the privelige to override somebody else? Is there some sort of heirarchy among users? Sorry, but I just can't see how this one is meant to work.

    Alan

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared Workbook (2000/SR1)

    I see it as the last one to edit ultimately controls it.

    It would work the same way if they did it exclusively. User 1 changes record 1 and quits. user2 opens the same record and changes it back. user1's work is Undone, since User2 was later.

    It the database example, user 2 gets a message since user2 was editing WHILE user1 had just changed it and finished.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Workbook (2000/SR1)

    <hr>user 2 gets a message since user2 was editing WHILE user1 had just changed it and finished<hr>
    Ah, OK. Now I see the idea - seems like a nice way of telling User2 that they're now the victim of a dirty read. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shared Workbook (2000/SR1)

    Alan, the 'None' option is fairly primitive. Say that User1 and User2 start editing the same record. User1 moves to another record, or closes the form. The record is saved in the single copy of the database. A few minute later, User2 starts to move to another record, or to close the form. This user is presented with a message box explaining that the record has been modified by another user. The message box has three options:
    - Save the record, overwriting the changes by User1.
    - Copy the record to the clipboard, so that User2 can view the changes made by User1, and then decide whether or not to paste his/her changes.
    - Cancel the changes made by him/herself, thus keeping those made by User1.

    So User2 gets to decide whose changes will be kept. This can be acceptable if it is improbable that two users will edit the same record at the same time, or in a small office where users can contact each other directly; in a situation where many users will be updating, the "edited record" option is probably better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •