Displaying an Image in a Microsoft Access Form

In a previous article, we explained how you can include a bound picture on a Microsoft Access form. This article gives further details and a working example database of displaying images in the form, that will change for each record.

As per the previous example, we will be storing a path to the image location, we won't be actually storing the image in the database. It is not a good idea to store the image file in the database itself or you will very quickly suffer from database bloat. A better approach is to store the images in a dedicated folder and then simply enter the filename/path to the file in the database.

Our example file is based upon Property Details that we would store in something like an Estate Agents/Real Estate database system. We may have a form that appears similar to the following example:

Showing the completed form, containing image related to the record.

The information contained above is stored in various tables in our database. Our tblProperties table stores that main Property related details, including the path to the image stored in a folder on our PC. The table includes the following fields:

The Property details table, containing the PhotoLink field that stores the path to the image

We have locked the Photo field in the form, so to add an image to the form we need to click on the "Add Image" button. This opens up the Browse Files dialog box (see How to Use the Common Dialog API in an Access Database) and allows us to browse for an image file on our system.

Once we have selected a file, this inserts the filepath name into the Photo text box:

Showing the file path entered into the Photo field.

After updating the Photo field, the following AfterUpdate event is called:

Private Sub memProperyPhotoLink_AfterUpdate()
    setImagePath
    Forms![frmProperties].Form.Requery
End Sub

The setImagePath function is called (shown below), before the form.Requery action re-queries the form, to display the image.

Function setImagePath()
    Dim strImagePath As String
    On Error GoTo PictureNotAvailable
    strImagePath = Me.memProperyPhotoLink
    Me.memProperyPhotoLink.Locked = True
    Me.memProperyPhotoLink.Enabled = False
    Me.ImageFrame.Picture = strImagePath
    Exit Function
PictureNotAvailable:
    strImagePath = "C:\db_Images\NoImage.gif"
    Me.ImageFrame.Picture = strImagePath
End Function

The image is then displayed in the Image Frame, and will change with each record, providing that an image is assigned to that record. If there is no image, we display a message (image), informing the user that there is no current image available.

You can download, test and view the source code for this database example (including sample images), by downloading the database from either the Microsoft Access Forms page or the Microsoft Access Downloads page.

http://www.databasedev.co.uk/image-form.html

How to Set Business Goals

Your company's goals will only be effective if you have a clear vision of what you want to achieve--and how.

A smart CEO understands the inherent value of goal setting in steering a growing business in the right direction. Unfortunately, figuring out exactly what the right direction is—and the road map to get there—isn't as much of a no-brainer.

More than 80 percent of the 300 small business owners surveyed in the recent 4th Annual Staples National Small Business Survey said that they don't keep track of their business goals, and 77 percent have yet to achieve their vision for their company.

Though the statistics are grim, they should make sense: establishing business goals involves a fair amount of introspection into what makes your business tick, and what you want its future to be. Devoting the proper amount of time to do that can be difficult in a struggling economy, but your goals will be more achievable and effective if you do.

"You have to know what you're going for, and do it with your eyes wide open," says Francisco Dao, founder and president of The Killer Pitch, a firm based in Tarzana, California, that helps companies and entrepreneurs refine their message, and former business coach and columnist for Inc. "Look at yourself in the mirror and ask yourself what it's going to take to achieve your goals."

Here's Inc.'s road map to setting (and achieving) business goals.


Setting Business Goals: Determine Your Long-Term Aims

Start by distinguishing your long-term goals from your short-term ones. Your long-term goals should have a timeline of about three to five years, says Maria Marshall, an associate professor at Purdue University in West Lafayette, Indiana, who has conducted research on small and family-owned businesses.

They should articulate your company's mission statement, reflecting the reason your company was founded. "When you think about why the company is there in the first place, goals take on a whole different meaning," says Bill Baren, a business coach and founder and president of Bill Baren Coaching, based in San Francisco. "There's more energy behind them. They don't feel forced."

Marshall says that these types of visionary goals usually fall within four general areas: service, social, profit, or growth:

• Service - Goals related to improving customer service satisfaction or customer retention.

• Social - Goals that focus on giving back to the community, through philanthropy or volunteer organizations, for example.

• Profit - Goals set to increase profits by a certain percentage.

• Growth - Goals related to the expansion of the company, through new employees, for instance.

Marshall likens each type of goal to a vacation destination, and the related short-term goals and objectives you establish afterward as the road map for getting there.

To emphasize the distinction between long- and short-term goals, Baren suggests using different language. "Look at a long-term goal as an initiative," he says. "If you're constantly calling them goals, people will say they've heard it before. [To them,] it will feel like a marathon. Sometimes, a goal needs to be positioned as something bigger."

If you're truly thinking big, you might want to consider creating a B.H.A.G., a "big, hairy, audacious, goal." The term—coined by James Collins and Jerry Porras in their 1996 article "Building Your Company's Vision"—refers to the 30-year game changing goals, like Sony trying to change the worldwide perception of Japanese products being of poor quality.

Dao also points to the example of Boeing building the 747. "They were going all in," he says. "If it didn't work, Boeing was going to go bankrupt. B.H.A.G.'s aren't impossible, but the company better be willing to bet the farm."

Regardless of just how long you want your long-term goals to be, be cognizant of how quickly they can change. Lori Becker, founder and CEO of Boston-based education publishing firm Publishing Solutions Group, says she is a fan of the five-year goal, but the current economy and some major changes in her industry have forced her to reevaluate. "Instead of a few years out, I'm now looking quarter to quarter," she says. "My goal is just to make more money than I did last year."

Dig Deeper: Francisco Dao on making goals inspirational, not delusional


Setting Business Goals: Create Short-Term Objectives

Now that you've figured out your long-term goals, you need a road map to actually achieve them. Marshall recommends an easy way to think about your short-term objectives for accomplishing your long-term aims. Make them S.M.A.R.T.:

• Specific. In order to work, objectives need to be concrete (not as abstract as your long-term aims) and highly detailed.

• Measurable. Put a figure or value, such as a dollar amount or percentage, to the objective.

• Action-oriented. Lay out which actions need to be taken by which people, and when.

• Realistic. Make goals challenging, but consider your resources so that you can actually achieve them reasonably.

• Time specific. Set a deadline to keep things on track.

"You have to understand what the long-term goal means on a daily basis," Dao says. "How do you establish the goal at its most fundamental level? If I want to increase sales annually by 24 percent, how many new customers or orders a day is that?"

The short-term objectives should ideally have a much narrower timeline. "Increasing sales 24 percent a year is a pretty big number," Dao says. "But increasing sales two percent each month seems totally doable."

Break down specific actions to be taken by specific people, assigning someone in each department involved to be accountable—and to help motivate employees.

Perhaps the most important component of these short-term goals is tying them into the long-term ones. Because you've already identified those long-term goals, it's easier to see how focusing on seemingly small details can be a step toward achieving a broader long-term aim.

