Tips For Newbies (1 Viewer)

HillTJ

Registered User.
Local time
Today, 06:11
Joined
Apr 1, 2019
Messages
344
Hi, I have been an enthusiastic amateur for about 5 years & a keen forum follower for the past 12 months or so and thought I may be able to pass on some tips that I’ve learnt from a newbie’s perspective.

For background, my first exposure to ‘databases' was at a company I worked for, where they used a self-written system of databases based upon a Symantec product called ‘Q&A’. This was a dos based product &, I think, probably a flat file database! During that time, I became the "System Administrator" by attrition and, I’m thinking by mid 1990's, Access became available to us. We trialed it but concluded that the old dos database was more compatible with our eclectic mix of computers, so stuck with it. We also trialed a windows version of ‘Q&A’ called ‘Sesame' if I recall. Anyway, I fell for the beauty & power of databases, when most others were using excel in various guises.

To the subject at hand and please feel free to add to or amend this list.
In no particular order, I'd suggest;,

  • Align yourself with an excellent forum such as this. Forum members are only too willing to help.
  • Don't think that you are the first person to encounter a particular issue, there is always help at hand.
  • Study as many example databases as possible.
  • Keep a file of code samples even print out stuff & keep it in a folder for later
  • Run 2 monitors, that way you can keep your project on a screen, whilst googling stuff or displaying code samples etc. on the other.
  • Queries made via the ribbon tool are best used where you may wish to refer to them on multiple occasions. If you need a ‘single use' query, select the query builder 3 dots on a form or control row source to build the query. That way you do not clutter the project navigation pane with a stack of queries that you only refer to once
  • Master ‘many to many relationships’, they pop up more often than you may think.
  • Master ‘composite' keys, particularly useful on the ‘join' table of the many to many relationships.
  • Don't settle for compromises. If something is not how you like it fix it.
  • If something doesn't work, take a break.
Remember that a well-constructed database is a thing of beauty that you bought to life.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 28, 2001
Messages
18,701
@HillTJ - you make some excellent points. A couple of your points are worthy of amplification and reinforcement.

  • Study as many example databases as possible.
  • Keep a file of code samples even print out stuff & keep it in a folder for later

When you have example databases and code samples, you find ways that other people have used. This expands your way of thinking because as a "newbie" you would hardly EVER think of "doing it that way." I have found in my career in various aspects of computing (starting in 1975 as a professional and actually starting in 1968 as a side interest during my baccalaureate program) that seeing how anyone ELSE approaches a problem gives you ideas. In computing jobs, ideas are "tools" in your mental toolbox. Like a set of socket wrenches, where you quickly learn that "one size does not fit all," you can find samples, references, and discussions of strange topics in programming.

Your point about not settling for compromise is also incredibly important. You usually build a database for one of a few reasons
(a) you are making a library system such as organizing your recipes or your family picture album or
(b) you are building a digital model of some data flow or work flow for your business. It can be an inventory system, a document approval system, a work assignment system, ... doesn't matter. It is an attempt to digitally represent something that your business does... or
(c) something else for enjoyment, curiosity, ...

For case (a) the issue of compromise isn't such a big deal because Access does that kind of organization simply and well, pretty much on its own. Case (c) is so wide-open that it really doesn't matter here either. But for case (b) there is a TERRIBLE trap to fall into if you are not careful. Here is where compromise is anathema.

There is a principle in the theoretical sciences that says "if your model and reality disagree on something, the model is always wrong." Or "if Reality says X and the math says Y then the math is wrong." Remember that whatever your business does is REALITY; whatever Access says is merely your attempt to capture reality, however imperfectly. We also have a common phrase here in the forum that occurs when someone is trying to make a business tracking system and can't do what the business would actually do, so they attempt to mutate the business or "wing it" or something like that, allowing the database to dictate what your business have to do. This is a case of "the tail wagging the dog" which, if you think about it enough, can be seen as totally backwards to the way things should be. Therefore, when dealing with business modeling, there is NO room for compromise. When in doubt, your business is always right. (Even if it is totally wrong-headed.) This isn't to say that you can't use the DB as a springboard for a discussion of fixing your business data or task flow problems. But do it in the right order and consciously.

