Comparison of MS Access, Filemaker and distributed ideas like MS SQL Server/ReportServices/ASPX

Posted on by

Here I am writing to a client explaining which technology is the best. The end use is a one table, muliti-report application. Perhaps a few tables with selection lists, but the main data is in one table. The 2nd phase adds 2 more tables. This company is not a Windows shop, but has no Macs or iOS devices per-se. All the desktops are for Windows.

An executive summary is that if you want to do a corporate application that is the most flexible and might have complicated interrelationships then the non-all-in-one approach is the best solution using one tool for the data storage (the database or dB), another to make the input screens (ASPX, .net, php, html) and another for reporting like reporting services or even MS Access – it is good for that but only that. This requires having the resources and time to get this completed. A hybrid approach is always an option as the tools discussed in this article do interact with each other.

If you want to get going, make large even mid-complex databases and get your work done fast and easy and have many reports that are very quick to develop by even the most basic database designers or business people – Filemaker wins hands down.

Again, if you feel you need to grow, a hybrid system would work best. There are not lots of us who know so many technologies though, and it limits things to more advanced or experienced people – hence the cost might increase.

Rarely should one use MS Access even if it is already installed and tempting you to click on it on your start bar.

2 Classes of database core solutions

First is worth noting that there are 2 classes of applications – all-in-one and separate components working together as listed above. Another class of database solutions are like an all-in-one idea like Filemaker and MS Access which are both rapid development tools. They integrate the storage part (database or dB) the interface or GUI and the glue or scripting that makes it all work. Solutions where you glue your own parts together would be like Microsoft SQL (dB) w. your browser as the GUI tool and also a reporting tool using ASPX (or Microsoft.net) to feed this browser’s content and also glue things together. Other components for a user interface/glue is lets say another programming language (C++) and/or a custom application that runs on Windows.

Accessing your application:

I prefer the web as a user interface, because EVERYONE has a web browser and it is free and easy for people to use – no matter what platform (Mac, PC, Android, iOS) . I have programmed many applications on devices that are specific. These include Windows apps, Mac desktop apps and iOS apps. Apps that are more ubiquitous are better for the end user. Examples are Filemaker (Macs, Windows, iOS and the web – almost all at the same time), Excel tools (Mac and Windows – almost all at the same time) and web applications.

Filemaker for viewing reports can easily be done via the web. The latest version is OK for features but they are limited for forms and data input via the web. Be normal version only allows 5 people concurrently on the web view, which statistically is most likely fine for a few users. Upgrading to the  Filemaker Server version allows more concurrent users (like 200) concurrently but has a price tag which limits the adoption by small businesses. Filemaker Go allows most desktop features to be available on the ipad, iPhone and iPod. What you see on the desktop is what you get on the iPad. Custom layouts can even make the iOS devices even more productive, clear and simple interfaces.

Winner? If you build on the web you satisfy the customer. The downfalls is that designing them is more difficult than the slick custom apps that have swipes, drag and drop. These features are coming along in life, but slowly. To get them as easy to use as a custom application with features like Excel has or Word has, there is a lot more programming to do or one could buy a suite of tools that help all this slickness be part of the web which can also get costly. As  result, web programming and more clicks/user interaction are important. Backwards thinking must be employed on the users part and not-on-the-fly addition of data. It can be done, but a lot of web-apps make the user set things up first, then they can be used. Traditional desktop applications so a lot more as the user goes. Why? Because web pages are not constantly connected to the engine whereas on a desktop app, the input and engines know intimately about eachother. The web is getting there, but it is very slow. Additions like adding an item to a list on the fly is not always easy to program in on the web like with a custom application or an all-in-one program. The user might need to add items to a pick list, then go and use these new tools.

Reporting interface – I have never seen a more complete and flexible system as Microsoft reporting services. All users use the web browser to look at the data. It exports quite well (a few quirks) to Excel, Word, PDF’s. From these external tools, more processing can be done.