For example, one of Becker's long-term goals is to keep costs and overhead under control. When she saw that she was paying over $1,000 a month for pay per click color prints on her publishing firm's copier, she invested the time to figure out why the fees were so costly. It turned out that employees were needlessly printing out e-mails with blue hyperlinks in color, so Becker made it a priority to reconfigure each computer to print in black and white by default.

Baren suggests that inspiration and accountability are also two essential components for turning goals from abstract into reality. "Accountability without inspiration is like a prison sentence," he says.

"Goals are not separate from the culture of the organization," he continues. "It's not an accident that Zappos.com has done over $2 billion in business. They've created a culture of caring—when they ask their employees to do something, they go and do it."

Dig Deeper: The Zappos Way of Managing


Setting Business Goals: Solicit Employee Input

You may have a clear vision of where you want your company to go, but it's also crucial to motivate employees so they're looking in the same direction you are. So, instead of issuing a top-down initiative, try co-creating goals with employees.

"It's really important that there is enthusiastic buy-in from your employees," Baren says. "Everyone feels like they have some ownership in the goal, as opposed to [the CEO acting as] a dictator mandating something."

Once you've asked employees to help you create your goals, get everyone on the same page. "We're very communicative, and very hands on," Becker says. "Know what your neighbor is doing so, you can help each other out." She organizes Monday morning meetings and Wednesday midweek check-ins to keep each one of her employees on track.

"Even a really good boss can't see it all," Dao says. "Get the feedback of people who are executing it on the front line."

Dig Deeper: Meeting business goals takes constant communication to employees


Setting Business Goals: Stay Organized and Focused

The reality is, a growing business will have more than just a few goals. That's when a vigilant focus and a commitment to organization comes into play.

For example, Becker keeps checklists of her short-term goals, and also uses to programs like Microsoft Excel to keep track of them. Each Monday morning, she checks in on the status of her goals. "I don't want to forget about long-term goals because so many tiny things can come up during the week to distract me," she says.

It might make sense to tackle one goal at a time. "If you become unfocused, it's like getting off on every exit of the highway," Marshall says. "You have to pick the exits you want to take very carefully, so you can maximize your strengths and minimize your weaknesses as a company."

Dig Deeper: How important is small business goal-setting?


Setting Business Goals: Be Consistent

Another problem that can arise with having many distinct goals for your business is the possibility they will conflict with each other.

"Companies will say that they want 100 percent customer satisfaction," Dao says, "but they'll also want the highest margins. Customer service is expensive, so that's not going to happen. You have to pick. Look at the big picture."

Also look out for a situation where you're unwittingly preventing employees from accomplishing a goal.

Dao points to the hypothetical example of a company that wants to improve customer service ratings, but which has an extensive automated phone tree before customers can talk to an actual person. "By the time they're on the fourteenth level, customers are already pissed off," Dao says. "How is that guy they finally speak to going to be able to get customer satisfaction back up to a 10 when there is nothing he can do about the system?"

Dig Deeper: How Sales Goals are Set


Setting Business Goals: Build a Culture of Appreciation

One important (and often overlooked) part of the business goal setting process is rewarding the employees who are actually working to achieve those goals. This isn't always a financial incentive.

"You've achieved a set of goals, and the next day you hand out a new pile of goals to work on," Baren says. "What happened to a simple thank you, and celebrating what's gone right? If you work in a company long enough where that's not practiced, the motivation starts to disappear."

http://www.inc.com/guides/2010/06/setting-business-goals.html

Internet Explorer Typed URL's History

HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\TypedURLs

12 Things We'd Tell Our Bosses if We Could

Wednesday June 16, 2010
As employees, we are told to be diligent, to follow through, to be "self-starters," to have a good attitude, to be flexible and patient and dependable and loyal and respectful. We're told there is no "I" in "team." We're told, "You should just be grateful you have a job." But surely bosses have obligations to us just as we do to them. And surely one of the biggest of those obligations is to seek out their employees' point of view.

[See 21 things hiring managers wish you knew.]

Of course, not all employees feel free to say what they're actually thinking. You know who you are! That's why in today's column, the employees of the world get to speak up. What would you say to your boss if you could say absolutely anything at all? This is your chance, people.

Here are a dozen to get you warmed up:

1. "Give me the tools I need to do a good job." If you're unsure, ask me. But, basically, time, materials, information, maybe even a little authority--these are what I need to succeed.

2. "Admit it when you make a mistake." It shows you're not afraid and is the best way to earn my respect. Whatever you do, don't act as if you never make mistakes. That's just ridiculous.

[See why you need to be a decent public speaker.]

3. "Don't treat me like a cog on a wheel." I'm an individual. If I screw up, tell me. If someone else in my group screws up, tell him. Don't blame the whole team.

4. "Ask for my opinion from time to time." I might be able to offer some good ideas if you listened to me even half as much as you expect me to listen to you.

5. "I truly need frequent feedback." Please don't wait till the year-end performance review. I can do a better job for you if you let me know what I'm doing wrong, and what I'm doing right, on a regular basis.

6. "Don't leave me hanging out to dry." When things go south I need you to be a leader and back me up. If you are not loyal to me, it is impossible for me to be loyal to you.

