Things to Avoid In Access

[cbende2]

Hello all,



I've been working on an ongoing database project for around a year now. I've learned much about Access and the do's and don'ts. However, I really wish I would have known the "don'ts" before starting and building my database. Such as what fields to use/not use when dealing with longevity and possible up-scaling.

Just wanted to open a discussion on anyone's opinions of what to avoid in Access for the newcomers!

Thanks

[JoeM]

Here is a good listing of things not to do:
http://access.mvps.org/access/tencommandments.htm

However, here is what I would say is the single biggest thing:
"Do not attempt to create anything that is beyond a basic, simple working Access database if you do not have a good understanding of Relational Database Theory and the Rules of Normalization. Do not think you can just wing it, and figure it out as you go. Take the time to learn these concepts first. It will save you much aggravation down the road."
(spoken by a man who has experienced this heartache first-hand)

Here is a brief overview of these topics:
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[nick404]

Here is my two cents..

1. Recognize the reserved words Access has and do not use them for field names, table names, &c.
http://allenbrowne.com/AppIssueBadWord.html
2. I definitely agree with Joe's first link where it says to learn the code you are going to copy/paste into your database. You won't become a better access user if you don't even know what you are telling your database to do- learn it first.
3. Also avoid spaces in names for tables, queries, forms, reports...
4. Always make a copy of a functional database you are going to make major changes on, test it, then make the changes in the actual one.
5. Often times it is what you think access cannot do that restricts its functionality.

[orange]

One of the main things to avoid with Access is to jump in to Access without learning some basics of database.
Access is NOT equal to database. Access is a rapid development tool with its own variation of SQL and syntax. Too often people think Access is a glorified Excel (spreadsheet) and feel they can learn as they go. Many have to unlearn their spreadsheet knowledge to understand/learn database.

Learn some database concepts -- modelling, normalization, entities, attributes.... before jumping into any specific tool (RDBMS) --Access SQL Server, MySQL, Oracle....

There are some great links and info here. http://www.dcs.warwick.ac.uk/~hugh/M...abase%20Design

Here is a post that has some additional do's and dont's.

[cbende2]

Good additions guys!

[ssanfu]

After you understand the above concepts, understand that pencil and paper are your best friends.

Design your tables on paper, cardboard, sticky notes, whiteboard, ect BEFORE creating the first table in Access. DO NOT design your table structure on-the-fly. (this is when/where you do the normalization)
This will save you hours of deleting, re-creating and frustration! Been there, done that, had the headaches..... Wish I knew back then... but better late than never

[CJ_London]

my twopenneth

don't use lookup and multivalue fields - if you feel you really have to use them, take the time to fully understand their limitations and how they work
learn the proper terminology
use meaningful names for everything

[Waagemartin]

Hm... as a beginner, some of this looks pretty intimidating...

[JoeM]

Hm... as a beginner, some of this looks pretty intimidating...

It can be, if you are not prepared.

Note that this advice really pertains to designing a working database. If you just want to use Access to do something like a compare between two lists or some other "one-off" task, you don't really need to know all that stuff beforehand. However, if you are planning on designing an involved working database, do not think that you can just jump into it and "learn-on-the-fly". It will be a very frustrating experience if you go that route (many of us speak from experience!).

Access is a very powerful tool, but you need to understand some things first. It is not nearly as intuitive as Excel.

[nick404]

Hm... as a beginner, some of this looks pretty intimidating...

At first, yes, but it really just involves preparation. Of course you will not know everything you need to know before you start making your first few, hence the use of forums, but that it part of the learning process. And JoeM is absolutely correct when he states it is not nearly as intuitive as excel.

[Waagemartin]

not nearly as intuitive as excel.

That's quickly becoming VERY obvious to me now...