none
Persistent connection - Bound Form or the The DAO OpenDatabase Method - Pros and Cons RRS feed

  • Question

  • Hi

    What would you say:

    Persistent connection - Bound Form or the The DAO OpenDatabase Method - Pros and Cons.


    Cheers // Peter Forss Stockholm

    Thursday, January 3, 2019 10:31 AM

Answers

  • One significant downside to the idea of using an OpenDatabase method is row/page locking.

    If you want to use row locking vs page locking in Access? It turns out that ONLY MS access can open a JET/ACE database with row locking. So using VB6, c++, vb.net, c# or any other system, you cannot open access databases with row locking (only page locking is supported).

    It turns out that if you let MS Access open the database FIRST with OpenDatabase method as opposed to some form, or dao recordset of the current db object? Well, then you opening the database with page locking. This means you made the connection using a DAO object, and not an Access object.

    Now, it is somewhat unlikely the above will affect you, but do keep in mind that using OpenDatabase method in your start up code before any form or anything else in Access opens the back end will result in this “quirk” of overriding your row locking vs page locking setting in Access.

    Whoever gets in FIRST actually sets the locking used.

    Since this setting can ONLY be set by MS Access (vb.net, VB6, other applications cannot set this), then the results are different if you use OpenDatabase before anything else gets it hands on the back end. The result as noted is in effect to ignore the page locking settings in Access.

    Ignoring the locking issue? Well then open database method is most certainly preferred for several reasons:

    First, you don’t have to worry about some form launching, or loading or even the name. So your persisting connection will work for any application. This code can thus be “generic” – no hard coding of ANY object is required. And no worry about some form name. However you ARE suppling the back end location here – that can be fished from a linked table, but you have to grab/get the path to the back end to do this. So while no forms hardcoding, you are and will supply the path to the back end – so where and how you get that information could very well make this solution somewhat less desirable.

    And some often use a global recordset, but again this means you have to select or “know” about some table name.

    So all in all, I do prefer the OpenDatabase method. However, in the past I used a global reocrdset.

    The one big downside of using OpenDatabase is it overrides the MS Access settings of row locking unless some form, or recordset been opened by access before you create that persisting connection that way.

    If it was not for this “quirk” of OpenDatabase overriding MS access row locking setting, then hands down I would pick OpenDatabase method.

    However, ONE big advantage of a hidden form?

    The form idea is somewhat more robust, since an execution error will BLOW out the global database object you created, but will NEVER blow out an open form.

    If you using an accDE, then it don’t matter, since global variables are never re-set in an accDE, and they ALWAYS persist for the duration of that session. In an accDB, one missed error, and ALL your globals are now gone – including your persistent connection.

    My personal preference is to use a recordset in place of OpenDatabase, because I ALWAYS deploy using an accDE, and I don’t want OpenDatabase to mess with my row lock settings.

    I also in ALL of my applications have a back end table with Version Number, and some config options that I have for all my applications.

    So I can make TWO VERY big assumptions here.

    #1 – the table I am looking for will always exist.

    #2 – the application is an accDE. A accDE will NEVER re-set globals – even for un-handled errors!!! So my persisting connection can never go away.

    So which approach is best?

    Well, as I stated, it depends.

    If you using an accDE, then you can risk a global var, since VBA variables never get re-set, - even without error handlings.

    If you using a accDB, then the forms approach is likely better, since ANY un-handled error will blow out your global vars, and that means you just lost your persistent connection, but with an open form, they never get blown out with a accDB and errors due to VBA.

    So a form is MORE robust when using an accDB. However, when using an accDE, then an open form, or a global database object cannot be argued to be more or less reliable than the other. So in this case (accDE), I would use the OpenDatabase idea. However, because of the row/page locking issue, then I actually over a form, and over a global database object prefer a global reocrdset. And as noted, I need those “settings” such as version number, and things like default area code, default city, default state etc.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by ForssPeterNova Thursday, January 3, 2019 11:37 AM
    Thursday, January 3, 2019 11:06 AM

All replies

  • One significant downside to the idea of using an OpenDatabase method is row/page locking.

    If you want to use row locking vs page locking in Access? It turns out that ONLY MS access can open a JET/ACE database with row locking. So using VB6, c++, vb.net, c# or any other system, you cannot open access databases with row locking (only page locking is supported).

    It turns out that if you let MS Access open the database FIRST with OpenDatabase method as opposed to some form, or dao recordset of the current db object? Well, then you opening the database with page locking. This means you made the connection using a DAO object, and not an Access object.

    Now, it is somewhat unlikely the above will affect you, but do keep in mind that using OpenDatabase method in your start up code before any form or anything else in Access opens the back end will result in this “quirk” of overriding your row locking vs page locking setting in Access.

    Whoever gets in FIRST actually sets the locking used.

    Since this setting can ONLY be set by MS Access (vb.net, VB6, other applications cannot set this), then the results are different if you use OpenDatabase before anything else gets it hands on the back end. The result as noted is in effect to ignore the page locking settings in Access.

    Ignoring the locking issue? Well then open database method is most certainly preferred for several reasons:

    First, you don’t have to worry about some form launching, or loading or even the name. So your persisting connection will work for any application. This code can thus be “generic” – no hard coding of ANY object is required. And no worry about some form name. However you ARE suppling the back end location here – that can be fished from a linked table, but you have to grab/get the path to the back end to do this. So while no forms hardcoding, you are and will supply the path to the back end – so where and how you get that information could very well make this solution somewhat less desirable.

    And some often use a global recordset, but again this means you have to select or “know” about some table name.

    So all in all, I do prefer the OpenDatabase method. However, in the past I used a global reocrdset.

    The one big downside of using OpenDatabase is it overrides the MS Access settings of row locking unless some form, or recordset been opened by access before you create that persisting connection that way.

    If it was not for this “quirk” of OpenDatabase overriding MS access row locking setting, then hands down I would pick OpenDatabase method.

    However, ONE big advantage of a hidden form?

    The form idea is somewhat more robust, since an execution error will BLOW out the global database object you created, but will NEVER blow out an open form.

    If you using an accDE, then it don’t matter, since global variables are never re-set in an accDE, and they ALWAYS persist for the duration of that session. In an accDB, one missed error, and ALL your globals are now gone – including your persistent connection.

    My personal preference is to use a recordset in place of OpenDatabase, because I ALWAYS deploy using an accDE, and I don’t want OpenDatabase to mess with my row lock settings.

    I also in ALL of my applications have a back end table with Version Number, and some config options that I have for all my applications.

    So I can make TWO VERY big assumptions here.

    #1 – the table I am looking for will always exist.

    #2 – the application is an accDE. A accDE will NEVER re-set globals – even for un-handled errors!!! So my persisting connection can never go away.

    So which approach is best?

    Well, as I stated, it depends.

    If you using an accDE, then you can risk a global var, since VBA variables never get re-set, - even without error handlings.

    If you using a accDB, then the forms approach is likely better, since ANY un-handled error will blow out your global vars, and that means you just lost your persistent connection, but with an open form, they never get blown out with a accDB and errors due to VBA.

    So a form is MORE robust when using an accDB. However, when using an accDE, then an open form, or a global database object cannot be argued to be more or less reliable than the other. So in this case (accDE), I would use the OpenDatabase idea. However, because of the row/page locking issue, then I actually over a form, and over a global database object prefer a global reocrdset. And as noted, I need those “settings” such as version number, and things like default area code, default city, default state etc.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by ForssPeterNova Thursday, January 3, 2019 11:37 AM
    Thursday, January 3, 2019 11:06 AM
  • Hi Albert

    Thank you for a clarifying answer.

    I will read it over and over again and consider what to do.


    Cheers // Peter Forss Stockholm

    Thursday, January 3, 2019 11:39 AM
  • Hi

    I decided to use bound Form.

    I cant use the DAO OpenDatabase method. It is stopping me from use code like the one below:

    Dim lngStore As Long
    lngStore = Forms![Brewing]![FillNo]
    Me.Requery
    Forms![Brewing].Form.Recordset.FindFirst "[FillNo]= " & lngStore


    Cheers // Peter Forss Stockholm

    Saturday, January 5, 2019 9:22 AM
  • I can see no reason as to for what reason opening a connection to the database would prevent the above. It’s possible your openDatabase command is opening the database “exclusive”, but other than this issue, such a choice should not change anything.

    I would assume that in your VBA start up code (most likely in a standard access code module, you have this:

       Dim gblDataPersist   As DAO.Database

       ‘ above MUST be defined in the code module – not a sub or VBA routine

       Sub MyStartupCode

       Dim strPath          As String

       strPath = "full path to back end dataase goes here"

       Set gblDataPersist = DBEngine(0).OpenDatabase(strPath)

       ‘ more of your startup code here – uselly a truck load of

       ‘ things I set

         then – open your main form

      So above code will run on startup. After above, then your main form, or whatever you display to users on start-up can run.

    I am not aware that doing the above will affect ANY existing code.

    As I stated, the only down side of above is that it will override the row locking feature as opposed to “page” locking.

    I seen applications do the above for 10+ years, - never a issue or problem, or anything I can recall that will change or effect any existing code in any way I can think of.

    However, as noted, using a hidden form is a fine solution, since as noted, it will work for a accDB - even without error handling.

    If you use a global database object, then as noted, it has to be defined in a non forms code module, and the "dim" statement of course has to be outside of any code routine - it has to be placed at the start to the code module to be a true global variable that persists for the session.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Monday, January 7, 2019 12:57 AM
  • I can't remember the details now, but for one project I tried implementing FMS' The DAO OpenDatabase Method to create persistent connections and it didn't work properly and caused some side effects.  Long story short, I switched back to my tried tested form based persistent connection and never looked back.  It is extremely easy to setup (make a dummy linked table with a single column, create a form based on that table, open the form at the startup of your database in hidden mode - that's it!)  and it ALWAYS works in all cases, so why mess with it. 

    The KISS rule applies here!


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, January 7, 2019 2:15 AM
  • Much agree - I upvoted this. While  pointed out the pros + cons of each approach, this approach is likely the most reliable, simple and trouble free.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Monday, January 7, 2019 4:31 AM