Posted by Sean MacKenzie on Tue, Jun 08, 2010
After having a great response to my LinkedIn question regarding "Why is my Team's Microsoft Access Database so Slow?", I thought it would be good to post some other suggested solutions to help performance.
Just over half of the respondents recommended to simply scale to SQL Server while retaining the mdb file as the front end as described in my earlier article. However, there were some interesting alternatives proposed that could offer some performance enhancements without migrating the backend to SQL Server.
Recommended alternatives included doing some tuning on the database, adding or modifying the indexing strategies, performing some maintenance (like compacting the mdb), and doing network or hardware analysis to find out if it is the source of trouble. Some responses advocated rewriting the application on an entirely different platform, such as .Net or PHP. This could be costly and may not be appetizing if your application already suits your needs but just needs a performance boost.
An interesting suggested alternative was to move the application onto Terminal Server and optimize its performance in that environment. Moving to a thin-client environment centralizes processing activities and resource usage, particularly in areas like network utilization where MS Access/Jet is very inefficient. So, it is possible to improve the application's performance and number of users by moving to this configuration.
Moving to Terminal Services (or Citrix) is a good intermediate step that can improve performance until your application goes beyond the number of concurrent users that an MS Access database can handle. In this environment, users would connect to and use the database using Remote Desktop Connection, which is a program found on almost all versions of Windows.
One upside is that you get centralized administration, allowing you to do one change for all users when program changes are needed (instead of touching many computers), and enabling you to do things like kicking off idle sessions by logging users out instead of just breaking open connections to get control of the file, which can corrupt mdb files.
Also, this configuration will allow you to use your database in ways you weren't able to before, such as working remotely from home, airport, client site, or anywhere your laptop can get a connection. Some companies move to the Terminal Services configuration for that reason alone, regardless of whether they need a performance increase. You may need a VPN, Citrix or other technology to enhance your security if you plan to use Remote Desktop Connection to connect to Terminal Services from outside of your LAN.
A couple of downsides include giving users a 256 colour environment with no sound (in most cases), and having to purchase, license, and administer a new server.
Thanks to everyone who responded to my question on LinkedIn - http://www.linkedin.com/answers/technology/software-development/TCH_SFT/673347-2600475?browseIdx=0&sik=1276022297991
Posted by Sean MacKenzie on Fri, Apr 16, 2010
While on the subject of dates, it would be good to alert people to a common problem that happens when creating databases with reports in Microsoft Access & Excel.
Here is the problem: You get your nice date-range driven report done, which filters a particular range, but then discover a problem. Even though your report says it is filtering by a date range, it is actually missing some records that should be in there and including some that should not be there! You even went back to your table and looked at missing records and they showed dates that should be in your query or report. What's happening?
It turns out that this happens more frequently on computers outside the U.S., because it has to do with your Windows Regional Settings, and more specifically, your Short Date format on your computer.
For example, in Canada, your computer would be set up with a Short Date setting of dd/mm/yyyy in its regional settings. Whenever you type a date into a spreadsheet cell, Word document, or database table field, it is displayed in that format. If you type March 2 into the field, it will auto-format into 02/03/2010 if that field or cell is formatted to Short Date. So, when you create an Access query for March 1st to 31st on your table, the record should be included, right?
Well, the answer is possibly no. The record in the above example might be excluded from your query. Further, if you created the same query for February 1st to 28th, your record would show in that month instead! This is because Office uses your computer's selected Short Format for display while you're working on the data, but always uses the U.S. format for things like queries. If you are responsible for creating queries and reports on your organization's data, you could run into real trouble, because the query might read your date as February 3rd (2/3/2010) instead of March 2nd!
The solution is to make sure your computer's Short Date setting is set to U.S. format (m/d/yyyy) in the Regional Settings. Once you make the switch, your report will filter your data correctly. You also would need to change this setting for all other people using your queries or reports on their computer.
For Windows XP, the setting can usually be found at Start > Control Panel > Date, Language, and Regional Options > Regional and Language Options > Customize > Date Tab > Short Date Format. Change the selection to M/dd/yy and click OK. Finding Regional Settings in Vista and Windows 7 is similar. All your other selections (like long date formats) can stay the same, but this one needs to be changed for your reports to work correctly.
Posted by Sean MacKenzie on Tue, Mar 30, 2010
In a world of increasing connectivity it makes no sense for any entity to use or enforce a date display that could be misinterpreted by others.
Ambiguous Dates
An ambiguous date format is one which requires a reader to reference some external source, such as a policy or standard in order to understand what real date the display is actually referring to. For example, if you looked at a report with the date
2/3/08 on it, you would need to find out which country it was written in, which standard was used for dates in the report, or even worse, guess what the author meant when using this format.
Examples
Food and drink producers in many countries are guilty of this practice. Consumers are often confounded by dates on products in formats they cannot decipher. For example, depending on what country you're in or which product you're about to eat, a date of
11 MA 10 on a canned good could mean
March 11th, 2010,
March 10th, 2011,
May 11th, 2010, or
May 10th, 2011! Make the wrong choice and you could be in trouble.
Businesses run into problems when dates are ambiguous, as they create scheduling mixups, customer order mixups, and more. The inefficiency caused by this issue can be significant.
Many governments and international bodies enforce their own versions of ambiguous date formats, which causes confusion. Even some international standards promote ambiguous dates such as the ISO 8601 dotted standard that would show February 3rd, 2008 as
03.02.2008 (this could be interpreted as March 2nd, 2008 in areas of North America using m/d/yy). ISO 8601 also uses a more logical format that shows the same date as
2008-02-03, which has less of a chance of misinterpretation since it appears to be in descending orders of magnitude (yyyy-mm-dd).
Use Unambiguous Dates!
What can you do? Well, you can help by changing peoples' attitudes in the workplace by discouraging the use of ambiguous date formats, and by challenging any standards or policies that enforce the use of ambiguous formats, since they are obsolete in a connected world and create organizational inefficiency.
It doesn't mean that everyone has to always use a long format (ex. February 9th, 2011), as there are popular short date formats like
dd-mmm-yyyy. This would be my first choice and would show the date as
09-Feb-2011. The day and month elements are known on first glance, and cannot be misinterpreted. My second choice would be for people to use the ISO 8601 standard
2011-02-09, which is logical and has month and day elements that would be guessed correctly by most people.
Posted by Sean MacKenzie on Mon, Feb 15, 2010
Spreadsheets are one of the most useful tools that businesses have today. Some people have become so proficient at using them that, as a result they use them all the time, for all kinds of things. This often leads into people using spreadsheets for purposes they weren't designed for. You would not use a spreadsheet when:
It needs to be shared concurrently.
When many others need to have access to view and update it, you need another tool. Excel allows one user at a time. This often leads to many people taking a copy of their own, which leads to a real mess when it all has to come back together.
You are continuously collecting data.
An engineer who has 50,000 rows and growing in an Excel sheet will soon hit the worksheet row limit of 65,536 rows. At that point she would have to figure out a way to continue.
The data needs to be secure.
Excel does not have data security in the same way an enterprise application does, and so can only protect data using some simple mechanisms.
The data needs to validated on entry.
Though it is possible to set up some data entry validation in excel, in practice, most people just pull up a sheet and start punching data into it. This can lead to errors in data-entry, making the data less accurate, and harder to run statistics on.
There is obviously another tool that would do it better.
There are examples of users creating letter templates, trying to maintain multi-user mission critical manufacturing schedules, trying to create graphics files with it, and more.
You're building a "database".
Many IT support staff find this to be one of the biggest misuses of Excel. Usually users spend a lot of time designing and entering data before they find their solution does not work. At this point the sheet is mission critical, so they ask their IT support staff to help them. It takes more time to analyze it, so time is wasted on both sides.
Ask for Help
If you find yourself in a position where you're considering using Excel for a project, consider some of the points above. If you fall into any of these categories, you may want to have a chat with your IT support staff or an IT specialist before continuing. You might find that a small amount of time invested with them up front will save you a large amount of grievance in the long run.
For example, they might be able to set up a simple SQL Server/MS Access database that everyone can use, has unlimited data entry, is more secure, and validates data on entry. As an added bonus, an MS Access front-end could spit out reports in Excel for analysis, a purpose Excel is more suited for.
Many thanks to people who contributed to a discussion on this topic at http://www.linkedin.com/answers/technology/information-technology/computers-software/TCH_ITS_CMP/625070-2600475