[See why you don't need to love your job.]

7. "I can't hear you when you shout." Maybe someone once told you intimidation is a good management tool. But seriously, yelling at employees just makes you look weak and ineffectual.

8. "Don't make me work with idiots." I realize it's not easy but if there's a problem person in the group it's your job to resolve the situation. Don't let it drag on and on. It poisons the whole workplace.

9. "Have a clear agenda." If you don't know what you want, how do you expect me to? I need you to understand your own goals, and communicate them to me clearly.

10. "Don't lie about deadlines." There is no better way to lose the trust of your people than to set "fake" deadlines. Trust me enough to be honest with me and I'll do a good job for you.

11. "Be predictable." If you behave erratically I will spend more time and energy worrying about what you're going to do next than working.

12. "Mentor me." Ask me my goals. Give me projects that help me develop and grow. I will do a fabulous job for you if you take an interest in me and my career. And that's a win-win.

http://finance.yahoo.com/news/12-Things-Wed-Tell-Our-Bosses-usnews-1082890159.html?x=0

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Notes of a SQL Server MVP and Database Administrator

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

August 23, 2008 by pinaldave

Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server. Previously I had discussed about SQL SERVER – Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Let me know what you think about this tutorial.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Saving a record through code

If you need to force a record to be saved, normally in order that you can then use that record for something, such as being printed in a report, then you have three options available:

• Using a DoMenuItem method. These have now been effectively replaced in Access 97, and are not automatically replaced when a database is converted between versions.

• Using DoCmd.RunCmd acCmdSaveRecord

• Using the Dirty property of the form. This is the recommended method, as you can firstly only force a save if needed, and secondly, you are guaranteed of ensuring that the correct form has its data saved:

  Private Sub cmdSave_Click() If Me.Dirty=True Then Me.Dirty=False End Sub

http://www.applecore99.com/frm/frm025.asp

Spell checking a Control in a Form

If you have the full version of Office installed, and want to use the Spell Checker to check that data has been entered correctly rather than using AutoCorrect to force changes, you can use code similar to this in the AfterUpdate event for the Control:

Private Sub txtDescription_AfterUpdate() If Len(Me!txtDescription & "") > 0 Then With Me!txtDescription .SetFocus .SelStart = 0 .SelLength = Len(Me!txtDescription) End With DoCmd.SetWarnings False DoCmd.RunCommand acCmdSpelling DoCmd.SetWarnings True End If End Sub

I use DoCmd.SetWarnings so that the user doesn't get a message box popping up saying that the Spelling Check is complete.

http://www.applecore99.com/frm/frm045.asp

Do not print a Report if there is no data

If you want to stop the printing of a report that has no data, then there are two steps that you need to do. Firstly, you need to use the Report's NoData event to stop it being printed:

Private Sub Report_NoData(Cancel As Integer) Cancel = True End Sub

The problem with using the NoData event programmatically is that it raises an error in the calling code. Therefore, you will also need to add an error handle to the procedure that opens the Report specifically to trap this error:

Private Sub cmdPrint_Click() On Error GoTo E_Handle DoCmd.OpenReport "rptName" sExit: Exit Sub E_Handle: Select Case Err.Number Case 2501 Case Else MsgBox Err.Description, vbCritical + vbOKOnly, "Error: " & Err.Number End Select Resume sExit End Sub

http://www.applecore99.com/rpt/rpt010.asp

Previewing a report

If you want to preview a report, Access will normally open it up full-size, so that you can't see the whole page at once. If you wish to have your reports opened as large as the screen will allow, then when you open the report add a line of code:

DoCmd.OpenReport "rptName",acViewPreview DoCmd.RunCommand acCmdFitToWindow

http://www.applecore99.com/rpt/rpt008.asp

Printing the name of the Report and/or the name of the database on a Report

Sometimes you may want to print a report that has information such as the name of the Report, or the name of the Database, in the Report's footer, to identify the print out. To do this, you need to add a Textbox to the relevant location on the Report, and then set the ControlSource to:

=[Name]

For the name of the Database, you would instead set the ControlSource to:

=Dir([CurrentDb].[Name],16)

Note that we cannot use the VBA constant vbDirectory, and so must instead use the numeric value, which is 16.

http://www.applecore99.com/rpt/rpt018.asp

20 Things You Should Never Buy Used

We all love scoring great deals on Craigslist and Ebay, but many second-hand purchases are actually terrible deals. Stay clear of these 20 used items that will end up costing you money -- or even endanger your health.

1. Cribs and children's furniture: If there's any chance that you'll put your children at risk by buying used, just buy new. Used children's furniture, especially cribs, can be a safety hazard because you can't be certain of a potential recall or if the crib was installed correctly. (See also 7 Baby Items You Don't Need to Buy.)

2. Car seats: Even if a used car seat looks OK, damaged car seats aren't uncommon. Considering that safety technology improves every year -- and the fact that car seats can go for as little as $50 -- buying new is usually the better option.

3. Bicycle helmets: Usually, a crash would only crush the foam inside the helmet casing, so the damage to the helmet may not be visible. However, since helmets are meant to protect against one accident only, buying new would be a safer bet.

4. Tires: Sometimes it's hard to tell if used tires were once part of a totaled wreck. If they have been in an accident, they're bound to be unstable and unreliable. Putting your safety at risk for the sake of saving a few bucks just doesn't add up.

5. Laptops: Because of their portability, laptops are prone to all sorts of abuse and problems. When you buy a used laptop, unless it's refurbished, you have no idea what it's been through or when important parts will die on you. You also don't get the warranties and tech support that come with buying new.

6. Software: Most software comes with a serial number that you register with the company when you activate the software on your computer. If the serial number on your use software has already been registered, you can't use it again.

7. Plasma and HDTVs: The cost for fixing or replacing the parts on plasma or HDTVs is high. Sometimes, it costs as much as buying a new TV. Considering the repair costs, you'd want to get an extended warranty, but that isn't an option if you buy your TV used.

8. DVD players: While it's smart to buy used DVDs, this doesn't apply to DVD players. DVD players have lasers that will eventually wear out. The cost to repair or replace may cost more than the player is worth.

9. Digital and video cameras: Like laptops, used digital and video cameras are likely to have been dropped and banged around. It may not be obvious, but once the damage kicks in, it'll be expensive to repair. If you know what to look for in a digital camera, you can get a great new camera without breaking the bank.

10. Speakers and microphones: Speakers and microphones are sensitive audio equipment that don't stand up well to blasting and mishandling. Like laptops and cameras, the damage may not be obvious, but their performance would be severely compromised.

11. Camera lenses: An SLR camera lens is the most expensive part of a camera. It also directly affects the quality of your images. Any damage to the lens, however slight, will show up in your photos.

12. Photo light bulbs: Not the ordinary light bulbs you use at home. We're talking about the light bulbs used with photography equipment. They're relatively expensive, but their life span is short enough that you likely won't get much use out of them if you buy second-hand.

13. Mattresses and bedding: Just think: You may be sleeping with other people's mold, mites, bacteria, and bodily fluids. Besides, even the really good mattresses are only supposed to last eight to 10 years, and it's hard know for sure how old a used mattress may be.

14. Swimsuits and undergarments: This is probably a no-brainer, but it needs to be said: Do not, do not, do not buy used swimsuits or undergarments. They're worn too close to the body -- someone else's body -- to consider buying used.

15. Wet suits: Wet suits lose the ability to keep you warm over time. If you're a scuba diver, or the last owner was one, the constant change in water pressure will eventually wear out the wet suit and make it more likely to tear.

16. Shoes: If you get used footwear, it's likely they're already molded to the last owner's feet. Poor-fitting shoes are not only uncomfortable but can cause all sorts of health problems, as well.

17. Hats: Hats are likely not cleaned before they're resold or donated. If you buy a used hat, you don't know if you're also getting skin infections, old sweat stains, hair products, and other cringe-worthy remnants. Now that's a deal you don't want.

18. Makeup: A good thing to remember about used makeup is that it's a breeding ground for bacteria and a number of contagious diseases. The great deal you found may come with pink eye and cold sores. Instead of buying used, consider making your own beauty products (it's easier than you think) or skip makeup altogether.

19. Pet supplies: Old stains and odors continue to ferment even if used pet supplies are sitting around in storage. If cleanliness is ever an issue, just say no.

20. Vacuum cleaners: Vacuums are among the heavy-duty household appliances that tend to get a lot of use and abuse. They can also cost more to fix than if you bought them new right from the start.

