Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unhappy Unanswered: MS-Access 2007 - network interruption issue

    hi,

    I have an ms access application where i have front end and back end ... When i try to run a command on a specific form i get

    " your network was interupted .To continue close and then open again "

    I have front and backend running on same machine and in the same folder

    Basically i am closing off the stock at the end of every month so here is the code that is in use:
    Code:
    Private Sub Command5_Click()
    On Error GoTo Err_Command5_Click
    
    Dim stDocName As String
    
    stDocName = "Cl Closing 2 Opening Qry"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Exit_Command5_Click:
    Exit Sub
    
    Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
    End Sub
    And in the main form i added following:

    Code:
    Dim dbsAlwaysOpen As DAO.Database
    
    Private Sub Form_Close()
    Set dbsAlwaysOpen = Nothing
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    Set dbsAlwaysOpen = OpenDatabase("H:\EnglishBiscuit\English Biscuits.mdb", False)
    End Sub
    Any help please?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's not a good idea to have something like this:
    Dim dbsAlwaysOpen As DAO.Database
    not in a specific form's event (ie. in the global section of a form or in it's onload/onopen event.)

    You don't really want to set dbsAlwaysOpen when a form opens. Your linked tables handle the connection part so there should be no need to try and 'force' a connection using a recordset such as 'Dim dbsAlwaysOpen...' to keep it open in a form's loading type event (ie. in the Onload, OnOpen or not in any specific event.)

    You should only Dim dbsAlwaysOpen As DAO.Database or something like Dim xx as ADODB.Recordset, ONLY when you want to open a recordset based upon some event in the form (ie. they click an Update button.) - Then you open the recordset (based upon a linked table), update the records, and CLOSE the recordset.

    Dim a recordset of any kind in the form's property so to speak (ie. under/above the Options Explicit and not on a specific event), will cause connection problems when that form is opened or you close it and open another form.

    Instead, use the advantage of the linked tables and design your form based upon the linked table. Then, if you need to open a different recordset (based on another linked table) in your form's code somewhere, Dim the recordset in a SPECIFIC properties of whatever event (ie. OnClick of event of a button or AfterUpdate event of some text contro, etc....)

    Ideally, you want to dim your recordset, open it, do it's thing, and then close it as soon as possible (ie. you don't want to dim, load and keep a recordset loaded such as what your doing when the form opens - or try to connect to another mdb which is already connected through linked tables.) You don't want to dim XXX as DAO.Recordset in a 'global' , Onload, or OnOpen type event for a form. This will cause a connection issue and lock tables.


    1. Remove this code....
    Dim dbsAlwaysOpen As DAO.Database

    Private Sub Form_Close()
    Set dbsAlwaysOpen = Nothing
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Set dbsAlwaysOpen = OpenDatabase("H:\EnglishBiscuit\English Biscuits.mdb", False)
    End Sub

    And instead, use a linked table to base the form off of. If you already have linked tables in your frontend from the backend for English Biscuits.mdb, this code is essentially trying to 'force' another connection to a database which is already open/connected via linked tables.
    Last edited by pkstormy; 10-11-09 at 14:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I have to ask what is the reasoning behind
    Dim dbsAlwaysOpen As DAO.Database
    in the form's global section?

    ie. Why are you trying to establish a connection to a another database (in a form) versus just using the linked table (from the English Biscuits.mdb) as the 'recordsource' for your form?
    Last edited by pkstormy; 10-11-09 at 14:32.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Indeed. No doubt this is the source of the problem(s).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •