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. |
|
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
Because
Access does not locks records but locks pages of records, you basically have two options: Enlarge the child records size to a page-size or change the data files to record-level locking data file. The last 3 versions of ms-access (2000, 2002, and 2003) ALL support record, or row locking. You have to go back about 10 years to office 97 for a version that locks pages. So, your information is incorrect, and VERY old.... And, while we are at this, this issue is not a ms-access problem, but what database you are using (in this case, JET). As mentioned, JET for a long time does support row/record locking. You can use ms-access with JET, sql server, oracle etc. So, how things works is going to depend on what database you use, and not the development system called ms-access. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#13
|
|||
|
|||
Yes David, thank you, I did.
Forgive me not replying sooner - I was busy running acreoss the road. Yes, I went back to the drawing board and ended up using local temporary tables to store new records or changes. I now performs as I intended in the first place, and the client is happy with it! Thank you all for your thoughts (some of which I haven't yet read - will do so now) Cheers AliKwok "David C. Holley" wrote: Have you considered the suggestion that I made in another post? In the amount of time spent banging your head on the wall to get your fax software to work, you could have easily driven the fax across the street. (ie Sometimes is better to consider a different approach) AliKwok wrote: Thanks for your reply Albert. I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#14
|
|||
|
|||
The temp tables of course being the alternative. My suggestion was
geared to a back-end workaround that *MIGHT* minimize the number of changes. So I take it theres a good Irish Pub across the street? AliKwok wrote: Yes David, thank you, I did. Forgive me not replying sooner - I was busy running acreoss the road. Yes, I went back to the drawing board and ended up using local temporary tables to store new records or changes. I now performs as I intended in the first place, and the client is happy with it! Thank you all for your thoughts (some of which I haven't yet read - will do so now) Cheers AliKwok "David C. Holley" wrote: Have you considered the suggestion that I made in another post? In the amount of time spent banging your head on the wall to get your fax software to work, you could have easily driven the fax across the street. (ie Sometimes is better to consider a different approach) AliKwok wrote: Thanks for your reply Albert. I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#15
|
|||
|
|||
No, Opium Den ;0]
"David C. Holley" wrote: The temp tables of course being the alternative. My suggestion was geared to a back-end workaround that *MIGHT* minimize the number of changes. So I take it theres a good Irish Pub across the street? AliKwok wrote: Yes David, thank you, I did. Forgive me not replying sooner - I was busy running acreoss the road. Yes, I went back to the drawing board and ended up using local temporary tables to store new records or changes. I now performs as I intended in the first place, and the client is happy with it! Thank you all for your thoughts (some of which I haven't yet read - will do so now) Cheers AliKwok "David C. Holley" wrote: Have you considered the suggestion that I made in another post? In the amount of time spent banging your head on the wall to get your fax software to work, you could have easily driven the fax across the street. (ie Sometimes is better to consider a different approach) AliKwok wrote: Thanks for your reply Albert. I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#16
|
|||
|
|||
How were the brownies? Did you say hi to Rush?
AliKwok wrote: No, Opium Den ;0] "David C. Holley" wrote: The temp tables of course being the alternative. My suggestion was geared to a back-end workaround that *MIGHT* minimize the number of changes. So I take it theres a good Irish Pub across the street? AliKwok wrote: Yes David, thank you, I did. Forgive me not replying sooner - I was busy running acreoss the road. Yes, I went back to the drawing board and ended up using local temporary tables to store new records or changes. I now performs as I intended in the first place, and the client is happy with it! Thank you all for your thoughts (some of which I haven't yet read - will do so now) Cheers AliKwok "David C. Holley" wrote: Have you considered the suggestion that I made in another post? In the amount of time spent banging your head on the wall to get your fax software to work, you could have easily driven the fax across the street. (ie Sometimes is better to consider a different approach) AliKwok wrote: Thanks for your reply Albert. I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Entry Into Multiple Tables Using One Form | Linda | Using Forms | 2 | October 4th, 2005 07:40 PM |
Creating a data entry form from a query | Kathryn | New Users | 1 | June 21st, 2005 09:12 PM |
format data displayed on Excel data entry form | Bob, too | Setting up and Configuration | 0 | May 19th, 2005 08:26 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |