How let Access release the ODBC










0















I have 2 access databases, one in use as CRM and the other only holds linked tables to a firebird database using ODBC. This firebird database (fdb) is only capable to allow access to one user.



When updating the tables by the CRM through ODBC, the ODBC connection (Firebird) is not released, which means that an other application which needs access cannot open the database. The ODBC connection is only released when the CRM is closed.



Dim dba as database
Dim strODBCname as string
strODBCname = "OSF_ODBC.accdb"
Set dbs = OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)
dbs.execute .... (etc.)


And after all record R/W are completed



set dbs = nothing


Is there an other way to enforce the release of the ODBC connection?



Peter










share|improve this question


























    0















    I have 2 access databases, one in use as CRM and the other only holds linked tables to a firebird database using ODBC. This firebird database (fdb) is only capable to allow access to one user.



    When updating the tables by the CRM through ODBC, the ODBC connection (Firebird) is not released, which means that an other application which needs access cannot open the database. The ODBC connection is only released when the CRM is closed.



    Dim dba as database
    Dim strODBCname as string
    strODBCname = "OSF_ODBC.accdb"
    Set dbs = OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)
    dbs.execute .... (etc.)


    And after all record R/W are completed



    set dbs = nothing


    Is there an other way to enforce the release of the ODBC connection?



    Peter










    share|improve this question
























      0












      0








      0








      I have 2 access databases, one in use as CRM and the other only holds linked tables to a firebird database using ODBC. This firebird database (fdb) is only capable to allow access to one user.



      When updating the tables by the CRM through ODBC, the ODBC connection (Firebird) is not released, which means that an other application which needs access cannot open the database. The ODBC connection is only released when the CRM is closed.



      Dim dba as database
      Dim strODBCname as string
      strODBCname = "OSF_ODBC.accdb"
      Set dbs = OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)
      dbs.execute .... (etc.)


      And after all record R/W are completed



      set dbs = nothing


      Is there an other way to enforce the release of the ODBC connection?



      Peter










      share|improve this question














      I have 2 access databases, one in use as CRM and the other only holds linked tables to a firebird database using ODBC. This firebird database (fdb) is only capable to allow access to one user.



      When updating the tables by the CRM through ODBC, the ODBC connection (Firebird) is not released, which means that an other application which needs access cannot open the database. The ODBC connection is only released when the CRM is closed.



      Dim dba as database
      Dim strODBCname as string
      strODBCname = "OSF_ODBC.accdb"
      Set dbs = OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)
      dbs.execute .... (etc.)


      And after all record R/W are completed



      set dbs = nothing


      Is there an other way to enforce the release of the ODBC connection?



      Peter







      ms-access access-vba odbc access ms-access-2016






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 13 '18 at 12:06









      ArtArt

      10919




      10919






















          2 Answers
          2






          active

          oldest

          votes


















          0














          I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:



          Const ODBC_NAME As String = "OSF_ODBC.accdb"

          With CreateObject("Access.Application")
          .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
          .CurrentDb.Execute ...
          .CurrentDb.Close
          .Quit
          End With


          This should release the connections for sure.






          share|improve this answer






























            0














            I don't know which version of Access you are using. But before you create a new application object, you should try



            Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
            Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


            or the next level up:



            dim dbe=dao.dbengine
            set dbe=CreateObject("dao.dbengine")
            Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


            More globally, you can modify the registry settings that control ODBC connection caching.






            share|improve this answer






















              Your Answer






              StackExchange.ifUsing("editor", function ()
              StackExchange.using("externalEditor", function ()
              StackExchange.using("snippets", function ()
              StackExchange.snippets.init();
              );
              );
              , "code-snippets");

              StackExchange.ready(function()
              var channelOptions =
              tags: "".split(" "),
              id: "1"
              ;
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function()
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled)
              StackExchange.using("snippets", function()
              createEditor();
              );

              else
              createEditor();

              );

              function createEditor()
              StackExchange.prepareEditor(
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader:
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              ,
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              );



              );













              draft saved

              draft discarded


















              StackExchange.ready(
              function ()
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280704%2fhow-let-access-release-the-odbc%23new-answer', 'question_page');

              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              0














              I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:



              Const ODBC_NAME As String = "OSF_ODBC.accdb"

              With CreateObject("Access.Application")
              .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
              .CurrentDb.Execute ...
              .CurrentDb.Close
              .Quit
              End With


              This should release the connections for sure.






              share|improve this answer



























                0














                I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:



                Const ODBC_NAME As String = "OSF_ODBC.accdb"

                With CreateObject("Access.Application")
                .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
                .CurrentDb.Execute ...
                .CurrentDb.Close
                .Quit
                End With


                This should release the connections for sure.






                share|improve this answer

























                  0












                  0








                  0







                  I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:



                  Const ODBC_NAME As String = "OSF_ODBC.accdb"

                  With CreateObject("Access.Application")
                  .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
                  .CurrentDb.Execute ...
                  .CurrentDb.Close
                  .Quit
                  End With


                  This should release the connections for sure.






                  share|improve this answer













                  I've currently no idea how to explicitely release the ODBC connections of the current session, but you could open the database in a second Access application session (a second MsAccess.exe in memory) and release this after work:



                  Const ODBC_NAME As String = "OSF_ODBC.accdb"

                  With CreateObject("Access.Application")
                  .OpenCurrentDatabase ValidatePath(CurrentProject.Path, False) & ODBC_NAME
                  .CurrentDb.Execute ...
                  .CurrentDb.Close
                  .Quit
                  End With


                  This should release the connections for sure.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 13 '18 at 14:19









                  Unhandled ExceptionUnhandled Exception

                  975159




                  975159























                      0














                      I don't know which version of Access you are using. But before you create a new application object, you should try



                      Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
                      Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                      or the next level up:



                      dim dbe=dao.dbengine
                      set dbe=CreateObject("dao.dbengine")
                      Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                      More globally, you can modify the registry settings that control ODBC connection caching.






                      share|improve this answer



























                        0














                        I don't know which version of Access you are using. But before you create a new application object, you should try



                        Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
                        Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                        or the next level up:



                        dim dbe=dao.dbengine
                        set dbe=CreateObject("dao.dbengine")
                        Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                        More globally, you can modify the registry settings that control ODBC connection caching.






                        share|improve this answer

























                          0












                          0








                          0







                          I don't know which version of Access you are using. But before you create a new application object, you should try



                          Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
                          Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                          or the next level up:



                          dim dbe=dao.dbengine
                          set dbe=CreateObject("dao.dbengine")
                          Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                          More globally, you can modify the registry settings that control ODBC connection caching.






                          share|improve this answer













                          I don't know which version of Access you are using. But before you create a new application object, you should try



                          Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet) 
                          Set dbs = wrkAcc.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                          or the next level up:



                          dim dbe=dao.dbengine
                          set dbe=CreateObject("dao.dbengine")
                          Set dbs= dbe.OpenDatabase(ValidatePath(CurrentProject.Path, False) & strODBCname)


                          More globally, you can modify the registry settings that control ODBC connection caching.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 16 '18 at 7:46









                          daviddavid

                          1,3121117




                          1,3121117



























                              draft saved

                              draft discarded
















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid


                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.

                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function ()
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280704%2fhow-let-access-release-the-odbc%23new-answer', 'question_page');

                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Top Tejano songwriter Luis Silva dead of heart attack at 64

                              政党

                              天津地下鉄3号線