Access Database Getting Started Guide

For small systems, creating a database with Access is your best bet. An Access database is used to hold and retrieve large amounts of information. This could be as simple as keeping track of the DVD’s and CD’s you currently hold to something more substantial such as a stock monitoring system.

Let’s look at the key components needed when creating a database.

Planning

Know what you want your system to do before you go about creating it. What is the subject of your system? An example of Access database software could be a system to record your home inventory or students and their exam results? Before you go any further you must know what you want to record in the system.

Design

The design stage involves sketching out the data stores, otherwise known as tables. Most modern databases are relational in nature. Essentially this means that your data or tables are related together in some way, usually by a common field or key. For example a customer can be linked to related orders via customer ID. An example of a relationship is one student can take many courses. However each course can be taken by more than one student. This results in what it known as a many to many relationship. You would create one table to store the student details and another to store any courses they have taken. You can link them together via a student ID field. There is a little bit more to it than this, but you get the general idea of what is required.

Relating data is the part of database systems which tends to be difficult for some to grasp. It comes with practice and also trial and error, but the task should not be overlooked.

Inputs and outputs

Decide on what data you want to put into the system and what you want to retrieve. What questions do you want to ask your system? Do you want to know the sum or totals of any data? Do you need to know when a payment is due for a list of customers? Think about what information is needed for reporting or analysis.

Interface.

Now that you have defined your data relationships it is time to work on the screens which will be used to input and output data. A Microsoft Access database is an ideal tool for quick interface design. In the newer versions such as Access 2007 and Access 2010 you can use a single mouse click to create a quick simple interface.

Remember, although the interface is used to input data it can also be used to retrieve and present data. You may wish to have a search mechanism which searches for which students are currently present in the class. The system would search through your data in the tables and pull out which students are present. It then shows them on the screen interface.

Reporting

If you want data to be in printed format then you need to create MS Access reports. You may want specialised criteria to be used. For instance you could tell the database that you want all the widgets sold in South America for the last quarter of last year.

Let’s now look at what tools Access has that enables us to carry out our database tasks outlined previously and also how to use Access database systems.

Firstly let’s ask ourselves why MS-Access should be used? Well, it is suited for creating small systems with a small number of users. It is a great prototyping tool and interfaces can be built quickly. It offers a powerful way to handle large volumes of data. As with many other database software the information can corrupt and it is advisable to not only keep backups, but to repair Access database software. These days this task is easily done with Access recovery software.

Tables

Creating a table in Access is very easy. Name your field and select your data type. For instance, you may want a first name and a surname field. The data type would be text because they hold textual information. On the other hand a payment or amount field would need a numeric data type such as a decimal. A date of birth field would need a date data type.

Once the tables are created they can be linked together via what is known as primary and foreign keys. All tables should have a primary key field.

Forms

Although you can create forms quickly in an MS Access database, most times however you will want to have better control over the design. As you progress you will begin creating interfaces manually and using all the rich interface tools at your disposal such as text boxes, combo boxes, image place-holders, subforms etc.

Queries

You would create a query to give you a detailed analysis on your information. The query can then be bound to the form or report. You can run queries on their own without the need for a report or form though. You may want a query to show how many students are studying science this semester.

Reports

Access reports are very powerful and an essential tool for any serious database system. The built in wizard can be used to create a simple report quickly or you can manually design the report yourself. You can add grouping and totalling to the report if need be. You may decide you want to embed a subreport within the main report.

Once the report is created it can then saved, previewed and printed.

Macros and programming

There are bound to be times when you feel you are unable to do something with the standard tools available in MS Access. Although they are extremely powerful you may want to do something which is not within the capabilities of the general tools available. This is when you may want to look at programming. Don’t be put off by this as there is a halfway house in the form of Access macros.

The Access macro is extremely powerful and there is a huge range at your disposal. A simple macro can be created to open a form or report. Also a macro can be used to output data from your table into Microsoft Excel. There are so many macros available and they are easy to implement. It is possible that you won’t want to look at VBA programming if a macro will suffice. If you do decide to go down the programming route then it is recommended to purchase a book to learn from or attend a course.

Conclusions

Creating a database with Access gives you an enormous range of possibilities. Once mastered you have a useful skill at your disposal. There are times when your Access database may corrupt and an Access database repair is needed. This is not as bad as the old days and now stable systems can be built quickly.