Your VBA/Automation Headquarters!
Access is an excellent tool for working with groups of data. Its real value lies in the interface you can provide to your users. The most intricate database can be run by many who've never been trained or who've never even touched a computer before.
The basis for good database design is the tables themselves. Create the tables perfectly, and you'll have a very solid foundation from which to build.
We could look at Excel as being like a game of checkers. We might look at Access as being a three-tier game of checkers, where three games are going on at once, but they're related in that you must win all three games to win the match. The number three has nothing to do with it, rather it's the way that the games are related, regardless of how many tiers there may be. Relational database design is boring to read about, but you should at very least understand data normalization. Microsoft provides a good background on these topics at Relational Database Design Support Center.
The reasons for the need to compact and repair Access databases can be found in the help files under Compact and Repair. However, many people don't know about it or don't realize its importance. A newly created database can easily become over a megabyte in size even without data. It is very important to run compact and repair as often as possible. Depending on the number of transactions, you will ideally run compact and repair once per day; all users must first exit the database. For single-user databases, Access 2000 has an option under Tools-Options, General tab to compact on exit. Use it! Compacting the database is especially helpful prior to emailing it.
There are seven types of objects in an Access database. Here are explanations in simple terms to help you to understand the purpose of the objects.
Tables store the data. These are the most important objects in your database. If these are not designed properly, then there is no point in continuing to design other objects. Access tables are very much like Excel worksheets. In Excel, however, you'll have difficulty storing a client's name only once, and having many invoices for that client. Instead, every client's record would need to contain the clients name, address, etc. In Access, you'd have one client record and many invoice records that are related to that client. The Invoices table would contain a field that is linked (related) to the Client table.
Queries ask questions of your data. When you run a query, Access provides you with a temporary view of the data that you have requested. You can build many queries without increasing the size of your database because only the criteria is saved, not the data the data that is produced when the query is run. In our example, you may want to retrieve the client's name and address from the Client table, and the sum of that client's invoices.
Forms are yet another way to view your data, usually one record at a time. They are also created to allow an organized manner of entering new data, and changing or deleting existing data. Many people confuse forms with reports, because a form used to be something we printed. You can create subforms too. Again, in our example, we could have a form with the client's information and a subform below it with a list of that client's invoices. An invoices form may have a subform that shows details of the items purchased under that invoice. The Form Wizard is a great tool for creating forms. If you don't like what is produced, you can always change it. It's very helpful in learning to understand the design view of a form.
Reports are pretty much anything you want to print from your database, whether it is a single record or an entire summary. You may want to print individual invoices, end-of-month statements for your clients, or summary sales reports. The Report Wizard is a great tool for creating reports, which includes labels. If you don't like what is produced, you can always change it. It's very helpful in learning to understand the design view of a form. Many of the built-in form designs contain a lot of graphic elements, such as lines, that can be removed.
These are Data Access Pages. You need them only if you're putting your database on the web or an intranet. They are very similar to Forms.
Access has many built-in macros. If you believe that you need to know VBA for the simplest macro, then you may want to check out all of the built-in macros first. Practically all you need to do is pick a macro, tell it what you're using it on, and sometimes give a file name or other information.
Tables are the most important objects in Access. They are the foundation of your database, no matter how we might try to get around it. Until you have your tables perfectly designed, do not even bother creating any other objects. Bad table design is why a lot of people quit trying to use Access. They design the table badly, and then expect queries to work or reports to provide proper summaries.
We've worked with many people who have completely operational databases and now need help because they can't manage to get the information they want. They have fancy buttons on their forms, and their reports are beautiful. But because they never normalized their data, they may never be able to extract the information they need without redesigning their tables and, hence, a lot of these fancy items that were based on the poor table design.
Microsoft explains it best in their free download and webcast. Both are available at this Technet Article:
Naming and Creating Fields
You might call these column names, but they are appropriately called field names. As well as rules that conform to the use of Access, we provide some opinions here.
We recommend the following syntax for your field names:
Where TableName is a 3- or 4-letter acronym for the name of the table. For instance, if you have a customer information table, and you name the table "Customers", your field names could start with Cust. A customer company name field might then be called CustCoName.
Break your fields down to the smallest data available. You may think that CustName is good enough to insert your customer John G. Smith. Later, however, you might wish you could sort by last name. Middle initial fields are less important, but might be necessary if you have many John Smiths.
The size of your fields is important. Don't format a CustState field as text and allow the default 50 characters to remain. If you only have US addresses, make it 2 characters in length. Making relative field sizes is especially helpful when you use the wizards to create forms and reports. The wizards allow the amount of space you define in the table design.
Particularly when importing a table from Excel, check the design view data types of the columns. They often default to all text fields. Then, when you try to calculate a date that is 30 days from your invoice date, Access won't see it as a date and you'll get errors.
To create a search box on a form is very simple. While in design view of your form, make sure you're viewing the Control Toolbox. Choose the button indicated in the picture below:
I like to place the search box in the form header, so I draw it there. If you have a full install of Office, your Combo Box Wizard should kick in and help you at this point. Choose the item indicating to "Find a record on my form based on the value I selected in my combo box."
Hit Next, and choose the fields you want to SEE when you hit the combo box dropdown.
Here, we show choosing Last Name, then First name. Access secretly grabs the primary key field too, while you're not looking.
Hit Next and, if YOU chose your primary key field, Access will want to hide it, and show only any other field(s) you selected.
Hit Next. Give the combo box a name like "SearchName" and hit Finish.
When we move to Form view, the results of our sample display as such:
In the event you hate the font in the graphics like I do, don't use Haetten...whatever as YOUR database font!
Get free help with Microsoft Access and all of the Office applications at our forum. Click here to go there now!
Copyright© 2003 - 2010 The Office Experts and Tickling Keys, Inc.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®,