Faster Microsoft Access Databases with + Tips for Improving Performance and Speed

Looking for:

Free Access Cheat Sheet | Microsoft word lessons, Computer help, Computer shortcuts – 2: Create a task

Click here to Download


This paper outlines specific performance tips you can use to improve the speed of your Microsoft Access applications. These tips apply to all versions of Access including Access , , , , , , , and Some of the tips may contradict each other but they are offered because bottlenecks differ in each situation.

Some tips may make things run faster on your system, while others degrade performance. You should evaluate each tip as it applies to your specific application running on your specific hardware. Normalize your data so the same data is stored only in one place. Remember that time is also a dimension and you may need to store historic data as well. Read our paper on Data Normalization Fundamentals for more detail. Every table in your database should have a primary key.

This allows your database application to quickly locate specific records. Additionally, you cannot create secondary indexes on a table’s fields unless that table has a Primary Key. The primary key should only be one field and ideally numeric and meaningless. Primary keys define the uniqueness of each record which can be accomplished efficiently with a single number. The primary key is also used in each secondary index for that table, so the smaller the better.

Multi-field primary keys and non-numeric text fields are less desirable. That said, some tables should simply use text fields as primary keys because they don’t change much and the tables are relatively small.

For instance, a list of countries or states no need to create a separate number for each country or state. Having a meaningless primary key means the index is stable even when data changes. Otherwise, changes in the primary key have a ripple effect through each secondary index and any other tables bound by referential integrity. With cascading deletes, the removal of the parent record, automatically deletes the child records in the corresponding table. Access the Jet Engine automatically takes care of this for you without the need to write any code, which is great for maintaining data integrity.

With a relationship established, a hidden index is created for the foreign key, so links between those tables are faster in queries. The link is also automatically drawn for you when you put the two tables on your query designer. By default, Access creates subdatasheet views between related tables. This is nice if you want it, but is a huge performance hit every time you open the table. Set this property to None if you don’t want it.

Here’s more information on the Subdatasheet Name Property. Choose the best data types for your fields. By choosing the optimal data type, you can decrease both the disk space used to store data, and the time it takes Access to retrieve, manipulate, and write data.

The general guideline is to choose the smallest data type possible to store a particular type of data. If your tables are large and you search on a field or use it in a join, create a secondary index on the field s. Secondary Indexes offer performance gains on an order of magnitude. Just as it is important to add indexes to fields that need it, it is important to avoid indexing fields that don’t need it.

Every index adds to the time it takes the database engine to update, delete and add records. Don’t apply indexes to fields that contain much the same data. Similarly, if you have a number field that only contains two or three values, an index wouldn’t be a good idea.

To check the number of unique entries in an index, use the Access DistinctCount property. Compare this value to the number of records in the table and you can quickly see if the index is doing you any good. Latest Newsletter Sign up. Blog with us and subscribe to our RSS feed. Follow us on Twitter. You can significantly increase the performance of opening tables, forms, and reports that are based on linked tables by having Microsoft Access keep the linked table’s database open.

Keep this variable open as long as your application is running. This forces Access to keep that database open, making access to linked tables much faster. For more information, read our tip on Increasing the Performance of Linked Databases.

Minimize record navigation wherever possible on linked tables, especially large SQL Server tables. If you are only going to be adding records, use the DataEntry command on the Records menu.

This data access method is more efficient for adding records because existing records are not read from the database.

If you often have the need to add new records to a linked table, consider creating an “Add Records” form and set that form’s DataEntry property to Yes. This prevents Access from attempting to retrieve all the existing records in a linked table when you need is to enter new records. Limit the number of fields and records returned by using filters or queries. This reduces the amount of data that needs to be read from the linked table, thereby according faster performance. If a query is based on one or more linked tables, avoid using functions built-in or user-defined , or domain aggregate functions in the query criteria.

When you use domain aggregate functions such as DLookup , Access must fetch all records in the function’s data source to execute the query. To improve multi-user concurrency, assume that other users will be trying to edit data in the same linked tables that you are using. In other words, keep records locked only as long as is necessary. If you are going to be accessing a SQL database table, link the table permanently.

