locked
Appropriate DAO library RRS feed

  • Question

  • In Access 2010, if I am referencing an Access 2003 .mdb file using DAO should I be using the Microsoft Office 14.0 Access database engine Object Library or the Microsoft DAO 3.6 Object Library?

    Also I note that DAO 3.6 does not support row level locking (https://support.microsoft.com/en-us/kb/306435) Does using the Office 14.0 Access database engine object library overcome this limitation?  Thanks.

    -Bruce

    Friday, November 4, 2016 7:27 PM

Answers

  • Bruce - You should be using the ACE (Microsoft Office 14.0 Access database engine Object Library). It can read an MDB file. And that should also allow row locking.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Bruce Hulsey Friday, November 4, 2016 9:13 PM
    Friday, November 4, 2016 7:35 PM
  • When you use DAO in Access, it DOES turn on and allow row locking. So the KB article you refer to is ONLY in regards to developers using DAO and NOT in regards to when Access uses DAO. So Access of course does allow the use of row locking when using DAO.

    So a distinguishing between using DAO and Access is required here. Access most certainly can and does turn on row locking when using DAO (and does so when using DAO 3.6). So it’s WHEN you are using DAO without Access is the context of that KB article you refer to.

    However, it is RARE you will need row locking turned on. Don’t confuse the ability of Access forms to “lock” the current record being edited vs that of DAO row locking. Access can use a page lock, or a row lock. I never had to use row locking in Access for 16 or more years. So if you set a form to lock the current record, then depending on the setting for page vs row lock, then Access will simply lock the page.

    If you use row locking, then you will experience a RATHER massive increase in “bloating” of the database during normal operations. This is due to the fact that JET/ACE never did have real row locking – what JET/ACE does is “fake” row locking by simply padding a record to ALWAYS be one database page size and thus in this scenario because access has page locking, the you get row locking in in a roundabout way– but at a significant cost in bloat.

    Keep in mind that since Access 2007 (about 10 years now), you do NOT require and SHOULD NOT have a reference to DAO – it is now included in the Access database engine (ACE). This include when you open and use an mdb file. So you just need the Microsoft Office 14.0 Access database engine object. However, if you do open an mdb file with A2010, then you will see and find that the DAO 3.6 reference does exist and that’s just fine. (but you could remove DAO and then add the ACE reference -  you find things work just fine).

    So I would not go to the trouble to remove the DAO 3.6 reference and change it over to the ACE reference WHEN using mdb files – it’s not really required. Regardless row locking is STILL available with both DAO 3.6 and ACE WHEN you using JET/ACE from Access.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Friday, November 4, 2016 7:53 PM

All replies

  • Bruce - You should be using the ACE (Microsoft Office 14.0 Access database engine Object Library). It can read an MDB file. And that should also allow row locking.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Bruce Hulsey Friday, November 4, 2016 9:13 PM
    Friday, November 4, 2016 7:35 PM
  • When you use DAO in Access, it DOES turn on and allow row locking. So the KB article you refer to is ONLY in regards to developers using DAO and NOT in regards to when Access uses DAO. So Access of course does allow the use of row locking when using DAO.

    So a distinguishing between using DAO and Access is required here. Access most certainly can and does turn on row locking when using DAO (and does so when using DAO 3.6). So it’s WHEN you are using DAO without Access is the context of that KB article you refer to.

    However, it is RARE you will need row locking turned on. Don’t confuse the ability of Access forms to “lock” the current record being edited vs that of DAO row locking. Access can use a page lock, or a row lock. I never had to use row locking in Access for 16 or more years. So if you set a form to lock the current record, then depending on the setting for page vs row lock, then Access will simply lock the page.

    If you use row locking, then you will experience a RATHER massive increase in “bloating” of the database during normal operations. This is due to the fact that JET/ACE never did have real row locking – what JET/ACE does is “fake” row locking by simply padding a record to ALWAYS be one database page size and thus in this scenario because access has page locking, the you get row locking in in a roundabout way– but at a significant cost in bloat.

    Keep in mind that since Access 2007 (about 10 years now), you do NOT require and SHOULD NOT have a reference to DAO – it is now included in the Access database engine (ACE). This include when you open and use an mdb file. So you just need the Microsoft Office 14.0 Access database engine object. However, if you do open an mdb file with A2010, then you will see and find that the DAO 3.6 reference does exist and that’s just fine. (but you could remove DAO and then add the ACE reference -  you find things work just fine).

    So I would not go to the trouble to remove the DAO 3.6 reference and change it over to the ACE reference WHEN using mdb files – it’s not really required. Regardless row locking is STILL available with both DAO 3.6 and ACE WHEN you using JET/ACE from Access.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Friday, November 4, 2016 7:53 PM
  • Thanks all.  This clarifies things a good bit.

    -Bruce

    Friday, November 4, 2016 9:13 PM