Skip to content
Step by Step Internet 🌐 Guides for learning to surf the Net

Microsoft Access tricks: Become an expert with these secret tips and advice – 2022 List

Microsoft Access tricks: Become an expert with these secret tips and advice - 2020 List

Microsoft Access is a program that is included in the Microsoft Office package along with other applications such as Word, Excel, Power Point , among others. Taking into account that Access does not have the same popularity as the other applications named above.

One of the causes of why it is not so recognized by users is due to the ignorance we have about its operation , so we never open it. However, this software acts as a database that serves to store company data, receipts, loans, expenses , among other elements.

Therefore, you can start using Access on your Windows computer and in that way create your own billing programs adapting it to your needs. Given the importance of it, here we offer the best tricks so you can start using it and become an expert.

Access vs MySQL vs Excel What is the best program for databases ?

Access vs MySQL vs Excel ¿Cuál es el mejor programa para bases de datos?

Despite being 3 very similar programs that work with database each of them perform different functions. As we already know, a database is a computerized system that aims to maintain specific information in an orderly manner and with easy access .

In the case of Access it is a basic program that allows you to take your data anywhere simply with just copy the file to a disk . While MySQL allows you to obtain your data remotely from anywhere without having such a platform, the only thing that will ask you is to have an Internet connection and username and password to log in .

Finally, Excel is a spreadsheet that will allow you to perform data analysis, statistics, accounting , and many other tools for companies.

If we compare Access, MySQL and Excel we can see that this first is a program used mainly in Microsoft Windows operating systems, which was designed to carry out data processes created and modified . It can be used by a single user for local use.

In addition, it is important to mention that although Access offers to be able to set a security password your system is not yet completely reliable.

In the case of MySQL it is a multi-user program that easily adapts to situations that require it. In addition, being a program that is managed by several users allows you to easily control all data.

It is designed primarily to work in a network and meet the needs of a number of customers. It is also much safer than Access as it requires a Internet connection and a database for authentication.

Finally we find Microsoft Excel , another program of the Office package and that will allow you to manage a lot of information from businesses, companies and personnel. For this it has a sales software which generates a file in Excel , this is made up of elements such as the date , article, units , among others.

In the same way it allows you to perform statistics, graphs and many other functions very quickly and easily. Because of this, it is considered as one of the best database programs of today .

List of the best Microsoft Access tricks to get the most out of it

Lista de los mejores trucos de Microsoft Access para sacarle el máximo provecho posible

Microsoft Access as well as all other programs in the Office package have a large number of tricks that will help the user to achieve a better performance on it. Therefore, here we are going to show you what some of them are so you can become an expert and start working in a more professional and advanced way.

To do this follow each of the tricks that we will explain below:

Center titles in Access

If you have used this program before or frequently you should know how tedious it is to be able to center the titles since this tool is within the control. Therefore, There is a way to solve this problem and do it faster.

This method consists in making the control and the report equal in width , for this, the upper left corner of the control is placed in the left margin of the report, then the size of the control to the opposite extreme . The following will be write the title that you want and we focus it with the alignment tools that are included in the format bar .

Effect 2000 and databases

In this case, the same thing happens when we work with Excel , therefore, the most convenient thing is to be able to work with four-digit dates so that you can do the correct use of them and be controlled at all times.

In order to perform this trick you will have to perform the following steps:

  • Enter the menuTools Tools.
  • Then select eachOptions•.
  • Select there the seconGeneral†.
  • section Finally, you must activate one of the two boxes in the section “Use digit box year format” .

Correct the corrupt database

One of the biggest problems for users who have old versions of Access is when they want to convert a Access 97 data to Access 2000 format , since a message appears on the screen indicating that the database is corrupt . The reason for this very common error occurs because it is not compiled correctly or some error occurred during the conversion .

In order to correct this problem, it is necessary for to log in to version 97. If you cannot access this version from another computer, then it may be possible to install it on it where is version 2000 , but this will be in another directory , for this we will follow these steps:

  • Start the database in Access 97 .
  • There you open any module in “Design View” mode.
  • Now open the Debug menu, select the “Compile” option and then click on “Save all modules”.
  • Finally we close the database.
  • Now in the “Tools” menu we look for the utilities and select “Compact database. ”
  • Here we return to convert the project to version 2000 .

Stay organized while working

Generally, there are many tips to organize and carry out a satisfactory job . One of them is to keep things simple in order and avoid those that require a lot of organization. Therefore, being able to maintain an organized database will help you save time and problems .

For this you need a well-planned and organized table , this will make the process easier to inform, include forms, consult, filter information or classify it .

Controls blank lines in address labels

One of the most frequent problems for users is tag design especially when they are all different. In the case that the address is short, it only requires one line for the text , but in many cases they reach require up to two or three lines .

In addition, it is very common that each time the tags are created, blank lines are created between the address fields , since many of them do not have enough data > to fill in all these lines.

For this there is a way to omit these blanks , this is to set the Self-compressing properties to each control .

To do this you need to follow these steps:

  • Start the “Design mode.”
  • Select the control and set the property subsequentlyAutocomprimible•.

It is important to mention that for this to work the control must not have any associated tags , otherwise, the blank will continue to be displayed.

Use easy names to simplify things

Usually, every time a data table is built , you should think about the database file , either the name of the tables and in the fields. Keep in mind that many times tables with any names are created and after a while when they are seen again it is no longer remembered what each one means of them.

The most advisable thing in these cases is to place easy names that directly indicate the action you want to achieve. Keep in mind that as you go through the tables these names become more important. Therefore, prevent mysterious or complicated names from being part of your tables.

Keep the data fields as small as possible

When we are creating a Access table it is highly recommended that each of the fields where is inserted the data is as small as possible . Typically, the system configures these text fields to contain a amount of 255 characters, a fairly good amount. But, on many occasions these fields are filled with up to 10 characters or less with a lot of space available.

Keep in mind that all these unnecessary blank spaces will simply make the system work slower and in turn make the visual part of the worksheet more uncomfortable , taking into account that the more space there is the bigger the table will be and therefore more difficult to handle .

Validate each data

Another important aspect to consider is to be able to validate all the data that we enter in the program , taking into account that in many occasions incorrect data is added without realizing it. > This will help you ensure the quality and perfection in each of the tables. With this in mind, it is important to start using validations to protect the integrity of your database in case you are not using it.

Add all possible information to your database

It is important that you dedicate a good time to your database, especially that add all possible information to it. If you plan to add all your plans and general information to this system, it is essential that you list them according to their importance. It is also recommended to list when you are going to create a new data table.

In the event that something goes wrong in the current data or you need to consult some information you can do it as long as you already have all your data added in the base, since this will allow you to have a more control . One way to have careful forecasts and considerations .

Some of the data that must be added can be the locations of the files , the specific routes of the URLs , information about how it works, what it does the database , include fields such as names, sizes, contents , among others.

Remove unnecessary subdatasheets

Typically, Microsoft Access creates views of subdatasheet by default between related tables . This function can be very efficient if you wish, but most users do not usually agree with them and therefore, the best is to prevent them from leaving . To do this, you can set the properties to “None” and prevent them from continuing to appear.

Many fields in the same table

Another of the fairly common problems presented in this program are the fields of the tables, since they become a problem when designing a report. It turns out that Access often displays a message indicating that there are many fields, which prevents from recording the design. So much so that a table can have up to a amount of 255 fields, this being a very large number.

Many users try to modify the structure of the table but the system does not allow it, this is because it has an internal counter of the number of fields that have been written. Each time they are added, moved or copied they are added. But, every time a field is deleted it is not taken by the counter , which causes when the counter reaches the 255 of an error and does not allow save the structure changes.

The best way to solve this problem is to compact the database through the following steps: “Tools”> “Database Utilities”> “Compact and repair database” .

Remove the values ​​from the access field carefully

Whenever any type of value is to be removed, it is important to do it very carefully, especially to avoid making mistakes and delete unwanted ones from the registry. Therefore, it is recommended that you check the data again and again before deleting them.

One method to verify that the correct value has been erased is after deleting it, press the “Ctrl + Z” keys so that the value is recovered immediately, if This is correct, we simply delete it again.

Save your files as you work

Many people make the mistake of not saving in any time the information they are working with, taking into account that later on they can present problems or problems that could get to cause the loss of the project.

Note that electrical power problems may occur that cause the equipment to shut down , some system error that closes all programs, which the machine suddenly turns off , among many others. That is why it is recommended to save the file every 5 or 10 minutes , this will allow you to have a backup of all the information you have added.

Do not fill in data with identical or similar data ​​span >

It is very common to see some data tables with fields that have identical data, these can be Yes / No, True / False , among others. However, it is important to clarify that this is not always a favorable step, but that affects the performance of the record .

The same happens when you have numeric fields that have two or three times the same values, therefore, repeating values ​​is not a very good alternative to improve index performance.

To check all this you can use the “Access DistinctCount” property, here you can compare all these values ​​and quickly see if the indexes of the values ​​are looking good.

Decrease the navigation of records

Being able to minimize browsing records is a very good alternative to improve the performance of linked tables , especially if we use large tables of SQL Server . It is best to use only the movements of Av Pág and Re Pág, and the movements of navigation only when they are truly necessary.

Select optimal data for tables

Being able to select the best types of data for the fields of the tables is very favorable, since this will allow you to reduce the space used on the disk to store data. Usually, when Access tries to retrieve all this data, manipulate it or write the data it usually takes time. Therefore, the secret is to select small values ​​ > that are possible to quickly store one in a particular type of data.