This makes opening that table much faster in subsequent attempts. This is because linked tables cache a lot of information about the source table in your database, making the retrieval of the same structural information unnecessary after the linked table is created. Minimize the amount of data returned from the server. Do this by structuring your queries to return only the fields and records needed. Don’t open Dynaset type recordset object on SQL database tables unless you need to add or edit records, or need to see the changes made by other users.

Instead, consider using Snapshot recordsets which can be faster to scroll through. Of course, Snapshot recordsets can take longer to open since they require a full read of the source data.

If you need to retrieve a large number of records, use a Dynaset instead of a Snapshot. Snapshot type recordsets must load all records from the data source before becoming available, whereas Dynasets are available as soon as the first 20 or so records are retrieved. Also, when using a Snapshot against large ODBC data sources, you run the risk of running out of disk space on your local computer. Then, the database engine creates a temporary database to store the contents of the snapshot.

In a nutshell, when you open a snapshot, you need at least as much disk space as the largest table you are opening.

Use cache memory wherever possible when using external SQL data sources. Microsoft Access forms and reports have automatic caching mechanisms. Don’t use query constructs that cause processing to be done by Access on the local computer. The following query operations force the Jet database engine to perform local data processing:.

If you are using bulk update queries, optimize performance on the remote server by setting the FailOnError property of the Querydef object, or query to Yes. You can improve performance and application maintainability by splitting your Access database into two databases.

The “application” part of your database holds all objects except tables, and is linked to the “data” part that contains the actual tables. MDA created with a previous version of MS Access, convert it to the current version of Access for optimum performance. Microsoft Access has a useful performance tool built right into the product. From the Tools menu, select Analyze, Performance.

The Performance Analyzer allows you to select all objects or specific objects, and then runs an analysis looking for potential problems. The Performance Analyzer does not find all of the items that Total Access Analyzer does, but it does offer some tips.

The Access Table Analyzer makes it easy to properly normalize the data in your tables by breaking tables with repeated or improperly structured data into two or more tables.

This tool is available from the Access Tools, Analyze menu. If you embed graphics on your forms or reports, Access or later can store them much more efficiently. Access can convert graphics into much smaller PNG formats to significantly reduce the size of your databases. This does not affect graphics already on your forms and reports but helps if you add new graphics or replace existing ones. To activate this feature, change an Access setting. At the bottom of the Application Options section, set the Picture Property Storage Format to: Preserve source image format smaller file size.

Compacting your database reclaims unused space and makes almost all operations faster. You should do this on a regular basis. Also, be sure to compact anytime you import or delete objects in your database, or compile and save VBA code.

Learn more about Total Visual Agent for a system administrative tool to schedule compact and backup your Access databases on a regular schedule.

If you have exhausted all other performance optimization techniques, consider making your application “look” faster. Do this by displaying status messages and progress meters as your application loads forms, runs queries, and performs any other operation that may take a bit of time. While this doesn’t make your application run faster, it appears to run faster. Compact the database often. When you compact the database, you reorganize records so that they are stored in adjacent spaces, making retrieval faster.

Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. You can force a query to recompile which in turn causes it to use the latest statistics by opening it in design view, saving it, and then running it.



Basic tasks for an Access desktop database


Access might be a desktop database, but it can still be difficult to manage. These tips will help you work more effectively with less effort. Most of us lack serious Access development skills, so creating and maintaining an Access database can be tricky. In fact, you might prefer handing the project off to an expert.

If you can’t hire someone else, working faster and smarter in Access is essential. In this article, I’ll show you 10 simple ways to boost your Access productivity. These tips work in the Ribbon versions; most will work in earlier versions, including menu versions. There’s no demonstration file because you won’t need one.

If you have existing data in a Word or Excel document, it’s easy to get that data into an Access table. The easiest way is to copy and paste.

If you copy from Excel, Access uses the sheet name for the new table’s name. If copying from a Word table, Access prompts you for a table name and offers more options, as shown in Figure A.