http://shopping.yahoo.com/articles/yshoppingarticles/383/20-things-you-should-never-buy-used/

Microsoft Access Performance Tips to Speed up Your Access Databases

Microsoft Access Table Performance Tips

Normalize Your Tables

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 Should Have a Primary Key

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.

Primary Key Should be One Field and Numeric

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 easiest is using an AutoNumber field in Access or Identity column in SQL Server. 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.

Tables Should Participate in Relationships

Related tables with one-to-one or one-to-many relationships should implement referential integrity with cascading deletes and/or updates to ensure orphan records are not created. 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.

Eliminate Unnecessary Subdatasheets

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 Optimal Data Types

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.

Add Secondary Indexes As Necessary

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.

Don't Over-Index

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 Index Fields with Lots of Identical Data

Don't apply indexes to fields that contain much the same data. For example, indexing a Yes/No field is almost always a performance degrading operation. 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.

Keep Indexes As Small As Possible

When creating a multi-field index, index only as many fields as are absolutely necessary.

---

Linked Databases and Linked Tables

Keep an Open Handle to Every Linked Database

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. To do this, open a Database variable in Visual Basic code using the OpenDatabase method. 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

Avoid record navigation wherever possible on linked tables. Only use the PageUp and PageDown movements, and the Move last movements when absolutely necessary.

Use DataEntry Mode

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.

Create a Data Entry Form

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 Data Returned by Queries

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.

Don't Use Domain Aggregate Functions

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.

Release Locks ASAP

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.

---

SQL Server and ODBC Performance Tips

Link ODBC Tables

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 Server Traffic

Minimize the amount of data returned from the server. Do this by structuring your queries to return only the fields and records needed. Reduce the number of fields returned, and put constraints on the query by using the WHERE clause.

Use Snapshots When Appropriate

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.

Use Dynasets for Large Record Sets

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. This is because all data is downloaded into RAM until the amount of RAM is exhausted. 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.

Take Advantage of the Cache

Use cache memory wherever possible when using external SQL data sources. Microsoft Access forms and reports have automatic caching mechanisms. When using recordsets in your Visual Basic code, use the CacheStart, CacheEnd and FillCache methods to maximize cache effectiveness.

Don't Force Local Query Processing

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:

• Join operations between table that are linked to different data source (I.e. a join between a SQL table and a linked Access table)
• Join operations based on query that uses the DISTINCT keyword, or a query that contains a GROUP BY clause.
• Outer joins that contain syntax that is not directly supported by the remote database server.
• The LIKE operator used with Text or Memo fields
• Multi-level grouping and totaling operations
• GROUP BY clauses that are based on a query with the DISTINCT keyword, or the GROUP BY clause.
• Crosstab queries that have more than one aggregate, or that have field, row, or column headings that contain aggregates, or that have an ORDER by clause
• User-defined functions, or functions that are not supported by the remote server
• Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

Use FailOnError For Bulk Updates

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.

Use ODBCDirect

ODBCDirect gives you almost direct access to server data through ODBC. In many cases, it is a faster and more flexible way to hit server data than that traditional Jet/Linked table technique.

---

Microsoft Access Database Performance Tips

Split Your Database into Two Databases

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. For more information, read our technical paper Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability

Use a Current Workgroup Information File

If you are using a workgroup information file (SYSTEM.MDA) created with a previous version of MS Access, convert it to the current version of Access for optimum performance.

Use the Access Performance Analyzer

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.

Run the Access Table Analyzer

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

Reduce the Size of Graphics in your Access 2007 Databases

If you embed graphics on your forms or reports, Access 2007 can store them much more efficiently. Access 2007 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. From the Access Office button, choose Access Options, Current Database. At the bottom of the Application Options section, set the Picture Property Storage Format to: Preserve source image format (smaller file size)

Compact Your Database Often To Reclaim Space

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.

Make It Look Faster

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.

---

Microsoft Access Query Optimization

Compact Often to Update Statistics

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.

You may want to defragment your disk using a program such as the Disk Defragmenter that is part of Windows before compacting your database. This leaves contiguous free disk space immediately after the database file. In theory, this make future additions to the database occur faster. You may want to experiment with this on your system.

Index Query Criteria Fields and Join Fields

Index any fields in the query that are used to set criteria. Index the fields on both sides of a join. Alternatively, you can create a relationship between joined fields, in which case an index is automatically created.

Search Access Help for: Index

Use Identical or Compatible Datatype In Join Fields

Fields that are joined in query should have the same data type, or compatible data types. For example, the Long Integer DataType is compatible with the AutoNumber data type.

Limit Fields Returned by a Query

Where possible, limit the number of fields returned by a query. This results in faster performance and reduced resource usage.

Avoid Calculated Fields and IIF Functions

Avoid calculated fields, or fields that use expressions in subqueries. Pay special care to avoid the use of immediate If (IIF) functions in sub-queries.

Don't Use Non-Indexed Fields for Criteria

Avoid using non-indexed fields or calculated fields for criteria restrictions.

Index Sort Fields

Index any fields you use for sorting. Be careful not to over-index.

Use Temporary Tables to Eliminate Running the Same Queries Over and Over

If you are processing data that's used multiple times (for instance on multiple reports), it may be faster to store intermediate results in temporary tables rather than running a series of Select queries many times. Create a temporary table to store your results. Empty the table and fill it with your data using and Append query. You can then use that table for multiple reports and forms.

Avoid Domain Aggregate Functions on Foreign Tables

Do not use domain aggregate functions (DLookup for example) in queries to access data from a table that is not in the query. Link to the table and set criteria accordingly, or create a separate aggregate (totals) query.

Use Fixed Column Headings in Crosstab Queries

Wherever possible, use fixed column headings in your crosstab queries with the PIVOT syntax.

Use BETWEEN Rather than >= and <=

Between lets the search engine look for values in one evaluation rather than two.

Use Count (*) To Count Records

If you use the Count function to calculate the number of records returned by a query, use the syntax Count(*) instead of Count([fieldname]). The Count(*) form is faster because it doesn't have to check for Null values in the specified field and won't skip records that are null.

Compile Each Query Before Delivering Your Application

When you compact your database, its data statistics are updated. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query. Before you deliver your application, compact the database, and then force each query to be recompiled. 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.

Take Advantage of Rushmore Query Optimization

Microsoft Jet uses Rushmore query optimization whenever possible. Rushmore is applied to queries run on native Access data, and on linked FoxPro and dBASE tables. Other linked data types do not support Rushmore optimization. To ensure that Rushmore optimizations are used, create indexes on all fields that are used to restrict a query's output. If you have queries that don't contain indexes on fields used in the query's restriction clause, Rushmore is not used.

Link on Primary Key Indexes Whenever Possible

