Comparision of Microsoft Access Databases to Other Database Software
Many people ask us our opinions about which database should be used for their particular situation. This article is designed to give our general opinions about some of the database software that is available in the market today. In general we feel their about five main database software systems used today (Microsoft Access, Filemaker, My SQL, SQL Server and Oracle). While there are many more database systems available these are five of the most popular. We have worked with 4 of the 5 extensively. We do not have much experience with My SQL. We see My SQL being used a good deal with web sites built with PHP and have heard good things but for this article we will not discuss My SQL.
Desktop Database Systems
Microsoft Access and Filemaker are the two main desktop database systems in use today. In general they are both good and fairly similar in ease of use in building a database system. Table design and screen design concepts are similar. The two databases vary when it comes to the sql used vs the relationship graph and how code is written. In our opinion Microsoft Access databases generally use more code and give you a little higher level of control. Also SQL is a main feature of Microsoft Access and is starting to gain momentum in the Filemaker world. Filemaker does have a huge advantage if you need to make your application work with Macintosh computers because Filemaker is cross platform. Our general opinion is to use Microsoft Access over Filemaker unless you need the application to be used by Windows and Mac computers.
Server Database Systems
Microsoft SQL Server and Oracle are two of the leading enterprise database systems. In most cases the software you are using drives you to use one or both of these products. Lots of companies run both of these databases. Example: Your ERP, Financial, CRM, HR or other major software system usually works with one or both of these databases. Once you have one of these databases up and running you have made the investment and then can use it for some custom applications as needed. We personally prefer SQL Server becuase of it’s ease of use. However Oracle has some great tools and SQL features.
Application Development Tools
There are lots of development tools on the market. When building desktop database systems you usually use Microsoft Access forms, reports, queries, VBA code or Filemaker forms, reports, scripts or something like .NET code (Visual Basic or C#). Using the built-in tools in Microsoft Access or Filemaker in our opinion leads to quicker development than using .net code. However .net gives you the most flexibility and control. Here at RPT Software we like to push the limits of Microsoft Access and are able to achieve our goals, however there have been times where we wished for a few of the nice features of .net. Client server and web-based development is usually done in Microsoft tools (such as asp.net or vb/C#), or Oracle tools, or PHP with My SQL.
Desktop, Client-Server or Web based Application
One of the toughest decisions is usually “Should my application be web-based”. Desktop databases have concurrency limits and in general work best when on a local area network. However you can get around some limitations by using remote control technologies such as Citrix/terminal services. Many factors must be considered when deciding on “web-based, Y/N?”, how many users?, geographical locations of users, budget, complexity of application/data in application etc…. This article will not cover everything you need to know to make this choice, however in general in our opinion if you go web-based you should use SQL Server, My SQL or Oracle as your database. If you decide to stay with a desktop system Microsoft Access is our top choice. Client server applications are not as common today since most software is moving to be web-based. Keep in mind that building software from scratch is much much easier and quicker using a tool like Microsoft Access or Filemaker (in our opinion).
Why Choose Microsoft Access?
- Microsoft Access is the most widely used desktop database system in the world.
- Microsoft Access is part of Microsoft Office so there is a common interface and lots of developers and support options available.
- You can use the free Microsoft Access 2007 runtime.
- Microsoft Access is significantly less expensive than either SQL Server or Oracle.
- Typical development times for custom databases using a qualified consultant (such as RPT Software) are days. Doing similar development using other tools such as .net or building a web-based system would typical be multiples of the time required.
- Third party support for Microsoft Access databases is very common. Example: interfaces to external databases etc…
- If you need a database that serves your needs and Microsoft Access is a viable option, it is probably the fastest and least expensive way to get your goals accomplished.
Why NOT to Choose Microsoft Access?
- You have determined that you need a web-based system.
- You have too many concurrent users. Technical limit is 255 concurrent users, but real world limit is 10 to 80 (depending on type of application).
- Your users are geographically spread too far and you do not want to use remote control solutions to get around this limitation.
- You have users on Macintosh computers (consider Filemaker).
- Your database will be too big to work with Microsoft Access. Usually if you know that something will be over a million records, you might be getting into this situation.