I'm going to add a bit of advice that you didn't include: Remember your history. By that I mean that any or all of these are important:
  • Include meaningful in-line and "paragraph" comments in your VBA code and be specific in any object's .Description fields
  • Keep some kind of history either in a table in your project or in a separate document so you know what you did AND WHY
  • As a matter of discipline but also as a way of tracking where/when problems were introduced, keep version numbers and associate specific changes of code or structure with the version in which they were introduced.
This relates to your idea that you brought a database to life. IF your database mirrors reality then it has one more attribute - it grows, changes, evolves, whatever biology metaphor seems right for you. TRACK how it grows, because there will come a time when you have to put it aside for a while then come back to it to fix something. And you delve into it only to find a piece of code or a query that makes you scratch your head and say "What the HELL was I smoking when I did this? What burr was up my butt at the moment?" By having a tracking document that reveals the WHY of any decision as well as the WHAT of how you solved it, you will be able to drop and later pick up that hot potato constructively.
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
You may get some disagreement on mastering composite keys as apparently, they can cause difficulties in certain query join situations. A composite index is oft thought to be a better approach. 'Mastering' is often an illusion. I thought I completely understood composite keys until I ran into a situation with a 4 field composite index that I won't get into here as it will just derail your post.

To your list I might add something about studying - either from a reputable source of videos or books. Simply duplicating what you see in databases leaves one open to picking up bad habits, and the M$ Northwind database is no exception.
 

HillTJ

Registered User.
Local time
Today, 06:11
Joined
Apr 1, 2019
Messages
344
Most excellent responses. I was hopeing to generate such discussion. Micron, i agree, and further, composite indexes to a Newbie are a thing of mystery. I also think sub queries, you know, a query within a query is a pivotal concept. Simple by design, but not well covered by documentation. I often use a query to select 'max' date from a table then generate another query including that query & desired linked tables.
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
You might be interested in some of the comments here, as a sidebar. There are also some comments regarding Access instruction vs forum use instruction - the former being related to your post, the latter more pertinent to that thread.
 

HillTJ

Registered User.
Local time
Today, 06:11
Joined
Apr 1, 2019
Messages
344
To me there are 'pivotal' moments where a concept is finally understood after a struggle, research & assistance from the forum. Maybe it's me, but I'd expect other "newbies" (I cannot think of a better term) to advance similarly. I'd like to hear what members consider 'Pivotal' moments.
 

Isaac