Another winner? Filemaker – the standardized interfaces that are constantly evolving by this subsiduary-of-Apple company (explains its ease of use) has lots of slick features that are very easy to enable and glue together. The only tools that are not easy to use are printing flexibility in Windows and interactivity with other databases which, with a little fiddling come together. Windows, Mac and iOS are what you see is what you get between platforms. The ease of use is brought to Windows for all to use. One thing (which is selectable) that drives users crazy is that there is no save button. Microsoft was pre-programmed the need to have a Save button. It makes us feel better to be in control that we saved. In Filemaker, like a Mac, there is no save button – just a revert button. When you leave a control, a screen etc. the info syncs into the databases. Some of my clients claim that sometimes it does not save, but most of the time it is human error. Adding data on the fly to pick-lists etc. is a snap.

Filemaker Go is also a FREE tool that can be used on any iOS (iPad, iPhone, iPod). One app I developed on the desktop and it is therefore ready to be used on the iPad and iPhone was to connect a scanner via Bluetooth to the iPad and zap zap zap bar codes into the database and then sync it with an online ASPX web app that I also developed. All this iOS was done in hours – not days using Filemaker.

Multiusers

MS Access can be shared amongst users. It is a lot more robust if the database part is on a proper MS SQL server so that the MS Access application can be owned or stored on each persons machine to access the data. Keeping users up to date with the latest version sometimes can be a challenge.

Filemaker is inherently intended for multi-user access. If you are more frugal, you have one computer open the file and everyone else “open remote” into it and use it as if the file was on their desktop (sometimes a little slower if over a slow network or used from outside the company on a slow link). If you get Filemaker server – then there is no doubt a good choice.

MS SQL and related tools a custom interface is, inherently a distributed system and multi user. Programming people from fighting each other sometimes needs to be completed. It is assumed that the data and system is multi user. Report Services reporting package too is multi user and web accessed.

Costs

server or database

Filemaker is easy to calculate. You need one copy to run it $130/year or $300 to buy and once license per machine that uses it. If you get Filemaker Server instead of the “one copy to run it” which GREATLY SIMPLIFIES THINGS , then it is anywhere from $500 to $800 per year to operate (or $2000 to $2800 to buy outright) Setting it up is SUPER SIMPLE because it is basically an Apple subsidiary and they are true to Apples ease of use vision. I recently set up my own internal server and could not believe how easy to turn it on and get it going. Always buy one copy of Filemaker Advanced because of its extra tools that greatly reduces development time. They know this, that is why they have those features in a separate and VERY useful advanced version.

MS SQL express edition can be used as a starter to get going which is free. To get the properly licensed version the simple “it costs this much for this many users” is hard to calculate (here is the guide) . Hosting the web pages and using ASPX or .Net as the glue is also free with any Microsoft computer or server. This is done with a package on everyone’s computer (Windows 7, Windows Server 2008+) called Internet Information Services or IIS. Configuring it is a little bit of a hassle, but I have written these steps down a while ago.

MS Access if you buy it as part of MS Office costs about $250-$300 more for this Office package. Stand alone, it ironically costs $119 per license. Now since I use it with MS SQL, this too needs to be purchased even if it is MS SQL Express (free edition)

Now one feather in Microsoft’s cap to bring the cost down is their reporting services that REPORTS on the data. it is fairly easy to use (yes they store settings in the most hidden nooks and crannies) but once you fight through this (or hopefully a previous company paid for this) it is fairly easy and this the cost is reduced. The cost to get data INTO  the database to then get it out, far outweighs this benefit though. If, for example, another tools inserts the data (like  Bentley AutoPLANT/AutoCAD for an EPC company for example) the reporting on this data is quite inexpensive. That is NOT the application I am designing.

The interface design and Glue cost

Filemaker has a very integrated design environment. Programming is drag and drop and pick this-that to get things connected. Yes, this is a little slower than typing, but you can’t get much wrong or syntax errors. Because you click it into place, there is a lot less syntax (if any) debugging.  The debugging tools are excellent. Making layouts AND database decisions AND glue code can all be done at the same time. Linking this table to that etc. is also a snap.

One area that is still vague (I have done DOZENS of apps still in use 6-8 years later) is interfacing to other tools. On Windows – this is easy to interface to other external tables via ODBC works easy. Getting this to work on a Mac, I have got working but a couple of years ago was a little ornery) Importing via XML and text are quite easy but XML is painful in its own right, however this is a standard on ALL platforms (windows, mac, unix). I still claim that design in Filemaker for any applications I have done is at least 8-10x cheaper (time wise and frustration wise) that any other platform.

MS Access is one of the most cumbersome tools I have ever used and I avoid it like the plague. If you are trying to make money – it is a great tool to have to support and work with because it increases the hours worked. This is not good when being honest with clients when starting / suggesting a design. Many of my peers either cringe or apologize when they hear I have to work with it. Once client I have which has a large IT department have outlawed it completely but, managers OK and buy copies since it is so easy to obtain with the proper version of Microsoft Office. It is the most incompatible tool with other databases often connected via odbc. Have 32 bit and 64 bit computers or application needs like most companies? It is a nightmare to and one has to play, play, play to figure out how to connect the data, sometimes a whole afternoon wasted only ton finally find out that others too on line have given up (try connecting it up to AutoCAD 2013 and below through VBA (built in tool programming used by Office tools and many other tools) – it does not work). It can get VERY costly thus to integrate it with other software. Often custom interfaces are built to get data in an out which again can get very expensive. Many options, settings etc. can be hidden in this nook and this cranny in typical Microsoft fashion – they have been hiding settings for years (just use MS Office tools on a Mac vs. Windows -why are Windows versions so hard to get to options and use?)

Microsoft SQL Server has so much power which can greatly reduce the cost of design of course. The more tools, the cheaper the design.  If a programmer knows the database set of tool well, things can be done mathematically in the SQL Server query and the other software that uses this SQL engine would be thus greatly simplified in other programming areas. I feel that MS SQL Server is MUCH further ahead than lets say the free open-source MySQL (now owned by Oracle) . Simple things in MS SQL Server can be so difficult in MySQL and MySQL has so many rules, caveats etc. that I am sure make sense, but MS SQL does not have a lot of these hang ups.

Microsoft .Net web programming or custom applications in either the free  Microsoft design tools or a proper full version Microsoft Visual Studio is quite easy. However, the programmer has an open slate on how to design the screens etc. Everything that is seen by the user is designed either in programming or dragged and dropped and then connected by the programmer. Yes things have come a long way, but simple validation of lets say an email address or phone number etc. is still quite expensive to design. Things have come a long way in the past 5-10 years reducing the menial stuff that people have to do. Using jQuery on the web again greatly reduces design cost. In my programming life (25 languages??) , Microsoft Visual Studio rises to the top. The only environment that comes close is Apple’s iOS and desktop design environment.

REPORTING

Both Filemaker and MS Access have reporting as a strength. For more powerful Microsoft solutions, using Report Services also reduces cost. Which is best? I like Filemaker because it has very very fast production of this report or that report. Linking in MS Access is often done visually but at the end of the data it is programmatic. The same goes for a lot of MS SQL report queries which is the real magic that hooks this table up to itself or other tables to get the appropriate report.

Complicated reporting – Microsoft’s SQL engine is a clear winner. Filemaker with a little programming and its easy to use interface is great for mid to easy complicated reports. If they are complicated, they both take a long time to render. MS Access too is easy to make reports. The issue is about how complicated is the data?

Inter-tool import/export and playing nicely with MS Office

Lets face it, there is no other tool like MS Excel. Even MS Word is fairly easy to create a graph or chart.

Filemaker, MS Access and Report Services all integrate GREAT with the MS Office tools. ASPX apps can easily download MS Excel files.

Perhaps this point is not a good point to camp on since they all work. The best I think is MS Access since it has VBA as an underlying technology which Excel uses as well. ASPX applications would be the next most flexible. Filemaker can import and export MS Excel but cannot remotely program Excel. I have not found this to be a large distraction though.

Security

