Passa al contingut principal

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.
Etiquetes de comentaris:

Comentaris

Publica un comentari a l'entrada

Entrades populars d'aquest blog

How to export data from access to a text file

Introduction Hi all Today's post is about a set of functions to export data from Access to text files. I wrote the next functions years ago, when working on a big project and I had to export some of my data to another system, by text files, regularly. I needed something easy for the users, and transparent. I will explain what I did, and how. At the end of this post you will find a link to download the example of code. Feel free to modify it. Sometimes it happens that we need to export data. Of course, almost always we may write to another database. As I said before, I wrote this code to cover a need. This is a brief: I've been working for a company who mades canned fish. They had their ERP software, made by a external firm, and mantained across more than 20 years. When I entered, I had to write a software to control the production process and traceability. Of course, my processes (my software) was a subset of all the other software, and they had to communicate th
Publica un comentari a l'entrada
More info »

How to manage attached files with access. Options.

Hi all This week's post is about how to manage attached files with MS-Access and VBA. Introduction Sometimes is useful and/or necessary to store attached files in our applications. Let's see some examples: A used car seller may have a database with all the vehicles on sale, and may need to have photos of the cars, to show them to the potential customers before seeing the cars. And also may want to store digitalized the papers of the car. A real state business, has a file with each property, and may want to show the photos to the potential customers before visit, to restrict the number of properties to visit. And not only the photos, but also legal papers about the property. A doctor, may want to save information about their patients, but sometimes this information is not suitable to be stored in a record, for example a bone scan or the results of some tests, but is useful to attach to the patient file to find it more easily. A farmer may need to store data about the
Publica un comentari a l'entrada
More info »

Use of listboxes as a data summary instead data forms

Hi all This post is an extension of the previous post ( how to develop a carousel ). Here I will show how to manage a summary form linked to a detail one, in order to show data and detail them. I want to clarify that this is not "the way" to work with access, this is "a way". This is the way I use. After been developing Access applications, I've found this is a good solution, and I use it almost always. I will improve the previous example, the used cars seller adding a new form. The new form has a listbox and some buttons (add, delete, duplicate and modify) to perform those operations. All those operations are solved using VBA, as you may see. The new form is named Main. You may see in the next screenshot I've added new records. Once the form is loaded, the data are stored in the listbox using the Form_load event. The objects are named as follows: Button Add is named cmdAdd Button Duplicate is named cmdDuplicate Button Delete is named cmdD
Publica un comentari a l'entrada
More info »