Lifelong Learner
Local time
Today, 09:11
Joined
Mar 14, 2017
Messages
3,200
I like this thread, it was interesting and fun to read, and I agree with most everything I saw. My top few approaches that have helped me (or things I've failed in, that in hindsight I realize how valuable they were), include but are not limited to these, and I admit some of these broaden out to career minded things, but might be good for anyone whose career seems to be going in this direction:
  1. To the extent that it's allowable in your situation , try to memorialize your code in some way you can refer to at least in some form or fashion when you go on to that "next job". There is nothing like having built the coolest techniques in the world that you have to keep re-creating. A personal programming tips blog will do and will often strike a good balance as to what is allowed (or not) at your company. (And I am not suggesting doing anything that isn't allowed).
  2. Take every possible opportunity (!) to broaden your horizons with the larger RDBMS as early on as possible. It will quadruple your career options, while still quite possibly involving Access as a beautiful front end tool.
  3. Understand why "I.T. hates Access", and do your best to create databases that solve for many if not most of those concerns, so you can speak to it at your next job as to why you still feel Access should be a piece of the picture
  4. I rarely see this mentioned, but I feel that as developers, humility is practically the currency of learning and expanding. Ironically it can be a bit hard to find restrained ego's in the technical realm, but read as many articles as you can of what makes a "good developer". Most of them will be the first to emphasize how good developers have mastered things like peer reviews, accepting when others' code is better, and re-tooling your own without letting ego or pride get in the way.
  5. Highly prioritize readability, maintain-ability and clarity in your code. Most people instinctively recognize the application of this concept in the form of code comments, but another big offender I see are variable names. Variable names are not opportunities to create excessively short and cryptic abbreviations. They should be named in a way that steers the thinking and understanding of the next guy, who is currently reading 2 pages down from the declarations area. Take pride in naming them with the utmost precision--whatever pattern you may follow, which I won't even get into--and take the time to re-name them if they no longer make sense. You will thank yourself later, if no one else does. Think about how much more difficult those triple-negative, mind-tripping multiple boolean comparison one-liners will be to troubleshoot and to quickly understand. White space is not expensive. : )
  6. Don't create a new Access database every time you turn around. (See #3). Purposefully try to create inclusive workflow applications that minimize the number of different apps a given person in a given department has to log in to every day.
  7. Understand the responsibility & liability that are implicated if you store business critical database in an Access back end. (See #3). If possible store it in a corporate I.T.-approved system and embrace the DBA's role. They may seem like your enemy and a gatekeeper at times, but they take a lot of data responsibility off your shoulders. Be comfortable making front ends only, if that is your role.
  8. Don't shirk documentation. (See #3).
  9. Be a principled developer and try to follow sound practices even when creating a feature that everyone swears is for temporary or work-around use, or in testing, or in small procedures that nobody will ever see, because they will.
  10. Password protect your VBA projects. Mostly for obvious reasons, but also for confidentiality. Even if "but I never store passwords in code", at some point along the way chances are very good you'll accumulate confidential things in your VBA. It might even just be the business logic behind who got a bonus last year, maybe that's not public. It might be as simple as your comments are too colorful. That one gets a lot of people.
  11. Keep your code comments restrained and professional. It may be tempting to add superfluous thoughts as a slightly odd way of venting, but when you give your 2 week notice and are explaining every block of code to the next guy (perhaps), it's less fun to come across code comments like "and just in case THAT doesn't work...", or, "stupid process!"
  12. As Micron, I hesitate to use "master" at all, but try to master google search terms. It might sound funny....Surely all Googlers are alike? .... But it comes from appreciating usage of the CORRECT TERMS....especially in coding constructs....and using them with precision.
  13. Avoid getting yourself in a situation where you allow a power user, or an interested end user, get overly involved with what should be development decisions. Your mileage and tolerance for this might vary, but if you get to the point where a business manager is telling you to add a column to a table, you have gotten yourself in a quagmire you will probably regret. Try to phrase discussions with the business by capturing entities, relationships and events or transactions.........capturing reporting requirements and GUI requirements. Then go back to your personal drawing board, forget most of that and get the entities and tables perfect. The rest will flow.
 
Last edited:

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
I'd like to hear what members consider 'Pivotal' moments.
About 10 years ago someone solved my issue with a sql example using a sub query. First time I had ever heard of such a thing. Then you have to wonder how anyone visualizes such a concept in the first place. I think it takes a different sort of mind (at least from my perspective) to take the known to another level like that. It's a talent that not everyone possess IMO, but now I bet a lot of us take such results for granted.
 

Cronk

Registered User.
Local time
Tomorrow, 04:11
Joined
Jul 4, 2013
Messages
2,455
Another point is that a database system exists as a tool for users to do their job. If it's a good tool, users will enjoy using it and productivity will increase. If it is a bad tool, I haven't done my job and users will only use it under duress.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:11
Joined
Jan 20, 2009
Messages
12,165
A friend once described the "pivotal" moments very succinctly.

Gaining knowledge is an incremental process but understanding comes in quantum leaps.
 

Cronk

Registered User.
Local time
Tomorrow, 04:11
Joined
Jul 4, 2013
Messages
2,455
I've seen more horrid databases than good ones.
Ain't that the case. I don't know how many times I've been called into a site where a db has been set up (badly) and maintained (with band aids) by one of the Access unskilled staff who has then moved on. Then it's a matter of whether to scrap the lot and start again or try to patch the worst parts.
 

HillTJ

Registered User.
Local time
Today, 06:11
Joined
Apr 1, 2019
Messages
344
How about another thread "How to avoid introducing bad habits?"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 28, 2001
Messages
18,701
@HillJT - unfortunately, learning how to avoid introducing bad habits is what we used to call a two-pass algorithm involving either prescience or reincarnation.

But then again, experience is that which enables us to recognize our mistakes when we make them again.
 

Users who are viewing this thread

Top Bottom