Posted by Sean MacKenzie on Wed, May 12, 2010
So, you've got a Microsoft Access database that you've been using for years. Over the past while, you and your team have noticed that it is slowing you all down. What can you do?
Most people experience this situations when their business grows and more people need to use it. Also, after years of data builds up in the database, performance slows down when using MS Access.
In many cases, the provider of I.T. services wasn't the original programmer, who may recommend re-writing the system into a "newer" technology. The estimate for this kind of rewrite can be very costly.
What many people don't know is that the majority of Access databases can scale up easily, with very few adjustments to the original code. So, if you have an office with 10 people using an Access/VBA system which seems near capacity, it easily scale to handle dozens more.
What you need to do is use a provider who knows how to move the "back-end" database onto a true database server, and then have them reconfigure your application to use it. When the process is done, you won't see any difference when using your application, except that it is much faster and can handle more people. This is good news for your team!
Well, there is more. It can be done without purchasing expensive server software! This is because Microsoft Office ships with a free version of the server which is usually more than adequate to get you started. This server is called MSDE (since Access 2000) or SQL Express (newer versions).
In our experience, performing a conversion like this for a customer resulted in faster performance, fewer errors reported by users, less administration required, and better data security.
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, Feb 02, 2010
Microsoft discontinued support and enhancement of its Visual Basic 6 platform years ago, instead moving to its new .Net platform for enterprise applications. However, what many people don't know is that a flavour of VB called Visual Basic for Applications, or VBA as it is known, continued on in the Microsoft Office suite as the primary language for things like Excel and Word macros, and automation of Access database applications. It appears that, in spite of the relentless move toward .Net at Microsoft for many years, this technology will continue on and will be supported in Office 2010 (Office 14), and for SME business owners this is a very good thing.
1. Most businesses over 20 employees have invested in VBA-automated things for people "in the trenches", from simple mail merge applications in Word, to data extractions for analysis in Excel, to multi-user MS Access databases. Continuing support for these means that these companies will be able to extend the life of their investment.
2. Support for VBA also means that related technologies like the Jet database engine and Data Access Objects, or DAO can be supported by providers for years to come, on newer, more advanced platforms like Office 2010. For the large number of businesses who wrote entire systems around the MS Access/DAO/ODBC/SQL Server platform, this means they can stay current and be sure to be supported for years to come.
3. VBA with MS Access is regarded by many as the quickest way to prototype a working application for SME businesses due to a data access technology (DAO) tightly knit with the VBA language, a powerful design environment, and the best integration with other technologies in the office environment, like Word and Excel.
Ongoing VBA support means SME businesses can still get the best bang for their buck for years to come.