Tips For Newbies

[HillTJ]

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]

@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.

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

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]

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]

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.

 

[Deleted member 147267]

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]

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]

A friend once described the "pivotal" moments very succinctly.

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

 

[Galaxiom]

Study as many example databases as possible.

I've seen more horrid databases than good ones.

 

[Cronk]

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]

How about another thread "How to avoid introducing bad habits?"

 

[The_Doc_Man]

@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.

 

[gemma-the-husky]

A friend once described the "pivotal" moments very succinctly.

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

I always think of the learning curve for Access as more of a staircase, with various levels to ascend commensurate with stages of competence. There are skills that you would find very difficult to learn from a book, or even a formal course. You can get to a certain stage and find a difficult problem that you just can't overcome on your own - but then there's a lightbulb moment as someone points out the method, or even nuance you missed - and it's filed away in your memory banks for future use.

An example would be to have a form linked to a subform. If the main form is a single form, and the subform a continuous form it's quite easy and all the general access primers show you how. If you want the main form to also be a continuous form then it's not so easy until someone shows you how.

[edit - I read this thread as someone had referred to it in another thread, and @isladogs located it, and referenced it]

 

[HillTJ]

All, I got a bit 'chuffed' when I saw this thread referenced in another thread. Got me thinking and I have another tip.

Tip- Be careful what project you sign up for! Don't get in over your head and promise something that is beyond your skills, no matter what others may think of your abilities

Consider the ramifications of a 'mission critical' database that you wrote, failing and data being lost!! Particularly if you have written the database in company time, or you've accepted payment for the job and/or promised the earth. You could get sued! It may be ok to write something for a social group, but real 'Clients' may not see it your way when problems arise & you're the 'professed' expert developer.

I got extremely anxious, when, several years ago, I wrote a Product Quoting system for the company that I worked for during company time. I look back at the code, poor error routines, poor logic (my boss at that time was excelcentric and kept offering advice about the project as he sat beside me whilst designing forms etc). As an example, I had forms that include a multitude of hidden unbound comboboxes that contain the numerical result of calculations based upon the value of other combo boxes!. Clearly bad practice and an application for a query.

I no longer work for this company, neither does my Boss (unrelated). I suspect they have reverted back to their mess of excel spreadsheets for their quoting purposes. The company did not understand the benefit of the application I developed (my Boss at that time lived by the application). Despite my concerns, the application worked very well for several years.

I raise a glass to professional database developers, and another glass to those professionals whom share their knowledge with us amateurs!

 

[Galaxiom]

Understand why "I.T. hates Access"

Last week the IT Manager/Network Admin I had closely worked with for the past 15 years moved on.

It was with some trepidation that I told his replacement that we had a number of database applications written in Access. I purposely downplayed my enthusiasm, just in case. His response was "Access does what it does does very well."

I thought I'm going to like this guy.

 

[Rutillo]

Great info for me is collected in this thread. Thank you all!