The all in one tools are ok for security of which Filemaker tops the list . It has the ability to lock down to records/tables, layouts (read only, no view or full access), then pick-lists and scripts. Extended privileges and plugins can also be configured all with a few clicks. Many users can be added a well and all users can have a privilege set. MS Access is also configurable but with many Microsoft products, it is hard to use. It could be because this is one area I don’t have experience with. I have few ‘friends’ or even acquaintances that know MS Access since all of my peers avoid it like the plague. One has been led by the nose to redesign all the MS Access databases into custom applications.

There is nothing more frustrating but flexible that Microsoft’s security. So the proposal here, for example has 5 components each configurable SEPARATELY but yet use a common list of users, groups etc. in a corporate domain. 1) Web GUI or browser, 2) Report Services to see the reports, 3) ASPX IIS web server 5) the filesystem that holds the files  and 5) the application glue/methods developed by the user. Yes, one can narrow things  in  down to such an exact set of security settings and encryption of things but to set up some simple security can be nightmare.  All these have to be configured just right sometimes to narrow down access to things. Often most designers are usually happy that “because you don’t have external access to these pages – I don’t have to worry about sabotage”. Flexible but costly and – it has to get done so the client HAS to pay until it is correct even if it just an internal application. Most IT people write down their nightmares and repeat it at the next client, but each client seems to have to pay for their own view of what security looks like. What a shame and the whole model of security is rarely stored in the mind of one person.

Growing to a bigger system

Filemaker is its own system. If you get VERY complicated or wish to integrate many systems, you might have to redesign into lets say a distributed system like MS SQL, ASPX and a web browser or custom app. HOWEVER, I have never had to do that with any Filemaker solution I have developed. I have a finger print scanner connected to a Filemaker solution using a paid-for plug in. it works great. I have never had

MS Access – its first line of defense (which I implement almost from day one) is to have the data outside on a proper database like MS Server. So, is this really relevant? I would argue that this is not ‘getting bigger’ but starting bigger. MS Access databases slow down and corrupt easy. Will my solutions corrupt?

Multiple Designers – growing the staff to have a team of developers

MS Access certainly not since the end-users ‘application’ is itself a database that gets changed and users have to have new versions distributed to them. This can get costly.

Filemaker has the ability for people to access the database and design/change from a  remote machine. I have several clients who have databases shared over their internal networks ( and external ) and design can be done. The system warns you and others when you are interfering or trying to edit he same items as other people. This is helpful and a safe design proactive. I love it but I can see that if two or three people are co-designing it might be a little irritating unless they are co-located. Having people editing at different times would be the healthiest practice.

Putting code away and archiving different versions are too difficult with an all-in-one solution. In MS Access, the code can be exported but in so many places, the code and settings are not located in one spot. For Filemaker, keeping a good log is important fro a multi-discipline team. You can’t compare in text one version to the next. Because of this, it gets more costly that way for multi designers. Having a close design team would be important.

If you did things in MS SQL, Report Services, HTML and ASPX however, you could EASILY have many people attacking the code and storing it in a code-repository. Almost all the tools generate text as the core design files and the repository systems are all text friendly and dependent. Doing programs in Excel for example are difficult. I often export the code so that the code can be compared version to version.

The clear winner is the multi component approach with the alternative is a close design team. I personally like a close design team (even if worst case they are all over the globe – which I do not prefer)

Backups

Backups are essential. Collecting data is difficult. Keeping and protecting it can be more difficult. Disaster and deleting is easy.

MS Access is done via the end users and/or a certain person. This would be the same for the Filemaker non-server version.

Filemaker Server version has backups on a schedule and keeps hourlies for as many as you wish between lets say 8am and 5pm, weekly’s for as many as you wish, monthlies and yearlies as you wish.

MS SQL usually is backed up on a script or an administrator does it. If someone knows about the databases and backs it up, any solution is good therefore the automated ones being a lower cost and safer solution.

Filemaker is a set it and forget it in typical Apple like fashion and yes it keeps going.

 

 

Background and Links:

Remember that this whole blog is an excuse for me to be able to find my own bookmarks. Hence for myself and others, here are some good resources. There is no need to follow the links. The information has been summarized above.