Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Multi user problems on a unix network (97/SR2)

    Just crying out some frustration... as probably no-one will have an appropriate help for this one...

    I started at a new job. There, a simple Access-file (2 linked tables, query, form & subform, 300 & 600 records) turned strangely enough nuts after having been used for maybe a few days:

    - an innocent collegue-user which entered the data, suddenly got a message about duplicate key field values in the (autonumber) ID field
    => reason: autonumber restarted at once, numbering at a lower number
    (we got it solved by entering some data in a new record & (instead of saving/moving to another record) pressing escape twice to 'undo' & making Access 'loose' the current number in it's autonumbering process, untill autonumber finally re-arrived at the last used number)

    - a short while later, the form & database got corrupt, sending messages which normally only appear when you try to open an Access2000 file with Access97: unknown database format, etc. Database repair didn't work - suddenly worked once producing a file that soon got corrupt AGAIN and then, we gave up & started again with a backup.
    (it worked fine for about a week, then)

    - irritatingly & inconsistent restricted access to queries and tables
    e.g. Access refuses to open linked tables & (also in the same session created & for some seconds working) queries with an error like (translated from Dutch) "Microsoft Jet Engine stopped the process because you and another user tried to edit the same data"
    e.g. All record locking,... properties & options are set 'multi-user'. Still, when the database is already opened by another user, other users only get access after trying opening the file twice. The first time, they're warned that "The table MSysModules2 is exclusively opened by another user or opened by means of the interface and can't be manipulated by the program".

    I don't know what to expect next, but you can imagine that this doesn't invite to using my most beloved database development tool much longer...
    More: elder collegues, experienced from a previous project, told that their application tended to crash weekly/... - only regular backup helped. They told that even when the file is accessed by different users on different times, problems remain. So the problems ended... when Access use ended...
    More: I attended the helpdesk of the company supporting the IT around here. Their advice: don't use Access. The replacement of the network isn't planned for the near future. And that seems to be a regular advise here.

    BEFORE I 'm forced to move to (and start learning) Filemaker pro or whatever... (if that helps...)
    => does anyone have had the same experience?
    => does anyone have a solution?

    Hans Vanmechelen, Belgium, thought-to-be-a-rather-experienced-Access-developer

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multi user problems on a unix network (97/SR2)

    I have no experience running Access over a Unix network, but I don't think I've ever heard of all the troubles you've been having in any network situation. Have you tried using Jetcomp to repair the databases? It often can fix things that the internal repair can't.

    Are these databases split, with a frontend and backend? And if so, is the frontend on the server or on each local workstation?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi user problems on a unix network (97/SR2)

    Haven't seen the problem about restricted access to queries and tables. I have seen the other two. Yes, if your database gets corrupt, autonumbers can "forget" where they were. Rather than do what you did, when I had that happen to me I tried a compact; that did get the autonumbers back where they should have been. However, it didn't take long after that happened to me before I realized that the database was corrupt in other ways.

    One hint with Access 97 (Access 2000 automatically enforces this) - do not make any changes to the definitions of forms or reports when anyone else is in the database. If your database is split into front-end and back-end, the rule only applies to people using the same front-end that you want to change. It doesn't matter whether anyone else is using the same form or report that you want to change; simply the fact that they are in the database seems to make Access 97 very wobbly.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Multi user problems on a unix network (97/SR2)

    Mark,
    thanks for your reply.

    => I don't know Jetcomp but I'll sure check it out (enough search engines to show me the way).
    => The database wasn't split (yet), as I thought it wouldn't be necessary with its small complexity & amount of users (1 to occasionally 5). In case you'ld propose it as a solution: as I now even started getting problems with linked tables, I'm not sure it will help, but I'll try it out...
    => More in my answer to Douglas Martin...

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Multi user problems on a unix network (97/SR2)

    Hey, Douglas,

    So at least some of the mystery can be solved...
    Thanks a lot about the hint about NOT designing while other user(s) is in the database. From this point of view, even for the smallest database, splitting it in front and back end ends up to be a good idea anyhow... And putting front ends on each work station, even with this tiny app, is, knowing this, worth a consideration as well.

    Further, I'll try to figure out if designing & developing the databases on my pc hard disk (<=> network) might enhance stability.

    Right now, I wonder if there is an alternative to the worst case scenario: recovering an old old backup from before we started using it with more than one person, import the data from the latest version and restore the (fortunately - hopefully - limited) design changes made since then. Any suggestions?

    Thanks a lot for the advice, and (?) the shared experience of Access 97 going 'wobbly'!

    Hans

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi user problems on a unix network (97/SR2)

    Hans,

    There is an utility at FMS Inc Total Access Detective that compare two databases and generate reports with the difference between the two. If you want to use it only once it's expensive, so if you send me the two database I can compare it for you and send the reports with the design changes. See my e-mail address in my profile.
    Francois

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

    Re: Multi user problems on a unix network (97/SR2)

    Hans,

    I'd like to add my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> to the excellent suggestions others have already given.

    I had this problem of database corruption in a multi-user environment in Access 97. As far as I have been able to trace it, it happened as follows:
    1. <LI>User A and user B both open the same form.
      <LI>User A sets a filter (or changes the sort order, or ...).
      <LI>User A closes the form. Access sees the filter (or sort order or whatever) as a change in the design of the form. It tries so save the form (without asking for confirmation). But since user B also has the form open, there is a conflict. Apparently, Access can't resolve this gracefully.
    This problem doesn't occur if you make the database into an .mde database, or if you apply database security, so that standard users can't modify the design of forms. (There are other forms of corruption of course. Memo fields seem to be prone to corruption, and Access 97 is extremely sensitive to network problems)

    If .mde or database security are not an option, you might try the following:
    <UL><LI>Set the border style of forms to Dialog, so that they can't be maximized.
    <LI>Try to prevent users from inadvertently saving a form, by not showing the standard menus and toolbars.
    <LI>Prevent users from closing a form except by using a "Close" command button. I use a Boolean variable for this (see <post#=166577>post 166577</post#> - it has an attached example of this).
    <LI>Close forms in code without saving them:
    DoCmd.Close acForm, Me.Name, acSaveNo[/list]I have used this method in a few databases where .mde or database security were not possible (for reasons I won't go into here). This has eliminated the corruption problem almost completely <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>.

    Regards,
    (another) Hans

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    multi user solution works until now - thanks

    Thanks HansV,
    there were still several pieces of valuable information in your reply which I didn't know about...

    I'll try out the mde-solution and wait and see how far it'll bring us. The rest, I'll surely keep in mind.

    ((Meanwhile, I've built the file up again by (0) starting a new file (1) importing the actual tables from the corrupt one (compact) (2) importing the forms from an old backup which should be 'clean' enough (compact) (3) recreating the queries by copying (only) the SQL in design view from the original queries in the corrupt mdb (these couldn't be opened in design view no more, but after importing them in another mdb, they did...) (compact) (4) splitting => backend+frontend (5) frontend => mde (6) copy mde => workstations. ))

    ps just fyi...

    About preventing a user to close a form by other means than the close-button...
    I checked your post & it worked fine. Though, maybe also this variant, which I once used in one of my (few) app's, is worth mentioning:
    Private Sub Form_Close()
    cmdClose_Click
    End Sub
    Advantage: you don't need the variable & the user saves time: his first trial to 'get out' will work. And no matter how the form is closed, it's closed as if the close button was used.
    Disadvantage: I hope I didn't overlook any possibilities...

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

    Re: multi user solution works until now - thanks

    Hans,

    Thanks for the tip. It looks good. I'll be sure to try it.

    Regards,
    HansV

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multi user problems on a unix network (97/SR2)

    Jetcomp is available from Microsoft site.

    As for splitting databases, it has nothing to do with size, complexity, or # of users; I routinely split all my databases, especially those being used by others (be it one person or a bunch). For one thing, they are easier to maintain; I can modify the frontend, test it, and then distribute it all without touching the backend data. Split backend databases are also less prone to corruption, and lost data. In a network environment, you put the frontend on each workstation, which improves Access performance, and decreases network overhead.

    Of course, this has nothing to do with your problem. I'd start suspecting the network itself.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: multi user solution works until now - thanks

    Hans,

    You suggested
    <hr>Private Sub Form_Close()
    cmdClose_Click
    End Sub<hr>
    It looked promising, but when I tried it, I found a couple of problems:
    1. <LI>Error 2501 (action canceled) occurs when you close the form. You can get around this with an error handler that ignores error 2501.
      <LI>When you click the Close command button, the OnClick routine is run twice: first because you click the button, then again from the OnClose event. If the OnClick routine contains more than just the DoCmd.Close, this is annoying. You can get around it by creating a Boolean variable and setting it to True the first time, and exiting immediately if it is True. But your suggestion was meant to avoid the use of a Boolean variable...
    Still, it was worth trying.

    Regards,
    HansV

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    CmdClose_Click

    Well, Hans,

    so my solution was too simple to be true...
    I assume I indeed didn't get these problems because no extra code was included for the close-button. Mea culpa.

    Adn thanks again for giving your comments, also on this one - so at least no-one will use it without being warned about the consequences.

    Hans

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    1 mde for many users:+/-? Re: Multi user probl...

    Hey, Hans (and eventual other valuable members of this lounge)
    some time has passed since this thread was written and practicing your advice, in none of my databases here (maybe because multi user use was very rare), corruption has occurred ever since. Now, however, it's getting serious and I'll want to provide some database(s) for a couple of users. So... some additional questions...

    Given an mde frontend and a backend, both Access 97, with max. 2-6 simultaneous users.

    1. Are there additional advantages to put an mde copy on each user's desktop? The only one I can think of are network traffic (?) and the fact that you allow each of them to gather their own set of custom queries.
    (BTW: the latter seems to create the complication that each time you come up with a new version, you have to transfer those custom queries to the new copy...)

    2. Do I risk more corruption when using no record locking?
    I choose this strategy as we will probably never (... say never ...) work in the same records and because literature tells that this would probably lock several records at a time... which would probably provide more frustration than it would solve :-).

    3. Is it still usefull compacting the frontend on a regular basis? (It seems to me that compacting the backend remains as important...)

    (4. This goes to a new thread: workaround for corruption prone memo fields)

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

    1 mde for many users:+/-? Re: Multi user probl...

    1. In general, it is recommended to give each user his/her own copy of the front end database. Less chance of form corruption. This should be less important for mde databases than for mdb databases, but still...

    2. I don't think so. I don't set record locking in my databases and have no problems.

    3. Yes, even frontend databases increase in size.

  15. #15
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    1 mde for many users:+/-? Re: Multi user probl...

    Thanks!

Page 1 of 2 12 LastLast

Posting Permissions

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