To make queries run faster, you should have indexes on all fields in the query that join, restrict, or sort the data. Whenever possible, link on Primary Key fields instead of other fields. Indexes are most critical on tables with large numbers of records, so you may not see a difference on small tables. You also don't need to add secondary indexes on fields that are part of referential integrity.

Experiment With One-to-Many Restrictions

If you have a one to many join in query with a restriction, try moving the restriction to the other side of the join. For example, if the restriction is on the many side, move it to the one side. Compare performance results for both versions, and choose the fastest one.

De-Normalize If Necessary

Although you should strive to normalize your data for best performance and future flexibility, you may want to consider denormalizing some of your data if you frequently run queries with joins would benefit from such data restructuring.

Experiment With Sub Queries Instead Of Joins

If you have a query with a join that is not performing adequately, consider replacing the join with a sub query. In some cases, the sub query may cause the overall query operation to run faster.

Limit the Number of Fields Returned By Each Query

Where possible, queries should use a Where clause to constrain, or limit, the number of records returned. This results in faster performance and reduced resource usage.

---

Microsoft Access Form Optimization

Save the SQL of the Form RecordSource as a Query

We've seen situations where a saved query loads significantly faster than the same SQL string stored as the RecordSource of a form. Somehow, saved queries are optimized more than the SQL string behind the report.

Close Unused Forms

Close forms that aren't being used. Every form that is open consumes memory that could be used by other parts of your applications.

Open Forms Hidden

Consider opening your application's most commonly used forms when your application starts. Set their Visible properties to False, and then make the Visible as needed. This frontloads some performance hits to the application load event, making forms load faster when needed.

Use the DataEntry Property of a Form

If a form's record source (the table or tables accessed by the form's RecordSource property) contain a large number of records, and the form is primarily used to add new records, set the DataEntry property of the form to Yes. This precludes Access from having to retrieve existing records when the form loads.

Don't Sort A Form's Recordset

Avoid sorting records in a form's underlying record source unless a particular presentation order is absolutely necessary for the form. This makes the form load faster.

Base Forms on Queries-Minimize Fields Returned

Base forms and subforms on queries rather than tables. By doing this, you can use the query to restrict the number of fields returned, making the form load faster.

Use Lightweight Forms

Consider replacing Visual Basic code in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False. This turns the form into a Lightweight form, making it load faster. Search Access online help for "Lightweight Forms" for more information. In Access 2007, you can use embedded macros for simple operations.

Index Fields Used to Link SubForms to a Form

Index all fields in the subform that are linked to the main form. Also index all fields in the subform that are used for criteria.

Set Editing Properties on SubForms

Set the subform's AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't going to be edited. Or set the RecordsetType property of the subform to Snapshot.

Reduce the Number of Fields in ListBox and ComboBox Row Sources

In the RowSource property of listbox and combobox controls, include only the fields that are necessary.

Set AutoExpand on ComboBoxes to No

Set the AutoExpand property of comboboxes to No if you don't need the "fill in as you type" feature.

First Field of an AutoExpand ComboBox Should Be Text

In a combobox that has the AutoExpand property set to Yes, the first displayed field should be a Text data type instead of a Number data type. In order to find matches, Access needs to convert numeric values to text. If the data type is Text, this conversion can be skipped.

Optimize Bound ComboBoxes

If the bound field in a lookup combobox is not the displayed field, don't use expressions for the bound field or the displayed field, don't use restrictions (the WHERE clause) in the row source, and use single-table row sources wherever possible.

Move Linked Data Local for ComboBox and ListBox Controls

If the data that fills a list box or combo box does not change often, and that data comes from a linked table, consider moving that data's table into the local database. This can be a huge performance boost, especially if the linked table is located on a network drive.

Group Controls On Multiple Pages

Consider grouping controls on multiple pages. When the form loads, prepare only the controls on the form's first page. Defer operations on other page's controls, such as setting the record source until the user moves to that page. This makes the form load faster.

Only Load Subforms on Tab Pages when the Page is Selected

If a tab control contains several pages (tabs) with subforms on them, the form will load quicker if the subforms on the tabs that aren't visible aren't loaded right away. Since those tab pages aren't viewed yet, you can defer loading the data until the user clicks on the tab. You'll take a performance hit the first time the user clicks on it, but if they never select the tab, you would never load the data. For more information, read Microsoft Access Performance Tip: Using Late Binding for Subforms on Tab Controls

Close Forms That Contain Unbound OLE Objects

Close forms that contain unbound OLE Objects when they are not in use. When you activate an unbound OLE objects, the memory used in that operation is not released until the form is closed.

Convert Subforms to Listbox or Combobox Controls

Where possible, convert subforms to listbox or combobox controls. It is far quicker to load a control than it is to load an additional form as a subform.

Move Form Module Code to a Standard Module

You can reduce a form's load time by moving its code from the form module to a standard module. When the form loads, the form's module doesn't need to be loaded. Of course, the standard module needs to be loaded at some point, but once a standard module is loaded, it stays in memory until you close the database.

Avoid Unnecessary Property Assignments

Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.

Use the Requery Method Instead of the Requery Action

Use the Requery method instead of the Requery action. The method is significantly faster than the action.

Give Visual Feedback

Give the user some visual feedback during long operations. Consider using status meters to display a task's progress. At a minimum, use the Hourglass cursor along with a status message.

Keep Forms Lightweight With Hyperlinks

Hyperlinks in label controls make it easy to open another Access object. So instead of placing command buttons on your forms to do common operations, investigate the possibility of using a label control with the Hyperlink properties. This approach eliminates the need for a command button, and its associated event code.

Split Forms Into Multiple Pages

Consider using multi-page forms, separated by the page-break character. This allows you to present only the controls needed, and can reduce form-load time. For example, if your form has 10 combobox controls that take a long time to fill, split the form into multiple pages using the PageBreak control. Then, pick the 5 combobox controls the user is most likely to use and place them on the first page. Place the remaining controls on the second page. Load time for the form should be substantially reduced, especially if the queries filling those combo box controls are complex.

Minimize the Number of Controls

Minimize the number of controls on your form. Loading controls is the biggest performance hit when loading a form.

Avoid Overlapping Controls

Avoid overlapping controls. It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Use Graphics Sparingly

Use bitmap and other graphic objects sparingly as they can take more time to load and display than other controls.

Use the Image Control

Use the Image control instead of unbound object frames to display bitmaps. The Image control is a faster and more efficient control type for graphic images.

---

Microsoft Access Report Optimizations

Save the SQL of the Report RecordSource as a Query

We've seen situations where a saved query loads significantly faster than the same SQL string stored as the RecordSource of a report. Somehow, saved queries are optimized more than the SQL string behind the report.

Don't Sort Report Queries

Don't base reports on queries that use an ORDER BY clause. Access reports use their Sorting and Grouping settings to sort and group records: the sort order of the underlying record set is ignored.

Avoid Expressions and Functions In Sorting and Grouping

Try to avoid reports that sort or group on expressions or functions.

