5 tips to make your Access database super-fast

(Guest blogger Juan Soto is an Access MVP and founder of IT Impact, Inc. You can read more about Juan in our Q&A with him.)

We've all been there. You fire up Access and load your favorite Access app only to see it drag like molasses in winter. Try these five tips to boost the speed of your database:

1. Make sure all of your tables have primary keys
   
   Running a table without a primary key is like running a four-cylinder engine with only two active pistons. Open every table in Design view and make sure each has a primary key. Extra points: Limit your primary keys to just one field in order to stay away from using multi-field implementations. Can't settle on which single field to use? Then add an Autonumber field and make it your primary key.
   
   
2. Optimize by adding secondary indexes
   
   Do you find yourself searching on the same field over and over again? Time to add a secondary index! For example, the secondary index OrderDate will be heavily used in an order database. Go ahead and add it and any more of them that you need. (Don't overdo it by adding secondary indexes for all of your fields either).
   
   
3. Be like an atom and split
   
   If you are sharing your Access database with other people on a network and haven't split it up yet, you're in for a real treat. Use the Access Database splitter and split that bad boy already. Extra credit: leave the data file on the network and copy the frontend to your user's desktop and you'll increase speed again.
   
   
4. Use Compact and Repair
   
   Our favorite tool and I hope it becomes your's too! Use Compact and Repair at least once a month--it's just good housekeeping.
   
   
5. Load only what you need
   
   When your app starts, do you really need to display every order since the beginning of time? Ok, if you're the owner and love to see the bottom line dollars then yes, but for everyone else, reconsider what data you're loading where throughout your app. Get rid of forms that open everything and instead load forms using a "where" clause to show only the data that you need.

(Would you like to rev up your Access skills? Then join Juan this Friday at 8 AM Central Standard Time for a live webcast: "Access Kung-Fu: Mastering Access Session."  Find out more details  and register here.)