Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locked tables (2000)

    Is there any way of finding out, from the front end.mdb, if any tables out of say 3 tables in a back end.mdb are locked?
    Is there a way of finding out who the other users are who are using the back end data.mdb?
    Regards,
    Peter

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locked tables (2000)

    I don't know of any way of determining which tables are locked other than trying to perform an edit operation, and that gets pretty dicey. Re your second question, there is a utility available from the MS web site called LDBview that will let you look at a lock file and see who is currently using a database. See <post#=88808>post 88808</post#> and subsequent posts in that thread for further information, and a VBA routine that will give you that sort of info using code.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locked tables (2000)

    What exactly are you trying to accomplish? I don't see what purpose is served by knowing if a table is locked. For that matter, I don't know what you mean by a table being locked unless the application is set up to use pessimistic locking.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    I need to change some fields in 2 tables. Chages can be: adding fields, deleting fields, changing spelling &/or changing ordinal position. If I just try and do these alterations while a table is being used, I get an error, which is handleable, but from then on I get all sorts of funny things happening. Best if I can see if the tables are in fact being used, before doing the alterations.
    There are only 4 - 6 users, so it would also be great if I could find out which ones were actually using the Database. I'll try Wendal's suggestion for that, unless there is a simpler way.
    Regards,
    Peter

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locked tables (2000)

    Are you really sure you want to change table designs on the fly in a back-end?

    Doing that means you would need to relink the front-ends, and in addition if any forms or reports (or queries) are dependant on the tables, then things start breaking and users get errors. Generally it's a recipe for disasters when back-end design changes are made without putting out a new front-end at the same time. There may well be other ways of accomplishing what you need to do without changing back-end tables. What is the reason that you want to change table designs?
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locked tables (2000)

    In addition to the caveats Wendell has pointed out, the fact is that it doesn't matter which table might be "locked". You simply CANNOT make any design changes to any object unless you can open the database exclusively. You can't work around that by only dealing with "unlocked" tables.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    Wendell, thanks for your earlier tip. You must have an encyclopaedic mind.
    Currently the db is split and on 1 computer. But about to got to approx 6 users, with the back end on a server I did a mock of the new set up and tried the code you suggested (using the .ldb file). It works a treat.
    As long as I check that no one else is using the back end Db, everything works just fine.
    The thing that concerns me is the reference in that code to

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    What happens when we move to Office 2002, or when Jet 4 becomes obsolete? Will the above line still work?
    Thanks for your info.
    Regards,
    Peter

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    Charlotte, I have used code that Wendell pointed me to, to see if there are any other users using the back end Db. It works great. Thanks for your input. You guys are incredible.
    Regards,
    Peter

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locked tables (2000)

    Your concern about the code is appropriate, as Jet is supposed to be history in a couple of releases down the road. Whether that will really happen is debateable, but the thrust toward XML storage suggests it really might go away. I haven't actually tested it with Access 2002, but I would expect it to work, as 2002 still uses Jet 4, although slightly modified if you are using the 2002 file format.

    I'm still concerned about your need to make design changes in the backend on an apparently frequent basis - in 10 years and 100s of production databases we've not encountered that kind of requirment. It suggests to me that your table design may not be general enough, and in any event will cause you problems down the road when something is changed that breaks the front-ends. Please don't misunderstand - you will always have a need to make table design changes as requirments for the database change, but they need to be done with considerable care once you have a database in production. And you nearly always have a need to deploy a modified version of the front-end when table design changes are made.
    Wendell

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    I take note of your comments, and am in awe of your knowledge. I have obviously designed this incorrectly. Enclosed is a diagram of the relationships. TblChurchCategories is on the back end, tblOwnCategories is on each of the front end computers in, a separate Db. The
    Attached Images Attached Images
    Regards,
    Peter

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locked tables (2000)

    I think what you really need in this situation is a linking table the contains two pointers, one to the PersonID, and a second to the CategoryID. That implies a separate table that is just ChurchCategories. The same could apply to OwnCategories - a table with the 20 possible categories, and a second linking table with two fields - PersonID and OwnCategoryID. If you want to control ordinal position in some way, you might add a third column (field) to the linking tables which provides an order value to sort on. With this kind of design, you shouldn't need to change table designs at all.

    Having had some experience with the development of church congregation management systems, is it fair to presume you are working on something along those lines?
    Wendell

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    Yep. Bayside Church Melbourne Austrtalia. We are growing rapidly and we need to expand this management system to work from a new server coming on line in mid Jan 03.
    I can't grasp what the solution you suggested is all about. How does a 'linking table' work.
    I guess that either you don't need sleep, or you must be close to Melbournes longitude.
    Regards
    Regards,
    Peter

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locked tables (2000)

    The idea of a linking table is simply to create one or more relationships between two other tables, and you have a field that points to table 1, typically using an integer that is the primary key in table 1, and a second field that points to table 2, again typically an integer that is the primary key for table 2. The primary key on the linking table is often set to the combination of the two fields so you don't end up with duplicates. If I have a bit of time in the next day or so, I'll pull a simple example database together and post it as an attachment.

    Actually I live about 40 degrees North and UTC - 7 (whatever longitude that is more or less) from Melbourne, but visited there many times during the 80s. And I have a daughter-in-law from Vermont (as in Victoria)
    Wendell

  14. #14
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked tables (2000)

    Thanks for the input. I tried your suggestion. See enclosed.
    But, say a person is member of 10 ChurchCategories and also a member of 5 OwnCategories. His listing in a query to return people & their categories will have 50 records, (showing unique records only). So I couldn
    Attached Images Attached Images
    Regards,
    Peter

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locked tables (2000)

    So use two subforms, one for churchcategories and one for owncategories. You don't need elaborate joins in a query behind those because they only involve a single table and are filtered by the master and child links between the parent and subform.
    Charlotte

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
  •