Index Fields Used In Sorting and Grouping

Index any fields that are used for sorting or grouping.

Base Reports on Queries-Minimize Fields Returned

Base reports and subreports on queries instead of tables. By using a query, you can restrict the number of fields returned to the absolute minimum number, making data retrieval faster.

Index Fields Used to Link Subreports

Index all the fields used to link a subreport to a report.

Index Subreport Fields Used for Criteria

Index all subreport fields used for criteria. This will cause the subreport to link its records faster. Of course, remember that over-indexing can cause performance bottlenecks when editing, adding and deleting data.

Avoid Domain Aggregate Functions in a Report's RecordSource

Do not use domain aggregate functions (such as DLookup) in a report's recordsource property. This can have a significant performance impact on how long it takes the report to open and display pages.

Use the NoData Event

Use the report's NoData event to identify empty reports. You can then display a message and close the report. This is easier than running a separate process to see if data exists for the report.

Avoid Unnecessary Property Assignments

Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.

Eliminate Unnecessary Reports

If a sub report is based on the same query as its parent report, or the query is similar, consider removing the sub report and placing its data in the main report. While this is not always feasible, such changes can speed up the overall report.

Limit the Number of Controls on Reports

Minimize the number of controls on your report. Loading controls is the biggest performance hit when loading a report.

Avoid Overlapping Controls

Avoid overlapping controls. It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Minimize Bitmap Usage

Use bitmap and other graphic objects sparingly as they can take more time to load and display than other controls.

Use the Image Control

Use the Image control instead of unbound object frames to display bitmaps. The Image control is a faster and more efficient control type for graphic images.

---

Macros Tips

Convert Macros to Visual Basic Code

Convert macros to Visual Basic code. In almost all cases, Visual Basic code runs faster than macros.

---

Microsoft Access Module/VBA Performance Tips

Make an MDE File

If possible, make an MDE file out of your database. An MDE file cannot become decompiled, so your Visual Basic code always runs at top speed. Additionally, since no source code is stored in the MDE file, the database loads faster and uses less memory.

Achieve the Compiled State

Module code is saved in two states in your Access database: the source state, and the compiled state. The source state consists of the contents of your actual modules, with full text including white space, procedure and variable names, and comments. The compiled state is the executable version of your code. All comments and white space have been removed, and a stream of executable instructions has been produced-the code is ready to be run. The difference between these two states can cause your application to run slower than molasses in January if you don't understand them.

When you run a procedure, VBA checks to see if the module containing the procedure is compiled. If it is, VBA simply runs the code. If it is not compiled, VBA compiles it by loading the code into memory, performing a syntax check, and compiling it into an executable format. If all these operations succeed, it can then run the code. You probably surmised that this process of compiling is not a free lunch-it does take some time. And herein lies the crux of the matter: compiling code takes time, and compiling lots of code takes lots of time.

So if you want your database to run as fast as possible, your task is obviously to reduce the amount of time Access spends compiling your code to a bare minimum. In fact, in an ideal application, all your code should be compiled and saved in the compiled state. So how do you go about this? Your Access database (or project in VBA parlance) is said to be in a compiled state when all modules, including form and report modules, are saved in both states in the database. This means that the original source code is stored, as is the compiled version. In such a state, Access runs much faster, because it can completely bypass the compilation process.

Getting your database into the compiled state is actually rather easy:

1. Open any module
2. From the Debug Menu, select Compile and Save All Modules

Your database is now in the compiled state. This includes form and report modules (called class modules using Access terminology) and standard modules. All VBA code that is called by your application is immediately ready for execution. There is no need for compilation. This is all fine and well, but is just as easy for your database to become decompiled. When you make certain changes to your database, it automatically becomes decompiled, which means that the compiled state that you created using the previous steps no longer exists.

How to Avoid Decompilation

So how do you avoid decompilation, or loss of the compiled state? Any of the following actions can decompile your database:

• Modify any module code
• Make changes to code-bearing objects, such as form, reports and controls, or create such code-bearing objects

So the bottom line is: to avoid decompilation, don't do the above. Its not as bad as it seems. After all, your database does not need to be in a compiled state while you are doing development work on it-it only really requires the performance benefits of the compiled state when it is actually running on your user's workstations. Therefore, if you follow these guidelines, you can enjoy peak performance from your module code:

• During development, don't use Compile All Modules. It is a waste of time, because the first time you make any changes to the module, it will decompile, or reverse the effect of Compile All Modules. Rather, use the Compile Loaded Modules option instead. This action only compiles the modules that are called by the modules you have open. This is a much quicker operation, and results in the same syntax checking that Compile All Modules does.
• When you are ready to deliver your database for testing or live use, put it into the compiled state using the steps outlined above.

Decompile to Eliminate Old VBA Compiled States

The Compact feature only compacts the data, but not the code portion of an Access database. To flush the database of old code, shrink the size of your database, and avoid the infamous Bad DLL Calling Convention error, use the /decompile command occasionally. Read this reference for more information on the Microsoft Access decompile feature.

ActiveX Controls Should Have References

If you are using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control.

Use Option Explicit

Always explicitly declare variables. Ensure this happens in every module in your application by using the Option Explicit phrase at the top of each module.

Choose the Most Efficient Variable Type

Use the most efficient variable type possible when declaring variables. For example, don't use a Long Integer when an Integer will do. Avoid Variant types because the can be inefficient and slow.

Use Early Binding

Avoid using the Object data type. Instead use the specific data type for the object you are working with. This allows Visual Basic to employ "early binding" which can be substantially faster in many cases.

Assign Things to Object Variables

If you are going to refer to a property, control, object or data access object (DAO) more than once, assign it to an object variable.

Use the Me Keyword

Use the Me keyword instead of the Form!FormName form to refer to the form of a form's module.

Avoid the Immediate If Function if Parts Run Other Code

Use the IIf (immediate if) statement sparingly. IIf() does not employ "short-circuit" evaluation. This means that both sides of the expression are always evaluated, which may not be what you want since intuitively it looks like only the criteria satisfying side would run.

Use Dynamic Arrays

Instead of fixed arrays, use dynamic arrays with the Erase and ReDim statements to make better use of memory.

Take Advantage of Demand Loading

Organize and structure you modules to take advantage of Visual Basic's demand loading architecture. When a procedure is loaded from a module, the entire module is loaded into memory. By placing related procedures in the same module, you can reduce the number of loads the Visual Basic has to make.

Eliminate Dead Code

Eliminate unused procedures and unused variables. These elements use memory unnecessarily, and slow program load and execution. Our Total Access Analyzer program finds unused procedures, variables, and constants, and variables assigned but not used.

Use Constants Instead of Variables

If you are using data that is not going to change, put it in a constant instead of a variable. This allows Visual Basic to compile the value into the constant when the module is compiled, making the execution of that code faster.

Avoid Infinite Recursion

