Locked tables (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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 and subsequent posts in that thread for further information, and a VBA routine that will give you that sort of info using code.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Charlotte, yes I was missing the obvious. You

Reply | Quote

What about using a subquery to return a unique list of PersIDs in a union of the two category tables. Use the subquery to filter the parent form.

Reply | Quote

Thanks Charlotte. Tried that. Doesn

Reply | Quote

I’m sorry, but apparently I’m not understanding you at all. Are you saying that you can’t do something like this?

SELECT * FROM BayRole WHERE BayRole.ID WHERE ( EXISTS (SELECT DISTINCT PersID FROM tblChurchCategories UNION SELECT DISTINCT PersID FROM tblOwnCategories) ) = True

This assumes that BayRole is actually a table and not a query, and that it is the source for your parent form. That certainly should return only a single record in BayRole for each Person who is in any category.

Reply | Quote

Charlotte, thanks heaps, I never knew the EXISTS SQL predicate existed. What you have proposed would handle the filtering part.
However I couldn

Reply | Quote

Charlotte, I found the problem. Can

Reply | Quote

Sorry – I got distracted by a mini-mega crisis and wasn’t able to respond for a couple of days. After looking at your form, IMHO you are probably trying to display too much data on one form. We have solved this kind of problem by using a tree-view control to display Categories, with members of each category listed beneath the category. Then when you click a particular name, it displays the info about that person (including any categories they are assigned too) in a form. I’ll see if I can come up with a screen shot to show you the idea, but it may take a day or two. In the meanwhile, it looks like you have the idea of a linking table sorted out.

Reply | Quote

Well, it took less time that I thought to locate what I was after. The attached screen shot shows the TreeView on the left with Committees and Activities, and the people who belong to each, and on the right I displayed one of the people and the sub-form used to do a one-off assignment of someone to a particular group. Another form is used to make mass assignments when there are substantial changes in the members of a group, for example at the beginning of a new year. Hope this makes some sense.

Reply | Quote

Wendell, point taken. And Oh yeah! I have really squeezed a lot in on that page. However it has been working very well for about 6 years now. It seems to fit everyone

Reply | Quote

Ah, sorry, I didn’t realize that this was an ongoing project that had been deployed for a long while. That does change the nature of what you can easily do. As to the query that is giving you trouble, I noticed that you have fewer parenthesis in the second query than you do in the first – probably not the problem, but you could try it anyhow:

SELECT Bayrole.* FROM Bayrole WHERE (EXISTS (SELECT PersID FROM qryTotCat1 UNION SELECT PersID FROM qryTotCat2));

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote