Posted by Sean MacKenzie on Thu, Jul 22, 2010
Small business owners often wonder why they should be using an online hours collection system like the one we have here at www.billzone.com. In fact, most owners
know they need a system like this but only some take the step.
Opportunity Cost The biggest reason is simple. If you are spending valuable time collecting hours from contractors and/or employees for billing to your own clients, then you are
not spending time either doing billable work for your clients, managing, or selling your product.
Usually the monthly cost of using BillZone to automate a 10 person team is less than a few hours of billing for one of the team's members, compared to one or two days worth of work for that member to do the task manually. For large enterprises, the benefits are even greater.
Accuracy If you are consolidating a whole bunch of time entries from a variety of sources like paper timesheets or Excel workbooks, then your chances of creating an error during this double-entry process go up. When collection is centralized, data is only entered one time, thus reducing errors.
Timeliness Speed up payment on your accounts receivable. Want your bills to go out on the first of the month instead of the 10th? Automate your process.
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 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, 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
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.
Posted by Sean MacKenzie on Thu, Oct 29, 2009
Changing over to a new tax scheme can be daunting for small to medium sized business
owners, but there are some things you can do in advance that can help.
Update Your Accounting System
Ensure your accounting system can handle the change. If it can't, make sure to plan a
changeover to a new system that can, or design a workaround so you can accommodate the
new tax.
Double-Check Invoicing
Check over your invoicing thoroughly at the time of changeover to make sure that you
have applied the tax correctly for each product or service.
Get the Team Involved
If you have a larger business, make sure to involve all the different divisions in a
discussion about the change. There will often be some expertise unique to different
departments that can help your transition.
Prepare Your Suppliers
Prepare your suppliers to expect the change. For example, BillZone allows your
subcontractors to generate their own invoice to you, which speeds up Accounts Payable
(they get their bills in on time) and Accounts Receivable (you immediately generate
invoices immediately from the same data). They might be surprised if the tax rate
changed on their invoice and they were not expecting it, or forgot to implement it
themselves.
Confirm the Date, and Find Out When You're Affected
Be sure of the changeover date, and check to see if you will be affected in advance.
For example, you might be required to self-assess for goods or services sold in advance
of the changeover date but delivered after. Also, this could affect the timing of invoicing for certain aspects of your business.
Posted by Sean MacKenzie on Wed, Oct 07, 2009
Ok, you own a small business with several projects on the go at any
one time. Why bother with keeping project records? I mean, you've got it all
organized in your head, right? Besides, you looked at project management programs and
found that they were overkill and wasted a huge amount of your time. Well, there are
some very good reasons to use at least some basic project management tools in your
business.
Try answering these questions:
- If your customer calls you and asks how much time you've burned to date on her
project items, can you tell her right then and there? Or do you have to compile a
bunch of information (and phone people) before you can tell her?
- Is your business relatively free from costly bookkeeping mistakes?
- Do all of your bills go out on time, all the time?
- Can you measure your success over the past few years? Can you see how often you've
been on time for delivery of your projects?
If you answered no to any of these, you need some basic project management tools.
Forget complex project plans, you just need to set up your project with a good list of
deliverables on it. Make sure your people can access it and update it in real time, so
that you or your bookkeeper don't have to spend as much time on it. You'll know how much you've burned and what the item completion status is at any one
time, for all your projects, across your whole business.
Posted by Sean MacKenzie on Mon, Sep 21, 2009
These days everything is mobile, and mobile device technologies are
changing small businesses because it enables them to extend their data
capture into the field like never before.
Forget about just sending emails or making calls while in the field.
Whether it is consultants, home care workers, electricians, or architects,
they are entering time, expenses, distance to the job, and product sales
on their mobile devices while at the site instead of waiting to use a
computer.
Most people have a mobile device, and in many cases they use a mobile
device more than they use a computer because they are always in the
field. Contractors and installers are receiving workorders and
instructions on their mobile devices while sitting in their truck, and
no longer have to wait for phone authorizations, printed instructions,
or other things that waste time. Technology project managers are
running quick reports and receiving automatic notifications while at the
airport.
On the other side of things, small business owners or project team leaders are
getting the benefit of real-time oversight of their billings and
resource usage, when they need it.