How to organise an access application

Access is a powerful tool not only to manage data, but also to develop professional software.
Due to its powerful structure, it is possible to work with it in some diffent ways, while the application grows up.

For the rookies, I will expose, first, a brief of the objects you may find in an Access file.

Objects

These are the main objects and their meaning:

• Tables: where the data live. You may imagine them as sheets organised by rows and columns, where the columns are named fields, and the rows are named records. Each field has a data type, that is, all data contained on a field is always the same.
• Queries: these objects are very useful when you want to combine data of tables.
• Forms: to show and to enter data, those objects may contain text fields, check boxes, command buttons, text areas, list boxes, and other type of controls. Each form may have linked a module where to write code in VBA programming language for automate its behaviour.
• Reports: similar to forms, but only for showing data, reports are used to send information contained in the tables to a paper using a printer.
• Macros: the first step of automation in Access.
• Modules: this is VBA code. With this code you may do almost everything with Access.

Once we know what is in an Access file, let's talk about how to organise an application. 

Let's imagine a real case.

Imagine a business of selling cars and an application for this business.

What does we need?

• A list of the cars in stock
• A list of sold cars
• A list of bought cars
• A list of customers or potential customers
• Photos of all cars
• A price list

Which are the possibilities?. In other posts I will try to explain better all those points.

Very simple application

A a simple file, where there are the objects explained avobe.

Like this, all the information is in a simple file.

Every backup, nevertheless, is a copy of data, changing everyday, and the forms, reports and modules, always the same.

Simple application

The business is going well, and the company needs a new vendor, who has to work with the application too. 

What can we do?. Access allows to separate the data from the other objects, that is, one file to tables and queries, and another one to forms, reports, macros and modules.

Like this, the information may be shared by more than a user.

Medium application

Time goes by, the business is growing, and actually there are a lot of  sold cars. It's a good idea to have a list of those sold cars, it may help to see the evolution of the business, statistics, tendencies, prices, data of the customers, ... And it may be good to store information about old invoices. In most countries it's mandatory to keep only five years, at least in paper, but, are you sure you want to delete all your data after all these years?

On the other hand, the information grows in different rates: the list of suppliers is short, there is a new supplier each quarter, the list of cars is a big list, there are two or three new cars each week, almost every week... What does it means? it means that there are tables in our database very big, and others are small. If there are big tables, the time to browse them grows, and it becomes difficult and annoying to search some data.

What can we do? The solution is simple: we may divide our data file in other files, for example, one for the customers and suppliers, another one for the operations (sales and billing in general).

Complex application

The firm is growing up, and apart of selling cars there is a new service of repairing and painting them. The maintenance of the file with the forms, reports and modules is becoming hard and the file is very big, more than 20 MB.

What can we do?. We may separate the processes. Yes, we may put some forms, reports and modules in separated files and work with them as a simple application. This is transparent to the user, and very easier to the programmer in order to maintain the code. We have to work with references.

Very complex application

The final application: multiple users, specialized areas, complex processes... In this moment, the amount of data is more than big, and the time to load the information is becoming large. And, at end, we are accessing information stored in some files, not in a database server, and we need security and speed.

What can we do?. The final solution is to migrate the data to a server if we have not done before. Of course, it would be done, but now the plan is to change the access data files for a new system of data files: a data base server software. There are some systems, perhaps the most famous are SQL Server (Microsoft), MySQL (Oracle), MariaDB (community developed) or PostgreSQL (community developed). All of them may give the needed speed to access the data.

For the server, the machine, we may use a simple computer or a NAS, for example.

Well, I hope to have explained the possibilities of growing with Access.

I hope you like the post, you rate it and you make comments.

Best whishes.