Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Cleanup database

    I have been given the task of updating our access 97 database.
    When it was created they just kept rewriting the queries, forms, etc...
    Now before I attempt to update to a more recent version I would like to remove all of the unused junk, But I do not know where to begin. Any help would be a great help.

    Thanks Randall

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possible framework:

    1. If there is an Autoexec macro, trace each step of its execution and write down any object (form, report, macro, module) it uses.

    2. The next step consists in identifying the forms and reports that are actually used (outside a possible Autoexec macro).

    3. For each form or report collected in 1 and 2, you then must determine which tables/queries they use (RecordSource property).

    4. For each form or report collected in 1. and 2., examine their class module to detect any reference to functions in other modules, macros, use of tables or queries (either by opening a RecordSet, using a domain function or building dynamic SQL querie).

    5. Create a new database and import the objects ih the list assembled in the steps 1 to 4.

    6. Write down the references used in the original database and add any missing reference to the new database accordingly.

    7. Try to compile it. if you receive errors (Unknown Function), you'll know that one or several modules are missing. Identify them then import them in the new database and try compiling again.

    8. When you are able to successfully compile the new database, test each functionalities, note any error and identify the missing object. Import it and try again.
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    2

    Possible Framework

    Sinndho,
    Thanks that is something to work with. With the size of this it will take awhile, but you have to start somewhere.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally if it aint broke dont touch it

    by all means spend time understanding the tables, queries, forms and reports, but providing its meeting the user needs leave well alone. unless there is a good reason to make changes

    say the performance is iffy (usual suspect would be over use of domain functions such as dum, dmax, average and so on, especially in reports.

    say there's more stuff needing adding

    or if you have been explicitly told there's problems.

    before making changes spend some time with the users to make certain that changes being made are actually important to the business and not just 'da boss'

    if there's extensive use of VBA code behind the application then get to grips with the authors style and way of doing things. just because author A does things one way doesn't make it wrong, unless the code uses a style or approach that is deemed deprecated or uses libraries that are no longer supported

    the problem with removing junk code is that unless you truly truly get to grips with the system then its all to easy to delete something thats used infrequently (such as year end processing) and then have to reinstate stuff pronto. at year end I prefer sleeping in my bed, as opposed to tearing my hair out trying to work out why someone or something tinkered with this *&%^(*(*& code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I have to agree with the Jaded Developer, here! But if you're determined to persue it, here's a couple of points I'd do:


    1. Copy the current Access File and keep the copy safe!
    2. Send out a list of all Queries and Reports found, asking all users in the company to identify those they currently utilize
    3. Ask users to identify any of the above that they currently have problems with

    Concerning #1 above, I'd actually make several copies, including one to a CD disc.

    Concerning #2 above, you need your users to include any seasonal Queries/Reports that they use seasonally, although not necessarily at the moment, such as the aforementioned end-of-year Reports.

    Concerning #3 above, impress on the users that you're only concerned with problems they are currently experiencing with things acting up, not on new things they wish they could do! Time for those things will come after you get the current app trimmed down and functioning correctly again. Trying to do all of this at once is a sure fire recipe for disaster!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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