Get Acquainted with Microsoft Access Best Practices and Tips

• Always consider a uniform or consistent layout for forms. This will help the employees to understand the structure that who are new to the application.
• Use record sources to enhance the performance of form and loading time of drop-down list, because they have been optimized by Access.
• Note: The Select SQL statements for record sources created by Access are not optimum.
• Prevent the data entry error or glitch with the help of validation rules. This will restrict the inappropriate data entry into a form field. The use of a message box or on-screen area, let the user understand what data is required. Field’s tag property is the easiest way to encrypt the type(s) of validation according to each field requirements. Thus, one can create a loop through the field’s collection to do validations.
• In database queries, for criteria selection index the appropriate fields that you will require. Additionally, index those fields that join the tables in a query in order to enhance the execution speed.
• Choose the data types appropriately so they can utilize least amount of storage space. It will also improve the processing speed rate.
• Make sure that you use code error handling in the routines so that you have control over the logic that will apply. Once the error occurs, the MDE file will stop executing without any notice. The .mde is an add-in file used by Access. Never use macros except for custom menus for the same situation.
• Only allow the users to take a copy of the front-end access program file. This contains everything except the data and the link to the back-end data file.
• It is a better option to create two different files i.e., front-end and back-end data file. And, for fast read and write operation, put the back-end data MDB file on the network share.
• Always compress the data back-end on the regular basis. It is used to check any corruption issue in data records and table indexing too. Furthermore, it will reduce the size of database file.
• Assign the responsibility to back up the data and store at the safe location without overwriting the previous backup file. It will be determined that how much new data is re-entered by the user and how much time may have passed by until the point when the data loss is recognized.
• During compacting process, ensure that the version of MS Access is updated. Thus, there was no reissuing of already issued auto numbers. Also, create a duplicate file of the first original record.
• Try not to utilize Memo information fields over a system or network. They have a distinctive ordering that is considerably more prone to corruption during a write- back to the server.
• Utilize a principle menu form or other non-data entry form that is constantly kept open and is connected to a small program table. Also, this will stop the continued opening and shutting of the LDB (record locking document) when each form is opened then close before launching another form. It will safeguard the slowdown over a network.
• In order to link tables to the dependent child tables, use auto number field option. Because it is a complex task to modify and perform indexing manually.
• In access application, use lookup drop-down list for text fields. It stores the “long integer” auto number of the lookup table. In a situation, if historical data is required in a lookup table whose value is probably going to change, guarantee that the value in the table is additionally put into the fundamental table alongside the “long integer” key of the query table.
• All foreign key field names ought to have an indistinguishable spelling from their parent table's primary key field. This guide Microsoft Access programming, and the Access Programmer so queries will automatically connect tables, and sub forms link to the fundamental form.
• If anyone performs automation on the software component and/or controls another component using COM. Then do not worry about the version of other MS Office software being used on the same desktop machine.
• Use the similar name in both parent and child tables. It will help the users while relating an auto number field from the master table to comparing long integer field in the dependent child table to support and comprehension about the connections.
• Only share the record in read mode in a multi-user environment. To ensure that no change done by others, add a transaction which gets fails during any write operation.
• Name AutoCorrect does hamper system performance, and you can turn it off.
• Use Option Explicit On in VBA code. It explicitly declares all variables with Dim or ReDim statements. With this option, users will never receive undeclared variable name error at compile time.
• Always keep the updated version of Microsoft Jet 4.0 Engine. It is the underlying component of a database and stored data on a computer in a systematic way.
• On large set record execution, use Begin Trans and Commit Trans. It will save the record set updates to a buffer block. This block is residing temporarily in main memory and speed up the transaction process.
• Stay away from right-join in database queries until it is not needed by the program for better performance rate.

So, here are a bunch of hacks to keep your MS Access intact and free from corruption. By any chance user faces corruption in their Access database, they must use the best tool to repair Access database to fix the corruption.