Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    <P ID="edit" class=small>(Edited by WendellB on 10-Jan-03 05:26. Move Dr. Watson dump to an attachment to reduce message length)</P>Hi

    I wonder if anyone can help me?

    I'm developing a client-server application that uses Access as both the front-end and back-end databases, where the tables are linked in-between.

    I can do most things fine except for being able to view a linked tables data by double-clicking on the table - this simply does not open, however I can run a query against the attached table to display its records which works fine. If create a new database and set it up the same I can view the linked tables, however if I create empty databases and import all the objects from my app into them I still get the same behaviour.

    Every now and again I'll get a message saying Too Many Transactions, the code module must be closed before you can continue but I cannot find any running code to close. Most of my code uses ADO rather than DAO, the version I'm using is 2.6

    This morning I did not get the above message but found that Access crashed spectacularly (see DrWatson log below)

    I have removed and reinstalled the application but this did not seem to make any difference.

    I'd really appreciate any help/suggestions to fix this as I'm losing lots of time and productivity as a result

    Many thanks in advance

    Nigel Bell
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    Hi Nigel,
    We see this kind of behavior every once in a while in Access - there appears to be a code module running, but you can't find anything that appears to be open. In those cases, we shut down Access and restart and that usually cures the problem - for a while. The most common cause seems to be a situation where you declare objects but forget to destroy them cleanly at the end, so do a careful check to make sure that things are getting set to Nothing at the end. We run in an environment very similar to yours much of the time, and it can be an occasional annoyance, but it only happens to us rarely these days. Hope this helps and makes sense.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    Hi Wendell

    I have the following commericial code and trying to learn and understand good housekeeping techniques as discussed.

    AM I missing dbs and rst = nothing ?

    Thanks, John

    ===========================
    Private Sub butInstall_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim xType As Variant
    Dim myApp As New Access.Application
    Dim xC As Integer, xn As Integer, xd As Integer

    On Error GoTo ErrorHandler

    #If ezDemo Then
    MsgBox ("Not available in the demo version"): Exit Sub
    #End If

    xC = 0: xn = 0: xd = 0
    If IsNull(Me.PatchMDB) Then MsgBox "File not selected": GoTo Done
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("ezf_CompareMDBs")
    With rst
    If MsgBox("Have you backed up your MDB file and are ready to install the patch?", vbYesNo, "Ready to Install?") = vbYes Then
    'Proceed with installation
    While Not rst.EOF
    If !Name <> "ezf_PatchInstaller" And !Name <> "ezf_CompareMDBs" Then
    xType = Switch(!ObjType = "Table", acTable, !ObjType = "Query", acQuery, !ObjType = "Module", acModule, !ObjType = "Macro", acMacro, !ObjType = "Report", acReport, !ObjType = "Form", acForm)
    Select Case !Status
    Case "New"
    DoCmd.TransferDatabase acExport, "Microsoft Access", Me.PatchMDB, xType, !Name, !Name
    xn = xn + 1
    Case "Changed"
    DoCmd.TransferDatabase acExport, "Microsoft Access", Me.PatchMDB, xType, !Name, !Name
    xC = xC + 1
    End Select
    End If
    rst.MoveNext
    Wend
    .MoveFirst
    MsgBox xn & " new objects added. " & vbCrLf & xC & " objects replaced."
    MsgBox "At this time another instance of Access will be opened with the database being patched started. The objects to be deleted will then be deleted."
    myApp.OpenCurrentDatabase Me.PatchMDB
    While Not rst.EOF
    If !Name <> "ezf_PatchInstaller" And !Name <> "ezf_CompareMDBs" Then
    xType = Switch(!ObjType = "Table", acTable, !ObjType = "Query", acQuery, !ObjType = "Module", acModule, !ObjType = "Macro", acMacro, !ObjType = "Report", acReport, !ObjType = "Form", acForm)
    Select Case !Status
    Case "Not in current"
    myApp.DoCmd.DeleteObject xType, !Name
    xd = xd + 1
    End Select
    End If
    rst.MoveNext
    Wend
    MsgBox xd & " objects deleted"
    myApp.CloseCurrentDatabase
    Set myApp = Nothing

    Else
    'do not proceed with installation
    MsgBox "Installation of Patch aborted"
    End If
    GoTo Done
    ErrorHandler:
    If Err.Number = 3011 Then
    MsgBox !Name & " does not exist and cannot be deleted"
    xd = xd - 1
    Resume Next
    End If
    MsgBox Err.Description

    Done:
    'Code like this could be used if only the objects to be deleted are tables and queries
    'Dim curDB As Database, td As TableDefs, SQLStmt As String
    'Set curDB = DBEngine.Workspaces(0).OpenDatabase("c:win95deskto pcad.mdb")
    'curDB.CreateTableDef.DeleteObject acTable, "Change IncNum"
    'Set td = curDB.TableDefs
    'td.Delete "Change IncNum"
    End With
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    This is in response to Support4John's question, not to the original post.

    John,

    You have 3 object variables in your code: dbs (database) , rst (recordset) and myApp (application).
    You should always close a recordset.
    You should always quit other applications you start (unless you want the user to continue using it interactively.
    You should always set object variables to Nothing.
    And you should make sure that this is done even if an error occurs; in your code this can be accomplished by putting it in the section after Done:
    To prevent an error during this housekeeping causing an endless loop, prefix the instruction by On Error Resume Next.

    So the end of your code could look like this (the Set myApp = Nothing instruction has been moved here too):

    Done:
    'Code like this could be used if only the objects to be deleted are tables and queries
    'Dim curDB As Database, td As TableDefs, SQLStmt As String
    'Set curDB = DBEngine.Workspaces(0).OpenDatabase("c:win95deskto pcad.mdb")
    'curDB.CreateTableDef.DeleteObject acTable, "Change IncNum"
    'Set td = curDB.TableDefs
    'td.Delete "Change IncNum"
    End With
    ' No error handling here
    On Error Resume Next
    ' Close recordset
    rst.Close
    ' Release recordset object
    Set rst = Nothing
    ' Release database object
    Set dbs = Nothing
    ' Quit other instance of Access
    myApp.Quit acQuitSaveNone
    ' Release application object
    Set myApp = Nothing
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    Hi Hans

    Thanks for the clinic.

    Is there a utility that can be run that checks object vars to make sure they are being closed?

    If not, could I search for all dim's like:

    as object
    as recordset
    as database
    as control
    as lable
    as querydef
    as tabledef
    as property
    as container
    as document
    as form
    etc

    and make sure if there being use that they are properly closed as you tutored?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    John,

    Unfortunately I don't know of such a utility - let's hope someone else does!

    Since there are many different types of objects, looking at declarations of each type is a lot of work. Instead, you can search for:
    <UL><LI>Set (as a whole word) - object variables are assigned a value by Set varname = value, with one exception:
    <LI>For Each - you can loop through collections of objects this way, for instance
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    ... (code to open the recordset)
    For Each fld In rst.Fields
    ... (code to do something with the fields)
    Next fld[/list]All object variables should be set to Nothing; whether they have to be closed or quit depends on the type:
    <UL><LI>If you use something like OpenDatabase or OpenRecordset, the object should be closed.
    <LI>Objects like forms, Excel workbooks, or Word documents should be closed if you opened them just to manipulate them in code.
    <LI>Application objects should be quit unless you want the user to continue using it interactively (you must have made the app visible for this; if you start Word or Excel with CreateObject, they are invisible by default).[/list]HTH

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: My latest Access 2000 crash (Access 2000 9.0.3821 SR-1)

    <P ID="edit" class=small>(Edited by charlotte on 10-Jan-03 06:24. for afterthoughts)</P>No utility that I know of. I'm afraid you have ot do it the hard way because there are sometimes legitimate reasons for NOT destroying an object variable ... such as a routine I sometimes use called ReturnRecordset. It returns a recordset object to the calling routine, so I can't destroy the recordset object at the end of the function that opens it. I have to destroy it at the end of the *calling* function instead.

    One other thing. If you're going to check those objects, you had better change the declarations to include the DAO object model reference while you're at it. That will keep you from having other frustrating errors with both DAO and ADO references set. Just change the declarations to Dim <variable> As DAO.QueryDef, etc. Then it doesn't matter which reference comes first in the list because Access won't get confused.
    Charlotte

Posting Permissions

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