SELECT * FROM shipment LEFT JOIN distance ON (shipment.origin = distance.origin) AND (shipment.destination = distance.destination) WHERE distance.miles > 3000 ORDER BY shipment_id;
方法 1：使用 INNER JOIN
SELECT shipment.*, distance.miles FROM shipment INNER JOIN distance ON shipment.origin = distance.origin AND shipment.destination = distance.destination WHERE distance.miles > 3000 ORDER BY shipment.shipment_id;
适用场景：
数据量适中，无需提前过滤 distance 表。
只需简单的等值连接即可满足需求。
方法 2：将筛选条件提前（推荐）
SELECT shipment.shipment_id, shipment.ship_no, filtered_distance.miles FROM shipment INNER JOIN ( SELECT origin, destination, miles FROM distance WHERE miles > 3000 ) AS filtered_distance ON shipment.origin = filtered_distance.origin AND shipment.destination = filtered_distance.destination ORDER BY shipment.shipment_id;
改进点：
子查询用别名 filtered_distance 表示。
主查询中使用 filtered_distance.miles 代替 distance.miles。
提前过滤 distance.miles > 3000，减少主查询的处理范围。
方法 3：减少 SELECT * 的使用
SELECT shipment.shipment_id, shipment.ship_no, filtered_distance.miles FROM shipment INNER JOIN ( SELECT origin, destination, miles FROM distance WHERE miles > 3000 ) AS filtered_distance ON shipment.origin = filtered_distance.origin AND shipment.destination = filtered_distance.destination ORDER BY shipment.shipment_id;
适用场景：
如果只需要特定字段，避免使用 SELECT * 提高效率。
方法 4：完全索引优化（附加建议）
如果你的查询性能依赖大表，可以添加索引来进一步优化查询速度：
distance 表复合索引：
CREATE INDEX idx_distance_origin_destination_miles ON distance (origin, destination, miles);
shipment 表单字段或复合索引：
CREATE INDEX idx_shipment_origin_destination ON shipment (origin, destination);
最终推荐的查询
SELECT shipment.shipment_id, shipment.ship_no, filtered_distance.miles FROM shipment INNER JOIN ( SELECT origin, destination, miles FROM distance WHERE miles > 3000 ) AS filtered_distance ON shipment.origin = filtered_distance.origin AND shipment.destination = filtered_distance.destination ORDER BY shipment.shipment_id;
SELECT DISTINCT(class) FROM ship WHERE EXISTS ( SELECT 1 FROM manufacturer WHERE ship.manufacturer_id = manufacturer.manufacturer_id AND manufacturer.state = ‘CA’ );
Generative AI, or Generative Artificial Intelligence, is a type of AI technology that can create new content by learning from and mimicking large amounts of data. Unlike traditional AI, which primarily focuses on analyzing and understanding data, generative AI can produce new text, images, audio, video, and more.
Key Concepts and Applications of Generative AI
1. Basic Concepts
Generative Models: Generative AI uses models that can generate new data. Common generative models include Generative Adversarial Networks (GANs), Variational Autoencoders (VAEs), autoregressive models, and Transformer models like GPT-3 and GPT-4.
Training Data: Generative AI needs a large amount of training data to learn patterns and features. By analyzing this data, the model can generate content similar to the training data.
2. Common Technologies
Generative Adversarial Networks (GANs): Consist of a generator and a discriminator. The generator tries to create realistic content, while the discriminator attempts to distinguish between the generated content and real content. They train in opposition to each other, improving the generator’s ability to produce realistic content over time.
Variational Autoencoders (VAEs): Encode and decode data to generate new samples, capable of producing diverse content.
Transformer Models: Like OpenAI’s GPT series, trained on vast amounts of text data, can generate coherent and meaningful text.
3. Application Scenarios
Text Generation: Creating articles, stories, news reports, dialogues, etc. For example, GPT-4 can generate coherent and meaningful text.
Image Generation: Producing new images, artworks, photographs, etc. GANs can generate realistic human faces.
Audio Generation: Creating music, sound effects, voice synthesis, etc. Models like WaveNet can generate natural-sounding speech.
Video Generation: Producing animations, short videos, movie effects, etc.
Data Augmentation: In machine learning, generating new data to expand training datasets and improve model performance.
4. Challenges and Ethics
Data Quality and Bias: The quality of generated content depends on the quality of training data. Biases in the training data can lead to biased generated content.
Copyright and Originality: Generated content can raise copyright issues, especially if it closely resembles the training data.
Misuse Risks: Generative AI can be used to create fake information, deepfake videos, and other potentially harmful content.
Generative AI has broad applications across various fields but also poses technical and ethical challenges. As technology advances, effectively utilizing generative AI while mitigating its potential risks will be an important task for the future.
以下是每个句子的英文、中文和日文解释：
1. Generative AI is a type of AI technology that can create new content by learning from and mimicking large amounts of data.
2. Unlike traditional AI, which primarily focuses on analyzing and understanding data, generative AI can produce new text, images, audio, video, and more.
4. Common generative models include Generative Adversarial Networks (GANs), Variational Autoencoders (VAEs), autoregressive models, and Transformer models like GPT-3 and GPT-4.
Numbers come in all kinds of formats in Excel worksheets. You may already be familiar with the pop-up window in Excel for making use of different numerical formats:
Formatting of numbers make the numbers easier to read and understand. The Excel default for numbers entered into cells is ‘General’ format, which means that the number is displayed exactly as you typed it in.
For example, if you enter a round number e.g. 4238, it will be displayed as 4238 with no decimal point or thousands separators. A decimal number such as 9325.89 will be displayed with the decimal point and the decimals. This means that it will not line up in the column with the round numbers, and will look extremely messy.
Also, without showing the thousands separators, it is difficult to see how large a number actually is without counting the individual digits. Is it in millions or tens of millions?
From the point of view of a user looking down a column of numbers, this makes it quite difficult to read and compare.
In VBA you have access to exactly the same range of formats that you have on the front end of Excel. This applies to not only an entered value in a cell on a worksheet, but also things like message boxes, UserForm controls, charts and graphs, and the Excel status bar at the bottom left hand corner of the worksheet.
The Format function is an extremely useful function in VBA in presentation terms, but it is also very complex in terms of the flexibility offered in how numbers are displayed.
How to Use the Format Function in VBA
If you are showing a message box, then the Format function can be used directly:
MsgBox Format(1234567.89, "#,##0.00")
This will display a large number using commas to separate the thousands and to show 2 decimal places. The result will be 1,234,567.89. The zeros in place of the hash ensure that decimals will be shown as 00 in whole numbers, and that there is a leading zero for a number which is less than 1
The hashtag symbol (#) represents a digit placeholder which displays a digit if it is available in that position, or else nothing.
You can also use the format function to address an individual cell, or a range of cells to change the format:
This example will add the text ‘Total Sales’ after each number, as well as including a thousands separator
Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00"" Total Sales"""
This is what your numbers will look like:
Note that cell A6 has a ‘SUM’ formula, and this will include the ‘Total Sales’ text without requiring formatting. If the formatting is applied, as in the above code, it will not put an extra instance of ‘Total Sales’ into cell A6
Although the cells now display alpha numeric characters, the numbers are still present in numeric form. The ‘SUM’ formula still works because it is using the numeric value in the background, not how the number is formatted.
The comma in the format string provides the thousands separator. Note that you only need to put this in the string once. If the number runs into millions or billions, it will still separate the digits into groups of 3
The zero in the format string (0) is a digit placeholder. It displays a digit if it is there, or a zero. Its positioning is very important to ensure uniformity with the formatting
In the format string, the hash characters (#) will display nothing if there is no digit. However, if there is a number like .8 (all decimals), we want it to show as 0.80 so that it lines up with the other numbers.
By using a single zero to the left of the decimal point and two zeros to the right of the decimal point in the format string, this will give the required result (0.80).
If there was only one zero to the right of the decimal point, then the result would be ‘0.8’ and everything would be displayed to one decimal place.
Using a Format String for Alignment
We may want to see all the decimal numbers in a range aligned on their decimal points, so that all the decimal points are directly under each other, however many places of decimals there are on each number.
You can use a question mark (?) within your format string to do this. The ‘?’ indicates that a number is shown if it is available, or a space
All the decimal points now line up underneath each other. Cell A5 has three decimal places and this would throw the alignment out normally, but using the ‘?’ character aligns everything perfectly.
Using Literal Characters Within the Format String
You can add any literal character into your format string by preceding it with a backslash (\).
Suppose that you want to show a particular currency indicator for your numbers which is not based on your locale. The problem is that if you use a currency indicator, Excel automatically refers to your local and changes it to the one appropriate for the locale that is set on the Windows Control Panel. This could have implications if your Excel application is being distributed in other countries and you want to ensure that whatever the locale is, the currency indicator is always the same.
You may also want to indicate that the numbers are in millions in the following example:
This will display ‘mill’ after every number within the formatted range.
You can use most characters as literals, but not reserved characters such as 0, #,?
Use of Commas in a Format String
We have already seen that commas can be used to create thousands separators for large numbers, but they can also be used in another way.
By using them at the end of the numeric part of the format string, they act as scalers of thousands. In other words, they will divide each number by 1,000 every time there is a comma.
In the example data, we are showing it with an indicator that it is in millions. By inserting one comma into the format string, we can show those numbers divided by 1,000.
This will be the result using only one comma (divide by 1,000):
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Creating Conditional Formatting within the Format String
You could set up conditional formatting on the front end of Excel, but you can also do it within your VBA code, which means that you can manipulate the format string programmatically to make changes.
You can use up to four sections within your format string. Each section is delimited by a semicolon (;). The four sections correspond to positive, negative, zero, and text
In this example, we use the same hash, comma, and zero characters to provide thousand separators and two decimal points, but we now have different sections for each type of value.
The first section is for positive numbers and is no different to what we have already seen previously in terms of format.
The second section for negative numbers introduces a color (Red) which is held within a pair of square brackets. The format is the same as for positive numbers except that a minus (-) sign has been added in front.
The third section for zero numbers uses a color (Green) within square brackets with the numeric string the same as for positive numbers.
The final section is for text values, and all that this needs is a color (Blue) again within square brackets
This is the result of applying this format string:
You can go further with conditions within the format string. Suppose that you wanted to show every positive number above 10,000 as green, and every other number as red you could use this format string:
This format string includes conditions for >=10000 set in square brackets so that green will only be used where the number is greater than or equal to 10000
This is the result:
Using Fractions in Formatting Strings
Fractions are not often used in spreadsheets, since they normally equate to decimals which everyone is familiar with.
However, sometimes they do serve a purpose. This example will display dollars and cents:
Remember that in spite of the numbers being displayed as text, they are still there in the background as numbers and all the Excel formulas can still be used on them.
Date and Time Formats
Dates are actually numbers and you can use formats on them in the same way as for numbers. If you format a date as a numeric number, you will see a large number to the left of the decimal point and a number of decimal places. The number to the left of the decimal point shows the number of days starting at 01-Jan-1900, and the decimal places show the time based on 24hrs
MsgBox Format(Now(), "dd-mmm-yyyy")
This will format the current date to show ’08-Jul-2020’. Using ‘mmm’ for the month displays the first three characters of the month name. If you want the full month name then you use ‘mmmm’
You can include times in your format string:
MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM")
This will display ’08-Jul-2020 01:25 PM’
‘hh:mm’ represents hours and minutes and AM/PM uses a 12-hour clock as opposed to a 24-hour clock.
You can incorporate text characters into your format string:
Excel has a number of built-in formats for both numbers and dates that you can use in your code. These mainly reflect what is available on the number formatting front end, although some of them go beyond what is normally available on the pop-up window. Also, you do not have the flexibility over number of decimal places, or whether thousands separators are used.
General Number
This format will display the number exactly as it is
MsgBox Format(1234567.89, "General Number")
The result will be 1234567.89
Currency
MsgBox Format(1234567.894, "Currency")
This format will add a currency symbol in front of the number e.g. $, £ depending on your locale, but it will also format the number to 2 decimal places and will separate the thousands with commas.
The result will be $1,234,567.89
Fixed
MsgBox Format(1234567.894, "Fixed")
This format displays at least one digit to the left but only two digits to the right of the decimal point.
The result will be 1234567.89
Standard
MsgBox Format(1234567.894, "Standard")
This displays the number with the thousand separators, but only to two decimal places.
This displays ‘Off’ if the number is zero, otherwise displays ‘On’
The result will be ‘On’
General Date
MsgBox Format(Now(), "General Date")
This will display the date as date and time using AM/PM notation. How the date is displayed depends on your settings in the Windows Control Panel (Clock and Region | Region). It may be displayed as ‘mm/dd/yyyy’ or ‘dd/mm/yyyy’
The result will be ‘7/7/2020 3:48:25 PM’
Long Date
MsgBox Format(Now(), "Long Date")
This will display a long date as defined in the Windows Control Panel (Clock and Region | Region). Note that it does not include the time.
The result will be ‘Tuesday, July 7, 2020’
Medium Date
MsgBox Format(Now(), "Medium Date")
This displays a date as defined in the short date settings as defined by locale in the Windows Control Panel.
Displays a short date as defined in the Windows Control Panel (Clock and Region | Region). How the date is displayed depends on your locale. It may be displayed as ‘mm/dd/yyyy’ or ‘dd/mm/yyyy’
The result will be ‘7/7/2020’
Long Time
MsgBox Format(Now(), "Long Time")
Displays a long time as defined in Windows Control Panel (Clock and Region | Region).
The result will be ‘4:11:39 PM’
Medium Time
MsgBox Format(Now(), "Medium Time")
Displays a medium time as defined by your locale in the Windows Control Panel. This is usually set as 12-hour format using hours, minutes, and seconds and the AM/PM format.
The result will be ’04:15 PM’
Short Time
MsgBox Format(Now(), "Short Time")
Displays a medium time as defined in Windows Control Panel (Clock and Region | Region). This is usually set as 24-hour format with hours and minutes
Dangers of Using Excel’s Pre-Defined Formats in Dates and Times
The use of the pre-defined formats for dates and times in Excel VBA is very dependent on the settings in the Windows Control Panel and also what the locale is set to
Users can easily alter these settings, and this will have an effect on how your dates and times are displayed in Excel
For example, if you develop an Excel application which uses pre-defined formats within your VBA code, these may change completely if a user is in a different country or using a different locale to you. You may find that column widths do not fit the date definition, or on a user form the Active X control such as a combo box (drop down) control is too narrow for the dates and times to be displayed properly.
You need to consider where the audience is geographically when you develop your Excel application
User-Defined Formats for Numbers
There are a number of different parameters that you can use when defining your format string:
Character
Description
Null String
No formatting
0
Digit placeholder. Displays a digit or a zero. If there is a digit for that position then it displays the digit otherwise it displays 0. If there are fewer digits than zeros, then you will get leading or trailing zeros. If there are more digits after the decimal point than there are zeros, then the number is rounded to the number of decimal places shown by the zeros. If there are more digits before the decimal point than zeros these will be displayed normally.
#
Digit placeholder. This displays a digit or nothing. It works the same as the zero placeholder above, except that leading and trailing zeros are not displayed. For example 0.75 would be displayed using zero placeholders, but this would be .75 using # placeholders.
. Decimal point.
Only one permitted per format string. This character depends on the settings in the Windows Control Panel.
%
Percentage placeholder. Multiplies number by 100 and places % character where it appears in the format string
, (comma)
Thousand separator. This is used if 0 or # placeholders are used and the format string contains a comma. One comma to the left of the decimal point indicates round to the nearest thousand. E.g. ##0, Two adjacent commas to the left of the thousand separator indicate rounding to the nearest million. E.g. ##0,,
E- E+
Scientific format. This displays the number exponentially.
: (colon)
Time separator – used when formatting a time to split hours, minutes and seconds.
/
Date separator – this is used when specifying a format for a date
– + £ $ ( )
Displays a literal character. To display a character other than listed here, precede it with a backslash (\)
User-Defined Formats for Dates and Times
These characters can all be used in you format string when formatting dates and times:
Character
Meaning
c
Displays the date as ddddd and the time as ttttt
d
Display the day as a number without leading zero
dd
Display the day as a number with leading zero
ddd
Display the day as an abbreviation (Sun – Sat)
dddd
Display the full name of the day (Sunday – Saturday)
ddddd
Display a date serial number as a complete date according to Short Date in the International settings of the windows Control Panel
dddddd
Displays a date serial number as a complete date according to Long Date in the International settings of the Windows Control Panel.
w
Displays the day of the week as a number (1 = Sunday)
ww
Displays the week of the year as a number (1-53)
m
Displays the month as a number without leading zero
mm
Displays the month as a number with leading zeros
mmm
Displays month as an abbreviation (Jan-Dec)
mmmm
Displays the full name of the month (January – December)
q
Displays the quarter of the year as a number (1-4)
y
Displays the day of the year as a number (1-366)
yy
Displays the year as a two-digit number
yyyy
Displays the year as four-digit number
h
Displays the hour as a number without leading zero
hh
Displays the hour as a number with leading zero
n
Displays the minute as a number without leading zero
nn
Displays the minute as a number with leading zero
s
Displays the second as a number without leading zero
ss
Displays the second as a number with leading zero
ttttt
Display a time serial number as a complete time.
AM/PM
Use a 12-hour clock and display AM or PM to indicate before or after noon.
am/pm
Use a 12-hour clock and use am or pm to indicate before or after noon
A/P
Use a 12-hour clock and use A or P to indicate before or after noon
a/p
Use a 12-hour clock and use a or p to indicate before or after noon
Over 100 Tips for Faster Microsoft Access Databases
This paper outlines specific performance tips you can use to improve the speed of your Microsoft Access applications. These tips apply to all versions of Access including Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and 97.
Some of the tips may contradict each other but they are offered because bottlenecks differ in each situation. Some tips may make things run faster on your system, while others degrade performance. You should evaluate each tip as it applies to your specific application running on your specific hardware.
Normalize your data so the same data is stored only in one place. Remember that time is also a dimension and you may need to store historic data as well. Read our paper on Data Normalization Fundamentals for more detail.
Every Table 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.
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.
Minimize record navigation wherever possible on linked tables, especially large SQL Server tables. Only use the Page Up and Page Down 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.
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.
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 Microsoft 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 Microsoft Access 2007 or Later Databases
If you embed graphics on your forms or reports, Access 2007 (or later) 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.
If you have exhausted all other performance optimization techniques, consider making your application “look” faster. Do this by displaying status messages and progress meters as your application loads forms, runs queries, and performs any other operation that may take a bit of time. While this doesn’t make your application run faster, it appears to run faster.
Compact the database often. When you compact the database, you reorganize records so that they are stored in adjacent spaces, making retrieval faster. Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. You can force a query to recompile (which in turn causes it to use the latest statistics) by opening it in design view, saving it, and then running it.
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.
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.
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.
Convert macros to Visual Basic code. In almost all cases, Visual Basic code runs faster than macros. This obviously doesn’t apply if you are using macros for a SharePoint deployment of your Access forms.
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:
Open any module
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. It’s 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 classes, procedures, variables, constants, enums, user defined types, 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 off 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.
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.
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 parameters to tweak the engine for the best possible performance. These registry keys are located in:
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:
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.
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
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 4 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.
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.
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.
Sometimes, you may have to perform maintenance tasks on a Microsoft Access database, such as compacting or repairing, making backup copies, or making design modifications. Many of these operations require that all users exit the database. However, there is no built in way to force users to quit Microsoft Access. And it is not a good idea to just disconnect the user from Network solutions. That can cause the database to become corrupted.
This article shows you one approach that you can use to gracefully shut down a front-end Access database application. You can also use many of these concepts to compact or repair the database, make backup copies, and so on.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
More information
How the Solution Works
The solution works as follow. On a server, there is a file in a folder. This file can be named anything. For this solution, a file named chkfile.ozx is used. When this file is renamed or deleted, it notifies the front-end Access application that it has to close.
A form is built that opens when users start the front-end database application. This form checks for the existence of the file on the server at a set interval. It uses the TimerInterval property and the OnTimer event to do this.
If the file is found, nothing happens. If the file is not found, the form will display another form to warn the user that the database will be automatically closed in a specified amount of time.
Note
This solution does not use the MsgBox function to warn the user. The MsgBox function will wait for user input before it runs any code. This would defeat the purpose of the solution.
To gracefully close client sessions, this solution renames the file that is being checked. When everything that has to be accomplished is finished, this solution renames the file back to the original name. This lets the users know that they can start the front-end database again.
This process can also be automated to provide unattended operation by using a scheduled service on the server that renames chkfile.ozx at specified times.
Steps to Create a Sample Scenario
To demonstrate how this solution works, you will have to have the following:
A folder on your hard disk with a path of C:\MyData.
An empty file. This solution will check for the presence of this file.
A split database design with the tables in a back-end database file and links to that table in the front-end database. The front-end database will contain the code that checks for the existence of the file at a set interval, and then warns the user.
Creating the folder for the sample application
Create a folder in the root directory of drive C, and name it MyData.
Creating the empty text file
Create a new text file in the MyData folder, and name it chkfile.txt.
Rename the text file so that it has an extension of ozx (chkfile.ozx). When you are prompted, confirm that you want to change the file extension.
Creating the back-end database
Create a new database in the C:\MyData folder, and name it Northwind_Be.mdb.
Import the Customers table from the Northwind sample database into the Northwind_Be.mdb database. (By default, Northwind is located in the C:\Program Files\Microsoft Office\Office10\Samples.)
Close the database.
Creating the front-end database
Create another new database, and name it Northwind_Fe.mdb.
Link the Customers table from the Northwind_Be.mdb database into the new Northwind_Fe.mdb
Create an AutoForm based on the linked Customers table and save it as frmCustomers. Close this form.
Creating the form with code that checks for the existence of the file
Create an unbound form, and save it with the name frmAppShutDown. In a production database, this form would normally always be open but not visible. For this sample, it can be left open as usual.
Set the TimerInterval property of the form to 60000 milliseconds. This is equal to one minute. (For your own solution, you can increase or decease this time interval.)
In Microsoft Office Access 2003 or in earlier versions of Access, in Design view of the frmAppShutDown form, click Code on the View menu. In Microsoft Office Access 2007, in Design view of the frmAppShutDown form, click the Design tab, and then click View Code in the Tools group. Type or paste the following code:
VBCopy
Option Explicit
Dim boolCountDown As Boolean
Dim intCountDownMinutes As Integer
Private Sub Form_Open(Cancel As Integer)
' Set Count Down variable to false
' on the initial opening of the form.
boolCountDown = False
End Sub
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
Dim strFileName As String
strFileName = Dir("c:\MyData\chkfile.ozx")
If boolCountDown = False Then
' Do nothing unless the check file is missing.
If strFileName <> "chkfile.ozx" Then
' The check file is not found so
' set the count down variable to true and
' number of minutes until this session
' of Access will be shut down.
boolCountDown = True
intCountDownMinutes = 2
End If
Else
' Count down variable is true so warn
' the user that the application will be shut down
' in X number of minutes. The number of minutes
' will be 1 less than the initial value of the
' intCountDownMinutes variable because the form timer
' event is set to fire every 60 seconds
intCountDownMinutes = intCountDownMinutes - 1
DoCmd.OpenForm "frmAppShutDownWarn"
Forms!frmAppShutDownWarn!txtWarning = "This application will be shut down in approximately " & intCountDownMinutes & " minute(s). Please save all work."
If intCountDownMinutes < 1 Then
' Shut down Access if the countdown is zero,
' saving all work by default.
Application.Quit acQuitSaveAll
End If
End If
Exit_Form_Timer:
Exit Sub
Err_Form_Timer:
Resume Next
End Sub
Save and then close the form.
Creating the form that will serve to warn the user
Note
Do not use the MsgBox function to warn the user. The MsgBox function will wait for user input before it runs any code. This would defeat the purpose of the solution.
Create an unbound form, and name it frmAppShutDownWarn. Add the following text box control:adocCopyName: txtWarning Type: Textbox
Save and close the form.
Create a macro that will open the frmCustomer form and the frmAppShutDown form at startup. Name the macro autoexec.
Close and reopen the database.
Rename chkfile.ozx to chkfile.old.
Timing of Solution Events
Note
All of the following times are approximate, and they start after the renaming of chkfile.ozx.
One minute or less: Northwind_FE.mdb will notice that the file being checked is missing.
Two minutes: A form will be opened in Northwind_FE.mdb, notifying you that the database will close in one minute.
Three minutes: Northwind_FE.mdb will automatically close, and save all work.
Occasionally we come across project requirements that include the ability to do sequential numbering in Access on a set of data. We cannot just use the auto-number data type because there is no promise of sequentiality but only uniqueness. In other words, if someone creates a record then later deletes it, there will be a gap in the sequence. So, what are our options? There are actually several ways, each with its pros and cons.
1. Let SQL Server Do It
If you have a SQL Server backend, you could just use the windows function ROW_NUMBER() and define it as a part of a SQL Server view. This is probably the simplest, no-nonsense method that works well for any purposes. This was introduced in SQL Server 2005 so chances are high you can use it if you can create views.
Swell, but I don’t have a SQL Server backend.
In fact, we have had run into cases like this and we can still do it without the ROW_NUMBER() function. Let’s look at few sequential numbering methods that can be done entirely in Access…
2. Use a Report with Running Sum Property
If your objective is to generate a report, then you don’t want numbering to be a part of your query but rather a part of the report itself. You can use the “Running Sum” property of a textbox on a report to do all the work for you:
3. …But I’m Exporting Raw Query Data
The Running Sum method works great as long you are happy with creating and running an Access report, but not all requirements want the data in an Access report. Sometimes we do need to export a content of query and therefore we can’t use a report (or at least we don’t want extraneous formatting contents when we try to export a report).
To do this entirely in a query, we need a bit of help from the VBA. We need to create a brand new VBA standard module and define a module-level variable, and two functions: Private lngRowNumber As Long
Public Function ResetRowNumber() As Boolean lngRowNumber = 0 ResetRowNumber = True End Function
Public Function RowNumber(UniqueKeyVariant As Variant) As Long lngRowNumber = lngRowNumber + 1 RowNumber = lngRowNumber End Function Then within your query’s SQL, you should call the functions as so: SELECT RowNumber(TablePrimaryKey) As RowNum FROM SomeTable WHERE ResetRowNumber() ORDER BY SomeColumn; Let’s look at why we have this setup. It seems strange to put a ResetRowNumber() call in a WHERE clause, doesn’t it? However, the WHERE clause is actually resolved prior to the SELECT clause. (For those who wants to geek out on SQL internals, Itzik Ben-Gan, a SQL Server MVP has a great post that outlines the logical query processing. Though this is specific to SQL Server, the Access database engine as well the majority of RBMS engines generally follow the same outline). This gives us a convenient point to ensure that the module level variable lngRowNumber is always correctly reset at the right time (e.g. before we start returning records from a query).
Now, what’s the deal with the UniqueKeyVariant parameter for the RowNumber() function? We are not even using it all! True, but if we didn’t, Access will call RowNumber() only once for an entire query and thus we get “1” for the entire dataset. That is a good way to save on CPU processing – why call Now() for each row? In this case, we want to go against this CPU-saving measure and ensure that the RowNumber() is in fact called once per row to generate a new number for that row. Thus, we pass in a parameter — a primary key of a table is a good choice for this purpose. So we get a nice sequential number generated all in the query with VBA function.
Finally, though it won’t be required, having a ORDER BY clause is probably going to be very essential. Without an explicit sort applied to query, you might find that sequential numbers are sequential… but to the wrong rows! It’s entirely up to you to define how it should be sorted so the sequential numbers make sense.
4. Hey, I Opened This in a Datasheet and It’s All Messed Up!
Ah, that’s the con – this works beautifully when you are in fact exporting data but when you are viewing the query’s output in a datasheet or a form, as you scroll around, Access will be re-evaluating the rows, including a call to the RowNumber(). But when it repeatedly calls RowNumber(), of course it keeps incrementing blindly, without any regards to whether a number was already generated for that row.
So, if you wanted to use this idea in a form or datasheet, let me stop and first remind you – if this is going to be non-updatable, you can just embed a report as a subreport within a form and thus use Running Sum. But let’s discuss the case where you need to be able to edit data in the forms, even with sequential numbering generated for the data you are viewing. This means we need to be able to tie a sequential number to a specific row.
The best way to do this is to use a primary key. In cases where a query is complex and joins several tables, we may need to use the primary key of the table that’s on the bottom of the relationship hierarchy or possible a concatenation of multiple tables’ unique key so that the expression will be unique and thus we can identify which sequential number we have assigned.
We will then modify the mdlRowNumbers module accordingly: Private lngRowNumber As Long Private colPrimaryKeys As VBA.Collection
Public Function ResetRowNumber() As Boolean Set colPrimaryKeys = New VBA.Collection lngRowNumber = 0 ResetRowNumber = True End Function
Public Function RowNumber(UniqueKeyVariant As Variant) As Long Dim lngTemp As Long
On Error Resume Next lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant)) If Err.Number Then lngRowNumber = lngRowNumber + 1 colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant) lngTemp = lngRowNumber End If
RowNumber = lngTemp End Function Basically, the only difference is that we now maintain a VBA.Collection, and this time we do make use of the UniqueKeyVariant parameter, using it as a key to locate the previously assigned number. If we don’t find it, we add to the VBA.Collection. The SQL to use those 2 functions remains identical. The downside is that if the query generates millions worth of rows, the memory may run out due to a large VBA.Collection. However, if a form is bound to that large dataset, it will likely have several other problems anyway so VBA.Collection should not be a problem for forms that loads a few hundred rows. The other downside is that VBA.Collection will persist indefinitely even long after the query has “finished.” Back to form example, you might need to remember to call the ResetRowNumber() function on a form’s close to ensure that you dispose of the VBA.Collection and free up the memory.
5. But I Need it for Multiple Forms; I Can’t Have Several VBA.Collection Instances!
The problem with the previous example is that it’s OK since you only need it in one place but if you will have several places where it is used, you now have to qualify the instance of VBA.Collection to each place – not insurmountable but too easy to get messy and hairy very fast.
However, there is a property we can use to get ourselves out of this problem and that is the AbsolutePosition property of the recordset. We can create a textbox, bind it to expression “=RowNumber()” and then create a function on the module behind the form that references the recordset: Public Function RowNumber() As Variant On Error Resume Next If Me.NewRecord Then If Me.Dirty Then RowNumber = Null Exit Function End If End If
With Me.RecordsetClone .Bookmark = Me.Bookmark RowNumber = .AbsolutePosition + 1 End With
If Err.Number Then RowNumber = Null End If End Function This requires no pre-knowledge of a primary key and only assumes that when you load the form initially, it is already sorted. Any records newly inserted will get the next bigger number. However, if you requery and it causes the newly inserted rows to be re-sorted, they will be now given a new number that relates to their actual position within the set, which can be either good or bad, depending on your specific requirements.
You might find it useful to ensure that the RowNumber() is called after insert of records, perhaps with this code: Private Sub Form_AfterInsert() 'Assumes there's a textbox named txtRowNumber 'and it is bound to expression "=RowNumber()" Me.txtRowNumber.Requery End Sub You also will find the code much easier to re-use, perhaps by packaging into a class module and thus encapsulate the implementation while sharing among several forms without worrying about VBA.Collections bloating since you are just using an innate property of the Recordset object.
6. Use the Auto-Number, Luke
Yet another approach that does not require embedding VBA functions into your SQL is to just create a new table and insert data into it. That requires at least three SQL statements to do this: DROP TABLE tmpSomeTempTable;
INSERT INTO tmpSomeTempTable ( SomeColumn ) SELECT SomeColumn FROM tblSomeTable ORDER BY SomeColumn; Note this works only because we create a brand new table, add an autonumber column to it as well any other columns we need then insert records into it. It’ll be contiguous – as long we don’t delete any records from the table. Unfortunately, creating a brand new table every time we run this will result in bloat of the Access file – if you can do it in a separate Access file, all the better so that you can compact it when you need to.
This also either requires 3 separate saved queries or 3 separate calls to DAO.Database.Execute methods, for each SQL statement, in addition to handling the error when the temporary table doesn’t exist. The simplest and clean method for this case could be this following VBA code: Dim db As DAO.Database Set db = CurrentDb()
db.Execute strDropTableSQL ‘Do not use dbFailOnError parameter db.Execute strCreateTableSQL, dbFailOnError db.Execute strInsertDataSQL, dbFailOnError By omitting the dbFailOnError parameter only for the DROP TABLE statement, we won’t get a runtime error about a table not existing but get runtime errors if we can’t create the table or insert data for some reasons. I’m also not a big fan of creating multiple saved queries that are meant to be logically grouped together – it gets quite cluttered when there are several saved queries in a navigation pane like that. Keeping it all in VBA makes it clearer that the intention is to execute those statements together.
7. Why Not Do it all in a Single SQL Statement? I Saw a Method that Does This…
Yes, it is certainly possible to do it without VBA at all. One possible method is to write a subquery that counts all rows up to this row. This also assumes there is a primary key that you can use and you can define a sort order. Here’s a sample SQL: SELECT ( SELECT COUNT(*) FROM SomeTable AS x WHERE x.PrimaryKey <= t.PrimaryKey ORDER BY x.SomeColumn ) AS RowNumber FROM SomeTable AS t ORDER BY t.SomeColumn; This will work OK for a small set of data, but the more data there is, the more intensive the subquery becomes, and it must be called repeatedly for each row. This is one of those rare cases where doing it in VBA is faster than doing it in SQL — after all, row numbering is fundamentally iterative, not set-based which just goes against the grain of what SQL was designed to do – solve a problem in a set-based fashion, non-iterative fashion.
There are also other problems – for one thing, it’s not a very generic solution. You must customize several parts to work for different queries. If the set of tables used in the outermost FROM clause are different, to be the innermost FROM clause, you have to adjust the WHERE clause to reference the correct primary key (or some combination thereof). Then you have to also keep the ORDER BY in sync. That’s a lot of tweaks if you want to use it for different queries. That is why this is the last method listed here but it is one possible solution.
So Many Ways to Number a Cat
As you can see, an idea of sequential numbering can be solved many different ways using different domains. This is typical in this field of work and precisely why it pays to not just consider how you can do it in one domain but also whether the domain is the right domain. We saw how we can easily leverage built-in features such as Transact-SQL’s ROW_NUMBER() or Access report’s Running Sum property. We also not only saw how we can craft our custom solutions in either VBA or SQL but we also see how we can use both together to create a solution that is better.
So in the end, it is good to look at the final goal first rather than the intermediate steps and think about the domains that might be better suited to get you there.
This tutorial focuses on various ways to calculate times in Excel. You will find a few useful formulas to add and subtract times, calculate time difference, or elapsed time, and more.
In the last week’s article, we had a close look at the specificities of Excel time format and capabilities of basic time functions. Today, we are going to dive deeper into Excel time calculations and you will learn a few more formulas to efficiently manipulate times in your worksheets.
How to calculate time difference in Excel (elapsed time)
To begin with, let’s see how you can quickly calculate elapsed time in Excel, i.e. find the difference between a beginning time and an ending time. And as is often the case, there is more than one formula to perform time calculations. Which one to choose depends on your dataset and exactly what result you are trying to achieve. So, let’s run through all methods, one at a time.
As you probably know, times in Excel are usual decimal numbers formatted to look like times. And because they are numbers, you can add and subtract times just as any other numerical values.
The simplest and most obvious Excel formula to calculate time difference is this:
=End time – Start time
Depending on you data structure, the actual time difference formula may take various shapes, for example:
Formula
Explanation
=A2-B2
Calculates the difference between the time values in cells A2 and B2.
=TIMEVALUE("8:30 PM") - TIMEVALUE("6:40 AM")
Calculates the difference between the specified times.
Calculates the time difference between values in cells A2 and B2 ignoring the date difference, when the cells contain both the date and time values.
Remembering that in the internal Excel system, times are represented by fractional parts of decimal numbers, you are likely to get the results similar to this:
The decimals in column D are perfectly true but not very meaningful. To make them more informative, you can apply custom time formatting with one of the following codes:
Time code
Explanation
h
Elapsed hours, display as 4.
h:mm
Elapsed hours and minutes, display as 4:10.
h:mm:ss
Elapsed hours, minutes and seconds, display as 4:10:20.
To apply the custom time format, click Ctrl + 1 to open the Format Cells dialog, select Custom from the Category list and type the time codes in the Type box. Please see Creating a custom time format in Excel for the detailed steps.
And now, let’s see how our time difference formula and time codes work in real worksheets. With Start times residing in column A and End times in column B, you can copy the following formula in columns C though E:
=$B2-$A2
The elapsed time is displayed differently depending on the time format applied to the column:
Note. If the elapsed time is displayed as hash marks (#####), then either a cell with the formula is not wide enough to fit the time or the result of your time calculations is a negative value.
Formula 2. Calculating time difference with the TEXT function
Another simple technique to calculate the duration between two times in Excel is using the TEXT function:
Calculate hours between two times: =TEXT(B2-A2, "h")
Return hours and minutes between 2 times: =TEXT(B2-A2, "h:mm")
Return hours, minutes and seconds between 2 times: =TEXT(B2-A2, "h:mm:ss")
Notes:
The value returned by the TEXT function is always text. Please notice the left alignment of text values in columns C:E in the screenshot above. In certain scenarios, this might be a significant limitation because you won’t be able to use the returned “text times” in other calculations.
If the result is a negative number, the TEXT formula returns the #VALUE! error.
Formula 3. Count hours, minutes or seconds between two times
To get the time difference in a single time unit (hours ,minutes or seconds), you can perform the following calculations.
Calculate hours between two times:
To present the difference between two times as a decimal number, use this formula:
=(End time – Start time) * 24
Supposing that your start time is in A2 and end time in B2, you can use a simple equation B2-A2 to calculate the difference between two times, and then multiply it by 24, which is the number of hours in one day:
=(B2-A2) * 24
To get the number of complete hours, use the INT function to round the result down to the nearest integer:
=INT((B2-A2) * 24)
Total minutes between two times:
To calculate the minutes between two times, multiply the time difference by 1440, which is the number of minutes in one day (24 hours * 60 minutes = 1440).
=(End time – Start time) * 1440
As demonstrated in the following screenshot, the formula can return both positive and negative values, the latter occur when the end time is less than the start time, like in row 5:
=(B2-A2)*1440
Total seconds between times:
To get the total seconds between two times, you multiply the time difference by 86400, which is the number of seconds in one day (24 hours * 60 minutes * 60 seconds = 86400).
=(End time – Start time) * 86400
In our example, the formula is as follows:
=(B2-A2)* 86400
Note. For the results to display correctly, the General format should be applied to the cells with your time difference formula.
Formula 4. Calculate difference in one time unit ignoring others
To find the difference between 2 times in a certain time unit, ignoring the others, use one of the following functions.
Difference in hours, ignoring minutes and seconds:=HOUR(B2-A2)
Difference in minutes, ignoring hours and seconds:=MINUTE(B2-A2)
Difference in seconds, ignoring hours and minutes:=SECOND(B2-A2)
When using Excel’s HOUR, MINUTE and SECOND functions, please remember that the result cannot exceed 24 for hours and 60 for minutes and seconds.
Note. If the end time is less than the start time (i.e. the result of the formula is a negative number), the #NUM! error is returned.
Formula 5. Calculate elapsed time from a start time to now
In order to calculate how much time has elapsed since the start time to now, you simply use the NOW function to return today’s date and the current time, and then subtract the start date and time from it.
Supposing that the beginning date and time is in call A2, the formula =NOW()-A2 returns the following results, provided you’ve applied an appropriate time format to column B, h:mm in this example:
In case the elapsed timeexceeds 24 hours, use one of these time formats, for example d “days” h:mm:ss like in the following screenshot:
If your starting points contain only time values without dates, you need to use the TIME function to calculate the elapsed time correctly. For example, the following formula returns the time elapsed since the time value in cell A2 up to now:
Note. The elapsed time is not updated in real-time, it refreshes only when the workbook is reopened or recalculated. To force the formula to update, press either Shift + F9 to recalculate the active spreadsheet or hit F9 to recalculate all open workbooks.
Formula 5. Display time difference as “XX days, XX hours, XX minutes and XX seconds”
This is probably the most user-friendly formula to calculate time difference in Excel. You use the HOUR, MINUTE and SECOND functions to return corresponding time units and the INT function to compute the difference in days. And then, you concatenate all these functions in a single formula along with the text labels:
The syntax may seem excessively complicated, but it works 🙂
Alternatively, you can calculate time difference by simply subtracting the start time from the end time (e.g. =B2-A2), and then apply the following time format to the cell:
d “days,” h “hours,” m “minutes and” s “seconds”
An advantage of this approach is that your result would be a normal time value that you could use in other time calculations, while the result of the complex formula discussed above is a text value. A drawback is that the custom time format cannot distinguish between zero and non-zero values and ignore the latter. To display the result in other formats, please see Custom formats for time intervals over 24 hours.
How to calculate and display negative times in Excel
When calculating the time difference in Excel, you may sometimes get the result as ###### error because the difference is a negative time. But is there a way to show negative times properly in Excel? Of course, there is a way, and even more than one 🙂
Method 1. Change Excel Date System to 1904 date system
The fastest and easiest way to display negative time normally (with a minus sign) is switching to the 1904 date system. To do this, click File > Options > Advanced, scroll down to the When calculating this workbook section and put a tick in the Use 1904 date system box.
Click OK to save the new settings, and from now on negative times will be displayed correctly, like negative numbers:
Method 2. Calculate negative time in Excel with formulas
Is changing Excel’s default Date System is not an option, then you can force negative times to display properly using one of the following formulas:
Both formulas check if the time difference (A2-B2) is greater than 0, and if it is they return that difference. If the time difference is less than zero, the first formula calculates the absolute difference and concatenates the minus sign. The second formula yields exactly the same result by using a negative time format “-h::mm“.
Note. Please keep in mind that unlike the first method that treats negative times as negative numeric values, the result of the TEXT function is always a text string that cannot be used in calculations or other formulas.
Adding and subtracting time in Excel
Basically, there are 2 ways to add and subtract time in Excel:
Using the TIME function
Using arithmetic calculations based on the number of hours (24), minutes (1440) and seconds (86400) in one day
The TIME(hour, minute, second) function makes Excel time calculations really easy, however it does not allow adding or subtracting more than 23 hours, or 59 minutes, or 59 seconds. If you are working with bigger time intervals, then use one of the arithmetic calculations demonstrated below.
How to add or subtract hours to time in Excel
To add hours to a given time in Excel, you can use one the following formulas.
TIME function to add under 24 hours
=Start time + TIME(N hours, 0, 0)
For example, if your start time is in cell A2, and you want to add 2 hours to it, the formula is as follows:
=A2 + TIME(2, 0, 0)
Note. If you try adding more than 23 hours with the TIME function, the specified hours will be divided by 24 and the remainder will be added to the start time value. For example, if you try to add 25 hours to “6/2/2015 10:00 AM” (cell A4) using the formula =A4 + TIME(25, 0, 0), the result will be “06/02/2015 11:00”, i.e. A4 + 1 hour.
Formula to add any number of hours (under or over 24 hours)
The following formula has no limitations to the number of hours you want to add:
= Start time + (N hours / 24)
For example, to add 28 hours to the start time in cell A2, enter the following formula:
=A2 + (28/24)
To subtract hours from a given time, you use analogous formulas, and just replace “+” with the minus sign:
For example, to subtract 3 hours from the time in cell A2, either of the following formulas will do:
=A2-(3/24)
=A2-TIME(3,0,0)
To subtract more than 23 hours, use the first one.
How to add / subtract minutes to time in Excel
To add minutes to a given time, employ the same techniques that we’ve just used for adding hours.
To add or subtract under 60 minutes
Use the TIME function and supply the minutes you want to add or subtract in the second argument:
=Start time + TIME(0, N minutes, 0)
And here are a couple of real-life formulas to calculate minutes in Excel:
To add 20 minutes to the time in A2: =A2 + TIME(0,20,0)
To subtract 30 minutes from the time in A2: =A2 – TIME(0,30,0)
To add or subtract over 60 minutes
In your calculation, divide the number of minutes by 1440, which is the number of minutes in a day, and add the quotient to the start time:
=Start time + (N minutes / 1440)
To subtract minutes from time, simply replace plus with the minus sign. For example:
To add 200 minutes: =A2 + (200/1440)
To subtract 300 minutes: =A2 -(300/1440)
How to add / subtract seconds to a given time
Second calculations in Excel are done in a similar fashion.
To add under 60 seconds to a given time, you can use the TIME function:
=Start time + TIME(0, 0, N seconds)
To add more than 59 seconds, use the following formula:
=Start time + (N seconds / 86400)
To subtract seconds, utilize the same formulas with the minus sign (-) instead of plus (+).
In your Excel worksheets, the formulas may look similar to these:
To add 30 seconds to A2: =A2 + TIME(0,0,31)
To add 1200 seconds to A2: =A2 + (1200/86400)
To subtract 40 seconds from A2: =A2 - TIME(0,0,40)
To subtract 900 seconds from A2: =A2 - (900/86400)
How to sum time in Excel
The Excel sum time formula is the usual SUM function, and applying the proper time format to the result is what does the trick.
Supposing you have a few project times in column B and you want to add them up. You write a simple SUM formula like =SUM(B2:B4) and get the result in the default format, like hh:mm:ss.
In some cases the default time format works just fine, but sometimes you may want more, for example to display the total time as minutes and seconds, or seconds only. The good news is that no other calculations are required, all you have to do is apply custom time format to the cell with the SUM formula.
Right click the cell and select Format Cells in the context menu, or press Ctrl + 1 to open the Format Cells dialog box. Select Custom from the Category list and type one of the following time formats in the Type box:
To display total time as minutes and seconds: [m]:ss
To display total time as seconds: [ss]
The result will look as follows:
How to sum over 24 hours in Excel
In order to add up more than 24 hours, you use the same SUM formula as discussed above, and apply one of the following time formats to the cell:
Format
Displays as
Explanation
[h]:mm
30:10
Hours and minutes
[h]:mm:ss
30:10:20
Hours, minutes and seconds
[h] “hours”, mm “minutes”, ss “seconds”
30 hours, 10 minutes, 20 seconds
d h:mm:ss
1 06:10:20
Days, hours, minutes and seconds
d “day” h:mm:ss
1 day 06:10:20
d “day,” h “hours,” m “minutes and” s “seconds”
1 day, 6 hours, 10 minutes and 20 seconds
To see how these custom time formats may look like in your Excel worksheet, please have a look at the screenshot below, where the same SUM formula is entered in cells A9 to A13:
=SUM($B$2:$B$4)
Note. The above custom time formats work for positive values only. If the result of your time calculations is a negative number, e.g. when you are subtracting a bigger time from a smaller time, the result will be displayed as #####. To display negative times differently, please see custom format for negative time values.
Also, please keep in mind that the time format applied to a cell changes only the display presentation without changing the cell’s value. For example, in the screenshot above, cell A13 looks like text, but in fact it’s a usual time value, which is stored as a decimal in the internal Excel system. Meaning, you are free to refer to that cell in other formulas and calculations.
Date & Time Formula Wizard – quick way to calculate times in Excel
Now that you know a bunch of different formulas to add and subtract times in Excel, let me show you the tool that can do it all. Okay, almost all 🙂
Here comes Ablebit’s Date & Time Formula Wizard for Excel:
In the Date & Time Wizard dialog window, you switch to the Add or Subtract tab, depending on which operation you want to perform, and do the following:
Click the Show time fields link in the left part of the window.
Supply values or cell references for the formula arguments. As you fill in the argument boxes, the wizard builds the formula in the selected cell.
When finished, click the Insert Formula
That’s it! For example, this is how you can add the specified number of hours, minutes and seconds to the time in A4:
If you need to copy the formula to other cells, fix all references except the cell containing the original time (A4) with the $ sign like shown in the screenshot below (by default, the wizard always uses relative references). Then double-click the fill handle to copy the formula down the column and you are good to go!
If you are curious to try this tool in your own worksheets, you are welcome to download the 14-day trial version of our Ultimate Suite which includes this as well as 60 more time saving add-ins for Excel.
This is how you calculate time in Excel worksheets. To learn other ways to manipulate dates and times in Excel, I encourage you to check out the resources at the end of this article. I thank you for reading and hope to see you on our blog next week!