Avoid Infinite Recursion. Don't have code that can call itself without having some type of short-circuit mechanism. This can lead to "Out of Stack Space" errors.

Declare String Data Intelligently

Visual Basic allocates stack and heap memory differently according to the type of strings you create. By understanding how this works, you can write more efficient string code. String variables in procedures that are non-static use space on the computer's stack. Use the following information to write code that minimizes stack memory usage.

• Local fixed-length strings less than or equal to 64 characters use 2 bytes for each character in the string. They don't use heap memory.
• Local fixed-length strings longer than 64 characters use 4 bytes of stack memory for a pointer to the variable in heap memory and 2 bytes of heap memory for each character in the string.Local variable-length strings use 4 bytes of stack memory for a pointer to the variable in heap memory, and a variable amount of heap memory according to the length of the string.
• If your code used a large number of fixed-length strings of 64 characters or less, you can reduce stack usage by changing the strings to local variable-length strings or making them static fixed-length strings.

Minimize OLE References

Every time you reference a Visual Basic object, method or property, you are initiating one or more calls the OLE's Idispatch interface. Each one of these calls takes time. Minimizing the number of such calls is one of the best ways to make you code run faster. You can minimize OLE references by doing the following:

• Use object variables instead of directly referring to objects
• Use the With statement and the For Each construct to minimize object references
• Move references to properties and methods outside of loops
• When you refer to a member of collection, do so with the object's index number. Referring to a collections member with a name or expression introduces extra work, and therefore, more time.

Turn Off Screen Painting

Turn off screen painting during repetitive operations that update the screen. Consider using the Application.Echo property to turn of screen painting. Depending on the type of video card in your computer, this can have moderate to dramatic effects of performance.

Don't Write Code When a Query Would Be Better

We've seen pages of VBA code written using various recordset operations when one or a few queries would do the trick. Queries are not only faster and optimizable, they're easier to understand and maintain. If you're not familiar with how to use Select, Update, Delete, and Append queries, learn them to best use Access or SQL Server.

Close Your Database a Couple of Times a Day

VBA dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is loaded into memory. As you are developing your application, you keep loading code into memory. Visual Basic for Applications does not support dynamic unloading of these modules. Because of this, RAM will begin to fill up. To boost development performance (I.e. to decrease the amount of time you spend as a developer working on your application), you may want to close the database periodically to unload the modules. Note that you do not have to close Access itself, just the database itself. However, if you have library database code loaded, you should exit Access also.

It is especially important to close your database after a Compile All Modules command. The Compile All Modules command pulls all of your code into memory. Closing and reopening the application will unload the code and enable you to develop faster because of the additional free memory.

If you are developing your application in a single-user environment, you can improve your development performance by opening the application exclusively. This allows Visual Basic for Applications to save and compile faster by eliminating multiple-user save situations.

ActiveX Controls Should Have References

If you are using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control.

Don't Use Expressions To Determine Loop Boundaries

If use loop constructs in your VBA code, such as For...Next, Do...While, etc. don't force VBA to evaluate the boundaries of the loop each time. For example, instead of saying:

For intCounter = 0 To Forms.Count - 1
...
Next intCounter

use this:

intCount = Forms.Count - 1
For intCounter = 0 To intCount
...
Next intCounter

In the second example, VBA only has to determine the value of Forms.Count once. In the first, example, the value needs to be determined for each iteration of the loop.

---

Data Access Objects (DAO) Programming Tips

Use Seek Instead of Find

Use Seek instead of Find... whenever possible. It uses indexes more efficiently and is the fastest data access method.

Search Access Help for: Seek method

Use Bookmarks For Record Navigation

Whenever possible, use bookmarks to move among records instead of using the FindNext method. The Jet engine can navigate to bookmark values quicker than doing the sequential reads required by FindNext.

Use Indexed Fields for FindRecord and FindNext

If you can't use the Seek method, and must use the FindRecord or FindNext methods, use them on indexed fields. These methods are much more efficient when used on a field that is indexed.

Search Access Help for: Find methods

Don't Use Transactions Unless Necessary

Microsoft Access lets you wrap table update code in transactions so you can rollback incomplete attempts to save data. Any code operation that adds, modifies, or deletes data can be enclosed in a transaction using the BeginTrans...CommitTrans pair.

If you do not need to rollback your updates, you can avoid using transactions and the overhead of maintaining a rollback log.

---

Jet Engine Tuning Tips

Use SHOWPLAN To See Query Execution Plans

Microsoft Jet implements a cost-based query optimizer in its query engine. During the compilation process of the query, Jet determines the most effective way to execute the query. You can view this plan using the ShowPlan registry setting.

To use this setting, use the Registry Editor that comes with your operating system and add the following key to the registry:\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.0\Engines\Debug

Under this key, add a string data type entry named JETSHOWPLAN in all capital letters. To turn ShowPlan on, set the value of this new entry to "ON". To turn the feature off, set the value to "OFF". When the feature is on, a text file called SHOWPLAN.OUT is created (or appended to if it already exists) in the current directory. This file contains the query plans.

Tune Database Performance with Jet Registry Settings

Microsoft Jet lets you tune many new parameters to tweak the engine for the best possible performance. However, this was somewhat difficult because you could only modify these settings from their defaults by creating keys in the registry, setting them to new values, and restarting Access and/or Jet. Version 3.5 of the Jet engine makes this process a whole lot easier. These registry keys are located in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5.

Also, you can use the DAO SetOption method to set these parameters at runtime. Using SetOption causes Jet to apply the changes immediately-the changes are not stored permanently in the registry, and you do not have to restart Access. If you want to get the maximum performance out of your data operations, experiment with these settings.

For more information, search Access online help for "Customizing Windows Registry Settings for Data Access Objects."

Threads

Experiment with the Threads setting in the registry. With this setting you can increase or decrease the number of operating system threads available to the Jet database engine.

MaxBufferSize

Use the MaxBufferSize registry setting to tune the data buffer used by the Jet database engine.

UserCommitSync

Use the UserCommitSync registry setting to tune performance of explicit transactions.

ImplicitCommitSync

Use the ImplicitCommitSync registry setting to tune the performance of implicit transactions.

FlushTransactionTimeout

Use the FlushTransactionTimeout registry setting to tune the performance of asynchronous write operations.

ExclusiveAsyncDelay

Use the ExclusiveAsyncDelay registry setting to tune the performance of asynchronous mode writes to exclusively opened databases.

SharedAsyncDelay

Use the SharedAsyncDelay registry setting to tune the performance of asynchronous mode writes to databases opened for shared access.

PageTimeout

Use the PageTimeout registry setting to change the delay for checking other user's changes to the database.

LockDelay

Use the LockDelay registry setting to change how long Microsoft Jet waits between retries on locked pages in a shared database.

MaxLocksPerFile

