Results 1 to 6 of 6
  1. #1
    rhdr is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017

    VBA Access - 'Design Patterns' / Best Practices

    Hi all,
    Ill jump right into it our office got an Access/SQL_Server database that has been used and developed over several years. I am tasked with rewriting the system section by section using design principles to achieve better maintain & upgrade ability.
    Question: If you are forced to use MS Access VBA, is there 'Design Patterns' or Best Practices that can be followed to deal with VBA development?
    View Classes: I'm thinking of building "view" classes for entities and invoking these classes from a switchboard or from any form anywhere as needed.

    Model Classes: How do you handle "model" classes? - I don't think creating a private class variable for every column is a good idea especially if you are going to make use of SQL, then again complicated computations or automated tasks could benefit form this. However this would slow down processing if you say use DAO to read the records into the model class
    Passing a Class Variable to SQL: What if I got a class and I want to run a method like a.showReport(ID as Long). How would I pass an ID variable to the SQL query underneath the report?
    Option A.) Write the SQL for such a report in VBA - but doesn't this obfuscate the SQL? And if a developer later on make changes to the table structure then he got to search all the classes through to update the SQL written in VBA, which in my eyes is harder than updating a saved access query.
    Option B.) Write a set class method that then set a public variable used in a module function which in turn is then called by the SQL query! - Needless to say this might complicate things and the SQL will have to make a call to the function for every record which could cause performance issues (if only SQL had a way to directly call an class method?)
    Option C.) Just build a simple query(saved in access outside the class) to filter on say a variable in a Form with a report on top - but then I would need to write a new query and report for every new scenario and build that into the class, also another big drawback is that the query/report is now closely coupled with the form and cant be used with out it
    Naming Standards(especial query names): What are good practices? - As one can imagine after a few years of ad-hoc development there are objects all over the place, especially queries. The current db got queries that is stacked on queries which is stacked again and again (in cases where there are a lot of computations such as a payment advice) and just to add to the mess if a developer missed something and had to squeeze a query in to the middle of the query stack... let me just say the way we are naming objects now, it is not workingTah

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    1. I RARELY use classes. Most everything can be done via queries and macros.
    Since I know vb, i can enhance this.

    2. standards: i use naming conventions for everthing

    tables start with t
    tClients, tEmployees,

    queries start with q
    and then the type of query , qu ,update, quFixNames
    qa, append
    qm, make
    qd, delete
    qx, crosstab
    qn union

    reports, r
    macros , m

    variables: the 1st letter determines scope:
    modular variables start with m
    global vars start with g

    2nd letter the datatype
    mi, integer
    md, date
    mv, variant

    so , now by looking at the code, you know what is a query, what is a table,
    the scope via the variable name

    I inherited apps that use no convention, there was no telling if the object was a query, a table, or WHAT??

    for external apps, (like excel) i do create classes for data collection or retrieval if needed. again, rare.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    I have never designed custom classes either.

    There are several ways to pass data to a form or report, most employ macros or VBA.

    1. parameterized queries - static or dynamic parameters (I never use dynamic)

    2. WHERE argument of OpenForm/OpenReport

    3. OpenArgs argument of OpenForm/OpenReport

    4. Global variables and TempVars

    Whatever naming convention you design, strongly advise not use spaces nor punctuation/special characters (only exception is underscore). Also, no reserved words as names. As far as your 'stacked queries' issue - I expect no naming convention can anticipate changes in analytical structure if you want naming convention to describe dependencies.
    Last edited by June7; 09-28-2017 at 01:17 AM.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    I also do not & have not used classes.

    - For my PK/FK fields, I use a suffix of "ID_PK" and "ID_FK". Since my PK fields are autonumber, (Identity in SQLS) I use "ID" (most of the time). Ex. tblEmployees - PK field name would be "EmployeeID_PK" or "Employee_PK". So much easier when working in VBA or queries to see which are PK fields and which are FK fields.
    - I never use composite (multi-field) primary keys.

    Q: Do you use linked tables or are you using server side views, queries and/or stored procedures??

    Question: If you are forced to use MS Access VBA,
    Interesting... why do you say "forced" to use VBA???
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    rhdr is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Thanks for the input and sorry for the late reply, had a hectic week... Currently the system is making use of linked tables.I am not really up speed with how exactly server side views or procedures work, will look into it.(Will also take a look at dynamic parameters again)

    Is there a way to apply the MVC Design Pattern to MS Access using VBA?
    What I want to do is build classes that can later be reused in other projects or if this project gets upgraded in the future I want reuse the data & logic classes but only re-code the interface classes in a different coding language.

    I was hoping that I could implement a more object oriented approach using Access (like inheritance), but looks like even with the limited class functionality Access is more suited for a structured oriented approach. Not really sure why they included the ability to create classes to begin with, because it does not seem that anyone really use them in VBA.

    Quote Originally Posted by ssanfu View Post
    Interesting... why do you say "forced" to use VBA???
    To be honest it is because I am still studying and recently been doing a lot of OOP coding, was hoping for an OOP/c++/Java project to get some practice in that field lol

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Not really sure why they included the ability to create classes to begin with, because it does not seem that anyone really use them in VBA.
    any code in a form or report is a class and is probably the main reason users do not need separate class modules. But they do have their uses. I use them in three main areas - import and export of data and an overriding class over various form control collections.

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

Similar Threads

  1. Normalization/ Best practices
    By xmerrox in forum Database Design
    Replies: 4
    Last Post: 09-28-2017, 11:52 AM
  2. Replies: 10
    Last Post: 06-20-2017, 12:57 PM
  3. Access Best Practices
    By jrmbach in forum Access
    Replies: 4
    Last Post: 05-12-2014, 08:23 AM
  4. Replies: 2
    Last Post: 02-25-2012, 06:29 PM
  5. Best Practices
    By Sinkerhawk in forum Access
    Replies: 2
    Last Post: 06-30-2011, 11:44 AM

Tags for this Thread

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