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

Microsoft Access It is a program that is included in the Microsoft Office suite along with other applications such as Word, Excel, PowerPoint, among others. Taking into account that Access it does not have the same popularity as the other applications mentioned above.

One of the reasons why it is not so recognized by users is due to the lack of knowledge that we have about its operation, which is why we never open it. However, this software acts as a database which serves to be able store company data, receipts, loans, expensesamong other elements.

So you can start using Access on your Windows computer and this way create your own billing schedules adapting it to your needs. Taking into account its importance, here we offer you 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?

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 whose purpose is to be able 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 take your data anywhere just 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 it will ask you is have an Internet connection and username and password to log in.

Finally, Excel is a spreadsheet that will allow you perform data analysis, statistics, accountingand many other business tools.

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

Furthermore, it is important to mention that although Access offers power establishes a security password your system is still not entirely reliable.

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

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

we finally found Microsoft Excelanother program Office package and that will allow you to manage a large amount of information from businesses, companies and individuals. For this it has a sales software which generates a file in Excelthis is made up of elements such as the date, item, unitsamong other.

In the same way, it allows you to statistics, graphics and many other functions in a very fast and simple way. Because of this, it is considered one of the the best database programs today.

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

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

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 must know how tedious it is to be able to center titles since this tool is within control. Therefore, there is a way to solve this problem and do it faster.

This method consists of doing same width control and reportto do this the upper left corner of the control is placed in the left margin of the report, then it is enlarged the size of the control to the opposite end. next thing will be write the title you want and we center it with the tools of alignment that are included in the format bar.

Effect 2000 and databases

In this case, the same thing happens as when we work with ExcelTherefore, it is most convenient to be able to work with four digit dates so that they can be used correctly and controlled at all times.

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

• Enter the menu of “Tools”.
• Then select “Options”.
• There select the section “General”.
• Finally, you must activate one of the two boxes in the section “Use four-digit year format”.

Fix corrupt database

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

In order to correct this inconvenience it is necessary that log in to version 97. In the event that you cannot access this version from another computer, then it is possible that it can be installed on the same one where it is the 2000 versionbut this will remain in another directory, For this we will follow these steps:

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

Stay organized while you work

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

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

Control blank lines on address labels

One of the most frequent problems for users is the design of the labels especially when they are all different. In the event that the address is short, it only requires one line for the textbut on many occasions these reach require up to two or three lines.

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

For this there is a form of power skip these blanksthis consists of establishing the Self-compressing properties to each control.

To do this you need to follow these steps:

• start the “Design mode”.
• Select the control and place the property “Self-compressible”.

It is important to mention that for this to work the control must not have any tag associated with itotherwise the blank space will continue to be displayed.

Use friendly names to keep things simple

Generally, every time you build a data table you should think about the database fileeither in the name of the tables and in the fields. Note that on many occasions tables with any names are created and after a while when they see each other again, they no longer remember what each of them means.

The best thing to do in these cases is make easy names that directly indicate the action to be achieved. Note that as you go up the tables these names become more important. Therefore, it prevents mysterious or complicated names be part of your tables.

Keep data fields as small as possible

When we are creating some table in Access It is highly recommended that each of the fields where they are inserted the data is as small as possible. Usually, the system configures these text fields to contain a amount of 255 characters, a pretty good amount. But, on many occasions these fields are filled up to 10 characters or less leaving plenty of space available.

Keep in mind that all these unnecessary blank spaces will simply slow down your system and in turn slow down the visual part of your the worksheet more cumbersometaking into account that the more space there is the larger the table will be and therefore the more difficult to handle.

Validate each of the data

Another important aspect to take into account is being able to validate all the data that we introduce in the programtaking into account that on many occasions incorrect data is added without realizing it. This will help you ensure quality and perfection in each of the boards. Bearing this in mind, it is important to start use validations for protect the integrity of your database in case you are not using it.

Add as much information as possible to your database

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

In case something goes wrong in current data or 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 greater control. A form of have careful forecasts and considerations.

Some of the data to be added may be the file locationsthe specific routes of the urlsinformation on how he functions, what he does the databaseinclude fields like the names, sizes, contentsamong others.

Remove unnecessary subdatasheets

As usual, Microsoft Access create views of subdatasheet by default between related tables. This feature can be very performant if you want it, but most users don’t usually agree with it and so it’s best is to prevent them from leaving. To do this, you can set the properties to “None” and prevent these from continuing to appear.

Many fields in the same table

Another of the quite common problems that appear in this program are the fields of the tables, since they become a problem when designing a report. turns out Access frequently displays a message indicating that there are too many fields, which prevents it from being able to engrave the design. So much so that a table can have up to a quantity of 255 fields, this being a very large number.

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

The best way to solve this problem is to compact the database using the steps that we will indicate below: “Tools” > “Database utilities” > “Compact and repair database”.

Remove access field values ​​carefully

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

A method to be able to verify that the correct value has been deleted is after deleting it by pressing the keys “Ctrl + Z” for immediate recovery the value, if this is correct we simply delete it again.

Save your files as you work

many people commit the error of not saving in at any time the information with which they are working, taking into account that later they can be present drawbacks or problems that could cause the loss of the project.

Please note that you can electrical power problems occur that cause the computer to shut downsome system error what do you close all programs the machine shuts down suddenly, among many others. That is why it is recommended save the file every 5 or 10 minutesthis will allow you to have a backup of all the information you have added.

Do not fill in data with identical or similar data

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

The same thing 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.

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

Decrease log navigation

Can minimize log browsing It is a very good alternative for improve performance of linked tablesespecially if we make use of large tables of SQL-Server. It is best to use only the movements of Page Down and Page Up, Y navigation movements only when they are truly necessary.

Select optimal data for tables

power select best data types for the fields of the tables it is very favorable, since this will allow you to reduce the space used in the disk to store data. Usually when Access try to recover all these data, manipulating it, or writing the data is often time consuming. Therefore, the secret is to select small values that are possible to quickly store a in a particular type of data.

The tables must be included in the relations

In the case of tables related to one-to-one relationships at most you should implement only referential integrity with cascading deletes or updates. This ensures that no create orphan records.

When cascade is removedthis automatically removes existing child records. In that case, the system automatically takes care of these processes, which guarantees the integrity and proper functioning of the securities. Once you have an established relationship, you move on to create a hidden index what the foreign key is for, which makes the links between those tables much faster to query.

Add Secondary Indexes

If you are working with large tables and need a field or want it for a combinationthe best is create a secondary index on the fieldsthis will allow you to have better control and performance. For this, the secondary indexes offer you a greater order of magnitude.

Avoid over-indexing the table

As we already mentioned add indexes can be of great help to improve registry performancebut it is important that these are only added on those occasions that are really needed.

Therefore, it is important avoid indexing on fields that do not require it. Note that each of these indexes take up space and increase search time which takes the engine to update, delete or add new records.

Every table must have a primary key

The most recommended is that each of the tables in its database contains a primary key, as this will allow the database application can quickly locate each of specific records. In the same way, you cannot create secondary indexes in the fields of a table unless the table already contains a primary key.