Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Clean up db (2002 SP-2)

    This weekend I intend to (finally) go through and clean up my db. This app was originally created several years ago, and I just built upon the originators design. I'm resonably confident there are a number of queries, and certainly forms and reports, that can be removed. I have Find and Replace, and will be using Find (only) to discover the relationships. My primary question is if the code behind a form is removed from the app when the form is deleted? In other words, if I go through and delete all these old objects, do I first need to remove the code behind them? I will of course back everything up before I begin, but I wanted to get some advise on the best procedure to follow when doing this. I've got four days to get my mind-set formed (presuming that will help).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    If you activate the Visual Basic Editor, the Project Explorer will display a tree view of the modules in your project. There are up to three main divisions:
    1. <LI>Microsoft Access Class Objects. This contains the modules belonging to forms and reports. If you delete a form or report from your database, the corresponding code module (if present) will be deleted too. (In very rare occasions, the code module may remain behind; this is a sign of corruption in the database.)
      If you want to remove all code from a form or report, set the Has Module property in the Other tab of the Properties window to No. You'll have to confirm that you want to do this.
      <LI>Modules. This contains the "standard" modules you create by clicking New in the Modules section of the database, or by selecting Insert | Module in the Visual Basic Editor. They are not associated with a form or module, so the must be deleted explicitly. This division will not be present if you have no standard modules.
      <LI>Class Modules. Contains a special kind of modules, rather technical in purpose. This division will not be present if you have no class modules. You would have to delete them explicitly.
    Modules and Class modules are both displayed in the Modules section of the database window, and can be deleted there and from the Visual Basic Editor.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    In order to further understand, does code "run" only when it is called from a control/object? I can't think of how to really put this question properly, but I guess what I'm asking is if "orphaned" (unused) code is left behind, does it have an effect on the speed of the app? Is that code just passed over, or is it run through along with whatever else may be called by the app? Does that make any sense?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    If you have orphaned code, it will not run by itself. Code can be executed in a database from:
    - Events on a form or report associated to the form or report itself, or its sections, or its controls.
    - A macro named AutoExec, if available (it is run automatically when the database is opened.)
    - A macro named AutoKeys, if available (this contains keyboard assignments.)

    Orphaned code does take up space on disk and in memory, and may have a negative effect on performance. When you run code, all code in the module that contains it (whether it is a standard module or a form/report module) is "compiled", this takes time and space. So it makes sense to remove unused code. If you think you may need it elsewhere later on, you can always save the code in a text document, outside the database, before removing it.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Hans:

    Thanks for the guidance! I hadn't thought to save the unused code to a text file; even if not currently used, it might be useful later (presuming I understand it and save it with an appropriate name). There is code in here that doesn't appear to be used by anything (some quite lengthy) so maybe I can gain some speed after clean-up. Sure hope this week-end isn't one when all Loungers decide to go out of town. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Just poking around to see what I might encounter. One of the things I will want to do is correct naming conventions (ie. txt115_click). Is there a way to discover what txtbx that is from VB editor? Or, do I need to click each txtbx in design view to examine the name? Also, I'm (now) displaying the Properties box in VB, but it's empty. Is there something else I need to do to show the properties for an object?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    You can't view the properties of a control or form in the Visual Basic Editor.

    To locate a control on a form or report, use the Object dropdown list on the left hand side of the Formatting (Form/Report) toolbar, or the dropdown list immediately below the title bar of the Properties window in Access itself, not in the Visual Basic Editor.

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>...amazing the things I can't see that are right in front of me. Thanks, that will speed things up considerably! So, what's the Properties window in VBE for?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    >> So, what's the Properties window in VBE for?

    Not much in Access. It's mostly used in the other Office applications, where you create and design userforms in the Visual Basic Editor. Originally, Access had a different system for editing code than Word and Excel, but with Office 2000, they decided to give all Office apps the same VBE.

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Just to pass on what I discovered. In VBE, if I select a class object (with the Properties window open) and then open that object, it will populate the Properties window with more information than I could possibly understand. If I select another object, the Properties window goes blank, until I open that object. As you mentioned, it doesn't appear that you can do much there, but you sure can view every possible property of the object (by alpha or by cat.)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    If you select a control on a form or report, or a section of the form/report (in design view), and switch to the Visual Basic Editor, the Properties window/pane will display the control's properties. You can change a lot of them, but the Properties window in Access itself is much more convenient for this.

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Wow, what a mess!

    So far I've discovered/removed thirty-one separate instances of code that either have no current functionality, or are a duplication of more recent code. I am also finding a number of curiosities, not the least of which is as follows:

    <pre>Private Sub ComboShipperConsignee1_DblClick(Cancel As Integer)
    On Error GoTo Err_Command2296_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDirections"

    stLinkCriteria = "[Name]=" & Chr(34) & Me![txtShipperConsignee1] & _
    Chr(34) & " And [City] = " & Chr(34) & Me![txtShConCity1] & Chr(34)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command2296_Click:
    Exit Sub

    Err_Command2296_Click:
    MsgBox Err.Description
    Resume Exit_Command2296_Click
    End Sub</pre>


    I don't know what "Command2296" was, but it doesn't exist anywhere in the db. I presume it must have been a copy/paste process either from another db, or from a control that was re-named (I'm not even certain it is something I did, or if it was done by the original creator of this db). In any case, I wouldn't think the error handling would work as is. Being the master code writer that I'm not, I took a look at how error handling was addressed in some other areas - I found (thus far) two different methods. I'm not certain which is the better method, or even if both are correct. Here is the first method:

    <pre>Private Sub ComboFBlngMilesRates_DblClick(Cancel As Integer)
    On Error GoTo ComboFBlngMilesRates_DblClick_Err
    (some code omitted)
    ComboFBlngMilesRates_DblClick_Err:
    MsgBox Error$
    Resume ComboFBlngMilesRates_DblClick_Exit</pre>


    And the second method:

    <pre>Private Sub CmdOpenOrderStatusLog_Click()
    On Error GoTo Err_CmdOpenOrderStatusLog_Click
    (some code omitted)
    Err_CmdOpenOrderStatusLog_Click:
    MsgBox Err.Description
    Resume Exit_CmdOpenOrderStatusLog_Click</pre>


    I presume they both accomplish the same thing (assuming they are both correct). If they are both correct, is there a "prefered" method? I will be doing a search for "Command2296" (and any other odd looking error handling) and changing them, so I want to get it right the first time. Any advise appreciated.

    BTW, if there is anyone following this thread that knows less than I do (which pretty much means you would have had to be born just this morning), I'm not "deleting" any code regardless of what I encounter. I am copying it to a text file and storing it in a subfolder where my db is located ("dbCleanup"). Each string has it's own text file with a description of the object it came from, the event involved, and a name for the procedure.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Clean up db (2002 SP-2)

    Hello Bryan,

    The first procedure you post certainly looks as if the body of the procedure was copied from the on click procedure for a command button named Command2296. This has no negative effect on the operation of the procedure, it only makes it confusing to read.

    The two other procedures you posted are essentially the same, taking into account that one is for the On Double Click event of a combo box (and hence has a Cancel argument) and the other is for the On Click event of a command button.

    Error handling works with labels; the naming of these labels is up to you, it doesn't really matter which names you use as long as they conform to the rules: the name can only contain letters, digits and underscores, and mustbegin with a letter. And of course, you must be consistent. The scope of a label (the range in which it is recognized) is the procedure or function; you can use the same label name in different procedures/functions without causing problems.

    There are several conventions for naming error handling labels; there is no "preferred" convention. The ones in the procedures you post put the name of the procedure/function in the labels, preceded by "Err_" or "Exit_", or followed by "_Err" or "_Exit". The Control Wizards in Access use the first one (name of the procedure/function, preceded by "Err_" or "Exit_")

    I myself use a simpler convention: I always use ExitHandler and ErrHandler, whatever the name of the procedure/function. This makes it easy to copy code from one procedure to another without having to bother with adjusting all the label names. But it is purely a matter of personal preference, it is not intrinsically better or worse than another convention. This is the "skeleton" for procedures I use:

    Sub SomeProcedure()
    ' Declarations come first.
    Dim ... As ...

    ' Set error handling.
    On Error GoTo ErrHandler

    ' Code goes here.
    ...

    ExitHandler:
    ' Exit section - clean up.
    ' Make sure that errors here have no effect.
    On Error Resume Next
    ' For example, close recordsets, set object variables to Nothing, etc.
    ...
    Exit Sub

    ErrHandler:
    ' Error handling section. First inform user.
    MsgBox Err.Description, vbExclamation
    ' Then jump to exit section to clean up.
    Resume ExitHandler
    End Sub

    The error handling section only puts up a message box here. If I want to react to specific errors, I would set up a Select Case Err statement, for example

    ErrHandler:
    Select Case Err
    Case 2501
    ' Action canceled - ignore.
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    Resume ExitHandler
    End Sub

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Hans:

    Thanks so much for taking all that time to fully explain the method. I must say that I prefer your approach (now having a better understanding) over the others because it addresses the issue of copying code without creating confusion with the error handling. Even if only an "esthetics" issue, it certainly makes it easier to read. Thus far I am enjoying a moderate speed increase in the app. and a dramatic increase in the compact on close process (probably ten times faster). Probably the greatest gain has been in seeing how the objects interact with VBE, and seeing how one should properly address the removal of controls and event procedures. If done correctly, it certainly eliminates a good deal of "garbage". I have also come across a number of design issues where it appears there are query duplications (same result, but called from different objects). I will be addressing those as I gain a complete understanding of the consequences of removal. Thanks again for your further advice and "tutorial"!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Clean up db (2002 SP-2)

    Hans:

    Just checking to see if I follow:

    <pre>Private Sub CmdAddFBRate_Click()
    On Error GoTo Err_cmdAddFBRate_Click

    Dim stDocName As String

    stDocName = "mcrOpenFBRateAdd"
    DoCmd.RunMacro stDocName

    Exit_cmdAddFBRate_Click:
    Exit Sub

    Err_cmdAddFBRate_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddFBRate_Click
    End Sub

    <font color=blue>Would become:</font color=blue>

    Private Sub CmdAddFBRate_Click()
    On Error GoTo ErrHandler

    Dim stDocName As String

    stDocName = "mcrOpenFBRateAdd"
    DoCmd.RunMacro stDocName

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</pre>

    Because I don't know what "specific" error might arise, I presume there is no need (at this time) to address a select case. Something I didn't notice earlier when comparing the methods (and you mentioned) is that the combobx has a cancel argument, where the cmdbtn does not. Is that typical of the two controls? Also (I just noticed), should that be ExitErrHandler rather than ExitHandler?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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