When you’re copying raw content not a table from Word, create the table first and paste the data directly into it. Copying data from Access to Excel or Word is just as easy; simply select the records or the entire table and copy as you normally would. When exporting data from Access, you can create an Outlook task.

Let’s look at a simple example for doing so while exporting data to an Excel sheet:. You’ll find the new task in your Outlook Tasks list Figure D. If you’re lucky, you have a custom database that satisfies all your sorting and filtering needs with a quick click. The rest of us can rely on datasheet sorting options.

However, use these options with caution–working directly with the raw data is never the best route, but it’s quick!

To access sorting options, open the table in Datasheet View and click the Home tab. Click inside any field and then click Ascending or Descending. To sort multiple fields, hold down the Shift key and click the fields to add them to the selection.

As you can see in Figure E , a few clicks sorts first by vendor, then by invoice amount, and then by invoice date. Click Remove Sort to return the data to its original order at any time. Use Selection to filter data based on the data. Simply select a value and then choose an option from the Selection dropdown list Figure F. A relational database comprises many related objects. Before you start modifying things, review dependencies so you don’t accidentally affect an existing object–usually this happens when you change a field’s datatype or an object’s name, or even delete an object that’s part of a dependency.

You’ll save time spent fixing things after the fact by checking dependencies before you make changes. To see an object’s dependency, select that object in the Navigation pane, click the Database Tools tab, and then click Object Dependencies in the Relationships group. Using the Object Dependencies pane, you can quickly see all relationships with the selected object. This relationship should be considered if you modify either table. To add fields to the query design grid, you probably double-click, drag, or choose from the grid’s dropdown to add fields one at a time.

But there are several ways to add more than one field at the same time:. A customized database can be time-consuming and expensive to build and maintain, so take advantage of built-in features, such as Filter By Form.

Access will display a blank form resembling the original. If any of the controls contains a search string from a previous task , delete it. As you can see in Figure I , the navigation toolbar at the bottom of the form tells you how many records match your filter. Use the toolbar to browse through them. To remove the filter, click Toggle Filter.

Other filtering options are available by right-clicking the control. With easy-to-implement tools such as this, you’ll have a database up and running quickly. You can add customized filtering forms later. To create a more user-friendly interface, I recommend setting the following two properties for all forms, unless you have a specific reason not to:. Both settings reduce the time needed to readjust the form’s position and size when those two qualities matter, and they often do.

You’ll find these tools on the Database Tools tab in the Analyze group. Run both tools before building forms and reports on new tables to avoid time spent repairing mishaps. Considering how complex an Access database can be, finding the right template might seem impossible. However, it’s quicker to tweak a template than to start from scratch. When you launch Access, it displays the backstage, where you can open an existing database, create a new database, or search for existing templates.

It won’t take long to look at what’s available. Click one of the tiles shown in Figure J , click a link to see even more, or enter a search string. By default, the Navigation Pane groups objects, but it’s flexible enough to allow for a bit of customization. During the development stages, this option can save you a bit of time by allowing you to:. In the resulting dialog, add categories, add groups to those categories Figure K , and then return to Access.

In the Navigation Pane, drag objects from the Unassigned Objects group to your custom groups. To hide everything but the custom groups, open the Navigation Options pane, choose the custom category, and then uncheck the Unassigned Objects option. The result is a Navigation Page with fewer objects to distract users. Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.

Delivered Mondays and Wednesdays. I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible.

For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results? Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins gmail. Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world’s largest publisher of technical journals.

Figure B Identify the export destination. Figure C Create an Outlook task for the saved export. Microsoft Weekly Newsletter Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays Sign up today. Editor’s Picks. Windows 11 cheat sheet: Everything you need to know. These old programming languages are still critical to big companies. But nobody wants to learn them.

Faster Python programming: How these developers built Pyston, and where it goes next. Comment and share: 10 time-saving tips to speed your work in Access. Show Comments. Hide Comments. My Profile Log out. Join Discussion. Add your Comment.


Leave a Reply

Your email address will not be published. Required fields are marked *