Considering FileMaker or Access for a Starter Database



  • So a question about a new business needing a database of sorts and they were considering FileMaker or something like it. I felt that the responses were worth including here as there is good information.



  • So this will be a long thought but here goes....

    It's nothing against FileMaker, I've used and supported it for years and for what it is, FM is pretty good. Generally better than Access. It is definite an Access competitor.

    The problem is, you never want an entry level database conceptually. It's just a bad idea. What these are are either desktop databases and/or client / server database applications. Both are very, very bad ideas. The problems with this are large.

    • This is a very hard style of system to use and protect. With effort you can, of course, but as these are designed to run on desktops it introduces problems.
    • What starts as an "entry level" quick and dirty way to just get started won't stay that way and your database is an investment in your future. Anything you skimp on today will bite you... forever. You can do a lot of things quickly and cheaper getting started, your data isn't it. Any data you put into FM you will have to migrate out later to fix things. That will be more expensive that doing things right from the beginning (dealing with several people who made this kind of mistake just this week.)
    • FM (or Access) are not free. Not even kind of free. They are rather expensive. That's bad because an enterprise database that is vastly better for you would be actually free! Don't spend money to do something poorly when you could do something awesome for free. (LibreOffice Base will do this for free, but that only solves one problem and is still not good.)
    • Architecturally this locks you into a legacy LAN mentality and doesn't just hamper your database system, it makes your business hampered and less flexible and more expensive to run.
    • These products (except LibreOffice) lock you into expensive additional licensing like Windows or Mac that you might be okay with today but might regret tomorrow. Don't tightly couple your data to your desktop choices, that's unnecessary and a bad idea.
    • As you grow you will need to license these products for every new user. This makes new people or part time people extra expensive or makes you do bad things like only giving access to some people. It's not an efficinet way to use a system.


  • What is the alternative?

    Database: Something enterprise and free. Don't consider anything that isn't totally free (e.g. no MS SQL Server, Access, FileMaker, etc.) PostgreSQL, MariaDB and others are very high end, and free for unlimited use, forever. You will never outgrow them, you will never need to pay for them and the support for them is from a much higher class of professional (DBAs, not desktop techs.) If you don't need relational data, look at MongoDB, Redis and other NoSQL options.

    Application: Your database is not your application. The database just holds the data. You need an application to interact with it. Don't use a desktop app, that's archaic and hasn't been acceptable since the 1990s. Make a web app, and make it on free tools, don't tie it to anything that costs money. You can build your own from scratch using PHP, Ruby, Python, Node, Go, etc. And there are toolsets out there for making stuff that isn't from scratch (I'd have to look into current options.) What is important here is that if your data is in a good database, a cheap and fast application today isn't an issue because you can replace that easily without replacing the data! THIS is how you do a "starter" system. You don't do a starter database, there is no such thing. You do a starter application.



  • @scottalanmiller said:

    So a question about a new business needing a database of sorts and they were considering FileMaker or something like it. I felt that the responses were worth including here as there is good information.

    I've used Alpha Five, it's actually what I built my original billing system on. You can use pretty much whatever database you want (it uses ODBC). I built a couple tests on DB2 and it worked well.

    Any more I use Drupal for this type of stuff. It's web based, you can set up your own database structure if you want, and with views reporting is easy.



  • How to run a web app of which I speak?

    Assuming a small office with just desktops you could just run a database (like MariaDB) on any desktop but Windows desktops are not licensed to be used this way so this can cause issues (with Filemaker as well.) Mac OSX will not have this issue, MariaDB or PostgreSQL will run great from a Mac OSX desktop. Or, probably far better, you could install VirtualBox on a desktop, install CentOS to it, install the database to that and run the database (and the application) right from the VM. This will make it very easy to move, back up and manage. As you grow, you can just move it as needed. Migrating to a server will be trivial down the road. No problems with licensing, this will save you money today and save you money for forever while making your company more capable. It's a win/win/win design. Designed to start cheaply and grow indefinitely.



  • If you like Oracle and have people that can deal with it, I've used APEX before and it's not too bad. I've only used it with the free database though.



  • Access needs to die in a fire 🔥 along with faxing.

    I will agree with @scottalanmiller saying that it should never be used. I will disagree with building entire applications to connect and manage a database.

    Most of the time the business need when tools like Access are legitimately brought up are things that a single person or department needs.

    It is a waste of that company's money to develop an entire application for it.

    Excel and Libre Office Calc are designed to connect to a database. These power users that need this information are generally intimately familiar with the applications already.

    Install SQL Express on the local user's machine, or for a department, use a Linux VM with MySQL (MariaDB) and the appropriate ODBC connector.

    The developer only needs to get the basic database structure designed and then the users can query the data as needed.



  • @JaredBusch said:

    I will agree with @scottalanmiller saying that it should never be used. I will disagree with building entire applications to connect and manage a database.

    Ah, good point, I removed the original question as they did not post it here. The response was for a company that needed access to this from several desktops and several mobile devices. I don't know what "several" means, but I am assuming a minimum of six end points today based on the context with an expectation of future growth.

    I didn't mean to imply that a one person company would do the same thing. I should have included more context. Still would not use FM, too expensive. Excel, Base and others are too good.



  • @scottalanmiller said:

    @JaredBusch said:

    I will agree with @scottalanmiller saying that it should never be used. I will disagree with building entire applications to connect and manage a database.

    Ah, good point, I removed the original question as they did not post it here. The response was for a company that needed access to this from several desktops and several mobile devices. I don't know what "several" means, but I am assuming a minimum of six end points today based on the context with an expectation of future growth.

    I didn't mean to imply that a one person company would do the same thing. I should have included more context. Still would not use FM, too expensive. Excel, Base and others are too good.

    Ah, yes. In that context, a simple web based app is absolutely the best way to go.



  • @scottalanmiller said:

    desktops and several mobile devices

    That's enough to discount filemaker I think. You can build native mobile apps with Alpha Five but for the price it's not worth it. I think Drupal shines here again. You can use MariaDB, PostgreSQL, and Oracle.

    You can do mobile with APEX, but you're locked into Oracle.



  • I think the one big plus to systems like FileMaker, Alpha Five, and possibly Access are the reporting tools. Using those with ODBC can generate some really nice reports. Faster than querying and exporting to excel and then trying to move data around to make it look nice.

    But like I said before, Drupal can do this with Views, and APEX has reporting also.



  • @johnhooks said:

    @scottalanmiller said:

    desktops and several mobile devices

    That's enough to discount filemaker I think. You can build native mobile apps with Alpha Five but for the price it's not worth it. I think Drupal shines here again. You can use MariaDB, PostgreSQL, and Oracle.

    You can do mobile with APEX, but you're locked into Oracle.

    I've never looked at Drupal in that kind of capacity. What database interface creation options does it have?



  • LibreOffice Base might be a good way just to get started. It will talk to the databases that you mentioned, but it still needs to be deployed to a local desktop. But you could start with that to get something working "same day" and then use some other tool to make the application that people actually use.



  • I invited the OP to comment on this thread. The post here had more responses in five minutes than the original did elsewhere in an hour 🙂



  • @scottalanmiller said:

    @johnhooks said:

    @scottalanmiller said:

    desktops and several mobile devices

    That's enough to discount filemaker I think. You can build native mobile apps with Alpha Five but for the price it's not worth it. I think Drupal shines here again. You can use MariaDB, PostgreSQL, and Oracle.

    You can do mobile with APEX, but you're locked into Oracle.

    I've never looked at Drupal in that kind of capacity. What database interface creation options does it have?

    Everything is created from an entity. Normally you create a content type and then add the fields you want, but content types have a bunch of extra information like author, timestamp, etc. If you create a bare entity it has only the info you want. This is all done from the admin menu and you design the form that gets filled in.

    I did an intranet for the shop that allows us to track maintenance records, Lock Out Tag Out, Safety, and production issues by serial number. The image shows the fields created for a safety accident report.

    0_1455819512612_Screenshot 2016-02-18 at 1.13.28 PM.png

    Here's what the form looks like when a user is filling it out (it's longer, I can't fit all of it in the screenshot). Fields can be hidden or not usable based on permissions.

    0_1455819686646_Screenshot 2016-02-18 at 1.19.41 PM.png

    And here's a sample report with views based on filters (no accident reports so I had to pick something else). If you click that CSV button, it downloads all the info to a CSV file. The blue links go to the actual entry.

    0_1455819896437_Screenshot 2016-02-18 at 1.23.21 PM.png



  • Nice, it's been forever since I've played with Drupal, I need to mess with that again.



  • I'm going to throw a Drupal site up on ASO to check it out.



  • The new APEX looks interesting. If you click get started you can do a hosted demo. Looks like you can do desktop apps, mobile, and web.

    Eh I think the differences are just the layouts of the apps, but it looks easier than the old one.

    https://apex.oracle.com/en/



  • And here's the resident Access fanboy to have his say. I don't know why people have such a downer about it. There's certainly a lot of misinformation out there. I suggest people read this wiki https://en.m.wikipedia.org/wiki/Microsoft_Access and get educated.

    People seem to assume Access will only work with Access databases (Jet), when it works brilliantly with other databases, especially SQL server but I've written apps based on Oracle. I mostly use it with SQL Server these days. Whatever you use, the database should reside on a server not a desktop.

    And Access runtime is free. So you can develop multi users solutions using Access runtime and SQL Server express for practically free.

    And people talk about web apps. Access is designed to produce web apps, particularly with Sharepoint. Think of it more as an alternative to Visual Studio.

    I thing the haters either have hardly used Access or haven't used it since the nineties.

    It's awesome and keeps getting better with each new release.



  • @Carnival-Boy said:

    People seem to assume Access will only work with Access databases (Jet), when it works brilliantly with other databases, especially SQL server but I've written apps based on Oracle. I mostly use it with SQL Server these days. Whatever you use, the database should reside on a server not a desktop.

    I'm very aware that it talks to SQL Server, that's the only way that I would ever consider using it, but was unaware that it works with other options. Will it talk to the big two: MariaDB and PostgreSQL or at least Firebird?

    The assumption is that Access would be used this way, the problem with it is the client/server model. That's 1990s and pretty horrible. I know that you can put Access onto SQL Server and Sharepoint Enterprise for a full modern app, but the cost is incredibly high.



  • @Carnival-Boy said:

    And Access runtime is free. So you can develop multi users solutions using Access runtime and SQL Server express for practically free.

    The runtime is free? Is that true on iOS devices too (a requirement for the client in question.) What about on Linux? Can we "access" it from anywhere?

    SQL Server Express is free... except for the Windows Server license cost which would be required for even a tiny company and means that the cost shoots up by about $700. Not epic, but for a ten person company, not trivial either. But if you can use PostgreSQL, that would solve that piece.



  • @Carnival-Boy said:

    And people talk about web apps. Access is designed to produce web apps, particularly with Sharepoint. Think of it more as an alternative to Visual Studio.

    We've done this, although to be honest the results were horrific. SharePoint and Access were so slow together, it was painful to use. Not sure if we were missing something but it didn't seen to use WebSockets or anything modern and the response times from SP on SQL were rough.

    Can you do with with anything other than SharePoint Enterprise? When we looked at it, it was an enterprise only feature. Which you get with Office 365 E3, which we have and that's how we got it, but for a small business without O365, I feel like the cost is out of reach.



  • Someone on the thread mentioned Zoho Creator. It looks interesting, but looking into the pricing model, it doesn't look interesting at all.



  • @scottalanmiller said:

    Someone on the thread mentioned Zoho Creator. It looks interesting, but looking into the pricing model, it doesn't look interesting at all.

    Ya I think really the only thing they have that's truly appealing is the Email. It's free for 10 people (plus like 15 more with referrals) and only about $2.50 (something around there) for paid accounts.

    However, they still don't have desktop exchange support, only EAS. I use the web interface but it's still kind of crazy because you can't use any calendar apps built for desktop.



  • That Zoho product is NOT cheap. Saw your post, $5-$15/user each month. That adds up really quickly. That's well into custom, enterprise app prices if you get any number of users. Paying $15/mo for each user when you have to make the app yourself doesn't make sense. You could be paying for something already built at those prices.



  • So the place I interviewed with yesterday is a big Oracle shop. I was surprised to hear they use APEX for some of their process improvement and internal web app use. I thought that was interesting, and the interviewer thought it was interesting that I've used it before.



  • @scottalanmiller said:

    @Carnival-Boy said:

    People seem to assume Access will only work with Access databases (Jet), when it works brilliantly with other databases, especially SQL server but I've written apps based on Oracle. I mostly use it with SQL Server these days. Whatever you use, the database should reside on a server not a desktop.

    I'm very aware that it talks to SQL Server, that's the only way that I would ever consider using it, but was unaware that it works with other options. Will it talk to the big two: MariaDB and PostgreSQL or at least Firebird?

    It's called ODBC. 🙂

    I used Access to connect to my AS/400's DB2 server. As long as ODBC is presented to the client, it works just fine.



  • @scottalanmiller said:

    @Carnival-Boy said:

    And Access runtime is free. So you can develop multi users solutions using Access runtime and SQL Server express for practically free.

    The runtime is free? Is that true on iOS devices too (a requirement for the client in question.) What about on Linux? Can we "access" it from anywhere?

    2013+ offers some fun stuff, but in a sense, no, it's not a "web" application. You can use Access, and an ACCDB, as a backend for design and the DB for IIS. I believe Apache can do it as well, but haven't bothered trying nor would it be really worth it other than a "look what I can do" thing.

    SQL Server Express is free... except for the Windows Server license cost which would be required for even a tiny company and means that the cost shoots up by about $700. Not epic, but for a ten person company, not trivial either. But if you can use PostgreSQL, that would solve that piece.

    SQL Server runs on any version of Windows. I use Developer on Windows 8. I've used Standard on XP. It's not a perfect solution, but if you need to get some info into standard format of SQL and don't have interwebs access, it's great.



  • @PSX_Defector said:

    SQL Server runs on any version of Windows. I use Developer on Windows 8. I've used Standard on XP. It's not a perfect solution, but if you need to get some info into standard format of SQL and don't have interwebs access, it's great.

    The OP has an office too big to be used that way though for licensing reasons.



  • @scottalanmiller said:

    @PSX_Defector said:

    SQL Server runs on any version of Windows. I use Developer on Windows 8. I've used Standard on XP. It's not a perfect solution, but if you need to get some info into standard format of SQL and don't have interwebs access, it's great.

    The OP has an office too big to be used that way though for licensing reasons.

    Just pointing out that Windows Server is not a requirement for SQL Server.

    There is always the use of Azure's hosted DB. Again, simple, easy, ODBC compliant, uses standard SQL Server tools to modify and access. It's got some quirks, like I couldn't use hMailServer on it without some major modification of the schema, but otherwise it's plenty easy for quick and easy DB deployment.

    Given the "need" for iOS "access", seems stupid not to put it in a hosted solution. Licenses are then just a line item on the invoice.