Results 1 to 10 of 10
  1. #1
    alsaf is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    12

    Tips to make access database easy to maintain


    I am in the process of creating a utility for work based on MS Access. The only problem I have as it will not be supported by my companies IT department, it will have to supported by my team and I'm the only one in my team who has VBA programming experience. The only way I think I can get round this is by the following:


    • Fully comment the code
    • Write documenation explaining what each function does
    • Put all non-form based VBA code into seperate modules
    • Use fully descriptive variables


    Hopefully with some of my colleagues can pick up some programming experience and be able to help me support it although to be honest once I get it up and running, there shouldn't be any need for maintanance once it is working. Does anybody have any ideas or tips to make MS Access VBA code easier to understand for people who have little programming experience?

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Be very careful of documentation. It sounds good at the time - and there have been times that I regretted not explaining to myself what I did a few months later! But in my experience it doesn't get maintained, either by the originator (who sometimes makes a change in a hurry and doesn't update the comments) or by someone else who couldn't be bothered.

    VBA is self-explanatory for those who understand programming - and then there is always google!

    The best tips I can give you are your naming conventions. These will help a huge amount in the VBA code. Make sure your table names are obvious - not only what they are but where they come from. Form and Report controls should also have proper names, explaining what they are/do. This is where a lot of people are tripped up, calling things combo's or whatever. When reading the code it helps a huge amount if the field and column names make sense. It is very frustrating having to hunt around trying to find what field is actually being used in a piece of code.

    Another thing people love to do is prefix their objects - all with the same letters! Drives me crazy. All tables start with tbl, all forms with frm. God invented alphabetic sorting for a reason!!! Suffixes are 10 times better and make a whole lot more sense.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why does the IT department not want to support it?
    Do you have a Data Administration group within your company?
    What type of data and data base policies exist in your company?

    Do you have specifications for this utility in writing? Do you have a budget and project plan? Is there a designated user/client who will own this utility when completed?

    Hopefully with some of my colleagues can pick up some programming experience and be able to help me support it although to be honest once I get it up and running, there shouldn't be any need for maintanance once it is working. Does anybody have any ideas or tips to make MS Access VBA code easier to understand for people who have little programming experience?
    You might wish to frame this paragraph and return to it every now and then and see how much of this is actually realized over time.

    Your colleagues don't really want to maintain anything. They will want to develop and experiment with the latest and greatest HW/SW.


    Using this quote
    Does anybody have any ideas or tips to make "MS Access VBA code" easier to understand for people who have little programming experience?
    I'd like you to think about this revised (only 2 phrases changed) quote, that I'm sure someone, somewhere has made

    Does anybody have any ideas or tips to make neurosurgery easier to understand for people who have little medical experience?

  4. #4
    SteveH2508 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117
    Get yourself a copy of MZ Tools
    http://www.mztools.com/index.aspx

    It is great for documenting/commenting code amongst other things.

    http://www.helenfeddema.com/CodeSamples.htm has a control renaming add-in which I have found useful as well. There are several renaming tools on the market as well.


  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with SteveH2508 -- MZTools is an excellent set of tools.
    In addition I'd recommend
    -free video tutorials at http://www.datapigtechnologies.com/AccessMain.htm
    -free tutorials http://www.functionx.com/
    -great Access tips http://www.fontstuff.com/access/index.htm
    Last edited by orange; 12-10-2011 at 10:28 AM.

  6. #6
    alsaf is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    12
    Firstly, thanks to all who replied. I'll have a look at links and tips given and implement them into the project. Secondly, I'd like to pick up on comments made by Orange.

    Before I start, I want to point out that I am unable to go into any detail about the company I work for or specific company policies on a public forum. Now that is out of the way, I want to give a bit of background information about project and my motivation for it. This is something I am working on when I have some free time between normal work duties. There is no budget, no project plan and because it is not something that has been developed by my company, it will receive no IT support should anything go wrong. I have done similar projects before and while some has been successful, some have not and as a result I have got my fingers burned which is the reason why I want to get everything right before I implement it. While I am doing this off my own back and there are risks that again I might get my fingers burned, I believe the benefits gained by this is worthwhile especially in todays environment where efficiencies in the workplace are essential.

    One of the requirements for it to go ahead is that while it will receive no official IT support, my company needs to be happy that if something goes wrong, that it can be fixed 'in house'. I think the issue is overcoming cultural barriers as MS Access is hardly used for tasks it should be where the preferred option is using Excel spreadsheets. As a result of using spreadsheets, there are a lot of manual steps which wouldn't be needed if a database was used. Hopefully I can make the case that once it is fully tested that nothing will go wrong and that databases are not some scary thing to be 'afraid of'. I also want to try to simplify the little VBA code I will use in the database so that it is understandable to my work colleagues who hopefully will be able to pick it up. While your comment about neurosurgery is valid, I think it is possible for someone with little or no programming experience to get an idea about how it works as long as the code is properly structured and commented.

    Hopefully this makes my original comments are little more clearer.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff. Take as many precautions as you can; involve others and get them up to speed on Access and maintenance. Since, as you say, you have been burned in the past, you are aware that somethings just stick to the creator.

    Get you group involved in standards for the documentation; get them involved/participating in design and programming. In effect, make them part of the project and maintenance from the start.

    If your company is using Excel spreadsheets for database activity, it is time to "expand the vision" so to speak.

    Good luck with your project.

  8. #8
    alsaf is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    12
    Thanks for the tips Orange. Much appreciated!

  9. #9
    SteveH2508 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117
    What you might want to think about is getting your database to log all errors to a central location. This gives you the opportunity to fix problems before users become aware of them. You can also isolate and fix problems that may be user-specific rather than db problems.

  10. #10
    alsaf is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    12
    Thanks SteveH2508. That sounds like a great idea.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Tabbed Form - Urgent tips required!
    By kp123 in forum Forms
    Replies: 1
    Last Post: 11-24-2011, 10:11 AM
  2. maybe some tips on a .csv query?
    By dr_patso in forum Access
    Replies: 3
    Last Post: 07-13-2011, 12:08 PM
  3. Replies: 0
    Last Post: 03-29-2011, 09:37 AM
  4. Tool Tips popup text
    By pkstormy in forum Code Repository
    Replies: 6
    Last Post: 09-02-2010, 05:50 PM
  5. TransferSpreadsheet...Maintain formatting
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 08-27-2010, 08:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums