Access Database Front End Too Slow – Make Faster!

How To Make Access Database Faster

See techniques specific to speeding up slow MS Access forms.

This summary provides Microsoft Access consultant tips and recommendations for making Microsoft Access databases faster. This information, combined with other pages on the site, will help the novice MS Access consulting professional to become and expert at building better, faster, more efficient databases.  Prospective clients can get an idea of what we know and how we design databases to be fast and reliable.

These techniques, combined with our Table Design Recommendations, give us the MS Access consulting tools to create databases with good performance.  Some of these recommendations are simple to implement and some are more difficult but they all speed up your Microsoft Access Database.  If you don’t have the time to invest in these techniques or your current developer isn’t able to implement them then please give us a call.
1. Table design considerations if you Access database is too slow  a. Lean towards several long tables rather than one wide (many fields) table.  Normalizing the tables will tend to lead you to this goal.  b. Whenever possible, use lookup lists for text fields.  Design the lookup lists with an auto number field to link a lookup table with the main table.  See our Access consultants  c. Do not use memo fields if at all possible – they can cause Access databases to crash under some circumstances.  Free-text data should be as short as possible to convey the information! Abbr’s work great IMHO 🙂  d. Create an autonumber field for tables linking to subordinate tables.  Index this field with no duplicates. e. Always try to use one or more data fields (not autonumber field) to create the primary key for a table.  See more about creating primary keys. f. Generally, don’t store calculated values as fields in the table – calculate them on the fly as needed.  g. Normalize the table but don’t over normalize it – there can be too much of a good thing in sometimes.h. Indexes on other fields will be discussed in section 3. below i. All text fields longer than a few characters should have Unicode Compression set to Yes. j. If you have master/detail relationships defined in your database then consider changing the table property: SubDataSheet Name from AUTO to NONE.  This property can be seen in design view of the table.  This prevents Access from dragging around the subdatasheets (detail tables) when you open a linked databasek.If you absolutely need top performance then consider optimizing the data types used to store data in the tables.  For example: if you have a lookup list that will NEVER have more than 255 entries then consider using a Byte field as the ID – you won’t be able to use autonumbers and you’ll have to increment the IDs programmatically. This technique works best for static lists.  Next is to change the 8-byte date fields to simple integer fields (2 bytes) by calculating days since some date, such as 1980.  You then use the dateadd function to display the date in normal format.  This saves 6 bytes for every date field in a table.  Also, sorting and where criteria can usually work directly off of the integer date – saving even more calculation time.  End result could be doubling or tripling the speed of table access for some tables.

2. Table relationships  a. Create table relationships using the autonumber field from the master table to the corresponding long integer field in the subordinate table b. Use the relationships screen (tools… relationships…) to relate a master table with a subordinate (detail) table.  c. Enforce referential integrity in the relationships definition popup whenever possible. d. Always check the ‘cascade delete related records’ attribute in the relationship setup screen whenever possible – you may not be able to do this when relating lookup tables because then field in the main table can never be blank; lookup fields are sometimes not filled in at time of record creation. e.When using our table design recommendations you should never check the ‘cascade update related fields’ attribute3. Query considerations and tips to make Access faster  a. Always index fields that you put in the criteria section of the query design screen.  Or, in other words, index all fields on the right side of a where clause. b. If all else fails: split the query into two or more queries.  Restrict as many records as possible in the first query and use the query as input to the next query.  c. If b. fails too, then make the 1st query a make table query to store intermediate results in a table.  d. Use stored queries only.  Don’t create a form that has a select statement as the record source – the query will either not be optimized or optimized every time it runs. 4. Report and form considerations – filtering records and combo boxes  a. Try to restrict records and perform calculations at the query level and not within the form or report (using a filter) unless absolutely necessary b. When splitting a database into a front end and back end.  Put all static lookup tables in the front end – improving performance by reducing network loading and will conserve memory (so… you don’t link to the back end database for these tables).c. For list boxes and combo boxes use stored queries anytime the query is based on a long table or the query requires joins. (see why in 3.d.)  d. If you are have thousands of entries in a combo box your form may be slow to open and slow to bring up the list of values.  One trick is to have another text field for value input.  This text field is used to restrict the list of values that are returned by using the afterupdate event and dynamically assigning the source of the combo box using the first few characters typed into the additional text box. e. If you use a switchboard menu or other menu system where there are no forms opened until a choice is selected then the performance of a linked database can degrade because the frontend database will try to delete the .ldb file on the server several times before it fails.  If you have a small form or a recordset opened all the time then the frontend will not check on the ldb file again and again and performance will be improved. 5. Compacting Microsoft Access databases to speed up Access  a. Periodically, based on the amount of data added/changed, you should compact the Access database.  To do this choose ‘Tools’… ‘Database Utilities’… ‘Compact and Repair’. b.Warning! Make sure your subversion of Access is up to date otherwise you may encounter a bug in Access that causes autonumber fields to pick previously picked numbers when adding new records.  Access 2000 current subversion is 9.0.4402 SP-1 (on computers running OSs other than Windows XP).  For Windows XP computers the current sub version is 9.0.6962 SP-3.  To view the subversion pick ‘Help’… ‘About Microsoft Access’.  Access 2002 running Windows XP subversion is10.4302.4219 SP-2 c.Do NOT set the database to compact on close. Each time the database is compacted the table statistics are refreshed.  The table statistics are used to optimize the stored queries however the optimization information is wiped out when the statistics are regenerated and the optimization is redone when each query is edited or run.  This optimization process can take up to several seconds.6. Jet 4.0 Engine   Don’t forget to keep your Jet4.0 database engine up to date – version 8 as of 12/01/037.PC Hardware Recommendations for faster databases   Obviously a faster pc will make Microsoft Access databases run faster.  As of October 2009 the desk top PC we recommend: 2 gig of ram, 2.0 gigahertz processor, and the fastest hard drive you can find.  For a server PC: 4-6 gigs of ram, 3.0 gigahertz processor, and multiple fast hard drives, one of which is dedicated to the back end database.  Some PC sellers sell fast processors with little memory and the slowest/cheapest hard drive they can find.8.Database Name and Location to improve Access performance When you have a split database (front end/back end) give the backend database a short name.  Also, when placing the backend on the server put it at or near the top-level folder.  Don’t bury it in deeply nested folders.  Server security checking can take longer when the database is deeply nested. 9. Virus Protection Slowing Database   Some virus protection software will dramatically slow the opening and sometimes the functioning of an Access database.  Exclude the database folder from active virus protection scanning.  You may find the database opens 90% faster.    10.Slow Queries: Use recordset operations with BeginTrans and CommitTrans   If you have a query, other than a simple select query, such as an update or append query that takes too long to run then the following technique will increase the speed of your Access query from minutes to a few seconds.  The reason this works is that the record set operations are written to memory (transaction buffer) rather than directly to the hard disk.

The following is a snippet of some code we use to analyze our website hits.  The key parts of the Transaction processing are bolded.

Dim wksp As DAO.Workspace
Dim special As String
Dim visitor As String
Dim visit_date As Date
Dim Engine As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
special = Chr(34) & “-” & Chr(34)

‘ making Microsoft access databases run faster with better design

Set wksp = DBEngine.Workspaces(0)   ‘ set up a transaction buffer
wksp.BeginTrans      ‘ all record set changes are buffered after this
On Error GoTo roll0

Set rst2 = db.OpenRecordset(“M_Sitestats”)
Set rst = db.OpenRecordset(“select field1 from Sitestats”)
rst.MoveFirst
Do While Not rst.EOF
visitor = Left(rst!Field1, InStr(rst!Field1, ” “) – 1)
visit_date = CDate(Mid(rst!Field1, InStr(rst!Field1, “[“) + 1, 11))
If InStr(rst!Field1, “www.google”) <> 0 Then
Engine = “Google”
GoTo write_it
End If
If InStr(rst!Field1, “yahoo.com”) <> 0 Then
Engine = “Yahoo”
GoTo write_it
End If

write_it:
With rst2
.AddNew
!visitor = visitor
!visit_date = visit_date
!Engine = Engine
.Update
End With
check_next:
rst.MoveNext
Loop

wksp.CommitTrans      ‘ here we write the changes to disk all at once

GoTo finish_it
roll0:
If Err.Number = 3022 Then

On Error GoTo roll0
Resume check_next
End If
MsgBox Err.Number & ” ” & Err.Description
wksp.Rollback   ‘ cancel everything if unexpected error

finish_it:

rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing

End Sub

This method can speed up Access database queries many fold by allowing the transactions to be cached rather than written to disk after each transaction.  Go to our Microsoft Access Visual Basic examples to see more about Access transaction processing techniques.

If all these technique fail to speed up your Access database then pop in an SSD (Solid State Drive) for less than $100 and get a 400% improvement instantly.

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016