Use the MaxLocksPerFile registry setting to tune performance by setting the maximum number of locks can be placed on a database (MDB) file. For more information, search Access online help for "Customizing Windows Registry Settings for Data Access Objects", or visit this Microsoft Knowledgebase article 815281.

RecycleLVs

Use the RecycleLVs registry setting to determine how to memo, OLE and hyperlink data pages are recycled.

Use ISAMStats To See Engine Detail

Microsoft Jet contains an undocumented function called ISAMStats that shows various internal values. The syntax of the function is:

ISAMStats ((StatNum As Long [, Reset As Boolean]) As Long

where StatNum is one of the following values:

0 - number of disk reads
1 - number of disk writes
2 - number of reads from cache
3 - number of reads from read-ahead cache
4 - number of locks placed
5 - number of release lock calls

For example, to see the number of disk reads, try the following code:

Dim lngReads As Long
lngReads = DBEngine.ISAMStats (0, False)
Debug.Print lngReads

To reset the number of disk reads back to 0, do this:

lngReads = DDBEngine.ISAMStats (0, True)

---

Microsoft Access Startup Tips

Remove Code From Your Startup Form

If you use a Startup form instead on an Autoexec macro, place the Visual Basic code needed for that form in the form's module instead of a standard module. Since Access has to load your Startup form, it will automatically load that form's module, which is generally faster than loading a standard module. This technique gives your application the appearance that it is loading faster.

Minimize Startup Form Code

Minimize the amount of code in your Startup form. You may want to defer certain operations, such as opening data access objects in code, or checking objects to a later time. This technique gives your application the appearance that it is loading faster.

Don't Use ActiveX Controls on Startup Form

Don't use ActiveX controls on your Startup Form. These controls can take long to load than other controls and will subsequently slow down the load time of your application.

Automate the Deployment of the Latest Version of Your Database

Centrally manage and simplify the automatic deployment of your Access application to each desktop. When you update your application, you want to distribute it quickly and easily. It's also important to make sure each user is using the right version of Access. Our Total Access Startup program makes it easy to manage your database version and Access version by letting you distribute a simple shortcut to launch your program.

---

Multiuser Performance Tips

Split Your Database

Split your database into an application and data database. Place only the data database on the server, keeping the application objects such as forms, reports and queries locally, where they can load and execute faster.

Keep Static Data Local

Keep static data, such as lookup tables, on the local machine. Update the local tables as necessary from the server. For example, a lookup table containing the two-letter abbreviations for American states is not likely to change anytime soon. When such a table is used in a data entry application, it is a performance bottleneck to retrieve that data from the server every time it is needed. Instead, copy that table to your application's local database.

Set Options to Avoid Lock Conflicts

Avoid lock conflicts by setting the Refresh Interval, Update Retry Interval, Number of Update Retries, and ODBC Refresh Interval Settings in the Tools, Options menu.

Tune Jet with the Registry

Investigate the registry settings available for Access and Microsoft Jet for tuning data engine parameters. Search Access online help for "Performance" for more information on these settings

---

Computer Performance Tips

Increase RAM

Increase the amount of RAM on your computer. Operating systems, Office, and other programs eat up a lot of memory. With the cost of memory so low, you should have at least 1 GB of RAM. With sufficient RAM, Access can perform its queries entirely in RAM without the need to write temporary data to disk.

Avoid NTFS Compression

If you are using NTFS compression under Windows NT, move your Access application to a non-compressed drive. NTFS adds a significant amount of overhead during disk operations, and Access applications are very disk-intensive.

Make Sure Network Speed is Maximized

Most networks and network cards support 10/100 Mbps standards. Make sure your machine is using the 100 and not the 10 if you're accessing data across your network.

Keep Enough Local Disk Space Free

Access requires a fair amount of disk space to operate, especially with large databases. Operations such as running large action queries, adding lots of data, importing data, and compiling and saving module code can use a lot of additional space on a temporary basis. Additionally, transactions, and compacting the database can use a lot of disk space. A good rule of thumb is to have roughly 5-10 times the size of your largest database available in free local storage.

---

Windows Performance Tips

Keep Databases Local

Whenever possible, keep your databases on a local drive instead of on a network drive. In almost all cases, local disk access is faster than network access.

Install Access Locally

Do not install Microsoft Access, or its related Microsoft Office components on a network. Most applications, including Access and Office perform better when run from local drives.

Open Databases Exclusively

If you are opening a database, and no other users need to use the database, open the database in exclusive mode. To do this, check the Exclusive checkbox in the Open Database dialog.

Close Unneeded Applications

Free up memory by closing applications that you aren't using. If you are using the System Agent from the Microsoft Windows Plus Pack, consider turning it off to make more memory available for your access application.

Optimize Your Disk

Keep your disk in optimal shape by deleting unneeded files and emptying your Recycle Bin. Use a disk defragmenter such as the one found in the Windows 95 Accessories, System tools menu.

Close Access Occasionally to Reclaim Leaked Memory

Almost all Windows applications "leak" memory. This is due to data structures, variables, and other memory consumers that are not correctly released by the application. By closing Access, you allow Windows to reclaim this leaked memory.

Install Windows Locally

The Windows operating system should be installed on a local fixed drive on your computer. Because of the number of components involved in running Windows, a network installation of Windows causes poor performance, especially for database applications.

---

Conclusion

Microsoft Access has provided an incredibly powerful tool for the rapid development of database systems. With this power comes complexity—and complexity can lead to less than desirable performance. Use the tips in this paper to get your Access applications into top shape.

Additional Resources

Here are some additional resources that may help you create faster Access databases and/or create them in less time:

Automated Database Analysis for Best Practices

For automated analysis of your databases, check out our Total Access Analyzer program which is designed to examine every object in your database in detail, provide documentation to help you understand what's in it, how the objects interact, and offer suggestions for applying Microsoft Access Best Practices. It finds hundreds of types of errors, suggestions, and performance tips specific to your database objects.

Simplify Writing, Taking Over, and Delivering VBA Module Code

If you want to write better VBA code and do it in less time, learn about our Total Visual CodeTools program which works with Access, Office, and VB6. Total Visual CodeTools includes code builders, tools to standardize existing code (indentations, variable names, adds error handling, etc.), and tools for you to deliver more robust solutions. Learn more about our Microsoft Access coding tools.

Microsoft Access Source Code Library

Why write all the code yourself? Get our Total Visual SourceBook code library with 85,000+ royalty-free lines of code you can insert into your Access, Office and VB6 projects. Written exclusively for this professional code library, there's code you won't find anywhere else. More info on Microsoft Access code.

Microsoft Consulting Services

FMS also offers custom software development services. If you're in over your head or just don't have time, contact our team to see how we can help you maintain, enhance, and/or migrate your Microsoft Access applications. Here's more on our Microsoft Access Consulting Services.

More Technical Papers

More Tips and Techniques

http://www.fmsinc.com/MicrosoftAccess/Performance.html