General discussion

  • Creator
  • #2179589

    Which database solution is better – Excel, Access, or other


    by ntpropserv ·

    New business owner – property inspection/management. Multiple clients, but use different, client-specified databases for tracking results. We already use a “universal” inspection form; but have yet to create a “universal” database. The software must have the ability to generate a “universal” form, with most of the essential data already “filled-out” Suggestions, please

All Comments

  • Author
    • #3043794

      Excel is not a true database

      by jdgretz ·

      In reply to Which database solution is better – Excel, Access, or other

      Not knowing what you really need to do, it’s difficult to give you a great answer. However, if you are mostly into property management, then there are some industry specific programs that do that very well – Yardi is one with which I am very familiar.

      I don’t know what you mean by a universal inspection form – is this a move-in/move-out form or a pre-sale home inspection form?

      What industry segment are you in anyway?

      With a bit more information we might be able to give you a better answer.

      As for databases, Access, SQL, MySQL, Oracle, Progress, FileMaker (not one of my personal favorites, but very flexable), Paradox, etc. are all good solid databases and all are much better for data reporting and relating than Excel.



      • #3043775

        Excel is a spreadsheet,

        by jaqui ·

        In reply to Excel is not a true database

        not a database at all.
        use it for doing accounting tasks, as it was designed for.

        • #3127241


          by noyoki ·

          In reply to Excel is a spreadsheet,

          We still have people using *WORDPERFECT* as a spreadsheet at our firm.

          (That we use it at all is ludicrous enough….)

    • #3123067

      Design, design, design

      by dmarston ·

      In reply to Which database solution is better – Excel, Access, or other

      MySQL, MSSQL, Oracle, etc. etc.
      It doesn’t matter which DB backend you use. The BIGGEST and MOST IMPORTANT part is good DB design!!
      RE-Read that.
      You should spend about 10 times as much time designing your DB as you do deploying it.
      If you are unfamiliar with DB design, pay someone. It will more than pay for itself in the long run.
      I have spent MONTHS (at $75/hr) re-desiginging and migrating a poorly designed DB because they were trying to save a few bucks in the beginning, and ended up paying me several times what it would have cost to build it right to begin with (not that I?m complaining all that loudly).

      • #3123489

        I agree, but…

        by greg ·

        In reply to Design, design, design

        As a database designer I completely agree with your assertion that good design is essential to any DB project. (Duh!)
        But performance and stability is an important consideration. So the choice of a backend becomes an important design choice.
        Given the very few constraints that Microsoft has placed on SQL Express, I see no reason to ever choose Access, much less a spreadsheet, as the backend for any project. If the amount of data stored exceeds the 4Gig limit of SQL Express then the value of the data will probably be great enough to justify spending a couple of grand on the SQL Server 2005 Workgroup Edition and “migration” will be trivial.
        Note that I’m not pimping Microsoft here. There are also some very fine open source databases from which to choose. My first choice would be PostgreSQL, followed by Firebird. MySQL would be way down the list due in part to the complications that are sure to arise now that Oracle has its toe in the water.

    • #3122950

      Other !!!

      by tony hopkinson ·

      In reply to Which database solution is better – Excel, Access, or other

      Excel is not a database and for anything serious nor is access. If it’s going to be multi user and or fairly large or you want to impose some real business logic at the database end, get a proper DBMS.
      You have lots of options on this front and anyone of them is much better than access. If you want to stick with access or Excel as the front end either will drive an MS SQL database with external links.

      • #3122901

        I think you’re being a little harsh

        by jdgretz ·

        In reply to Other !!!

        Tony –

        I’ve seen some very complex databases set up using Access. I agree that if you are dealing with large amounts of data then Access is not the way to go, but in this instance he is not. At least he is not presenting it in that manner. As you mentioned, it will drive MS SQL, and SQL is the basis for the query language used in Access, so it is not all bad.

        Someone who can write good nested queries can get good buisness intelligence out of Access.

        No, it may not be THE best tool for all things data, but it’s a reasonable starting point and relatively simple to upgrade from – especially as it is ODBC compliant.

        I do agree with previous posts in that the design is the most important, as well as my original post – for what exactly is this going to be used. Once we know that, it’s much easier to suggest the correct size hammer for hitting this nail.


        • #3121978

          If it’s small and local then OK

          by tony hopkinson ·

          In reply to I think you’re being a little harsh

          But probably the most regular mistake is to think it will do for now while the system is in it’s infancy. If it’s expected/hoped to be used by many or for a lot, then the cost of switching away from it can be very great. Connectivity is not a problem, the main one is switching from desktop to client server which requires extensive design changes. You can switch from a destop backend to client/server but it will run like an absolute dog. If you design it with a future switch in mind then it will not perform as well while it is a desktop. Just at the end of a three man year conversion project in a similar vain and even after all that expense, the solution is still crap compared to what it could be.
          Access’ only advantage is it’s so called development environment, if all it is being used as is a back end then with tools like MSDE and MySQL around it’s a non starter.

          Access(any desktop) is a good tool for personal use, as soon as it goes beyond that, performance, utility and effeiciency falls off exponentially.

        • #3121924

          Access and ASP

          by rodp ·

          In reply to If it’s small and local then OK

          It all depends on how you use it. I’ve built many web apps using Access as the back end for various Admin tools. These apps are used by a limited number of people and are password-protected. Often they are used to populate static HTML pages or ASP Application variables which are used in public pages. You can get away with a LOT with access if you open recordsets in read-only mode with forward-only cursorts. Using “disconnected” recordsets also makes a big difference.

          Other uses: as mentioned, Access makes a great frontend for ODBC databases. I’ve used it to link to Postgresql and mySQL. A variant of this is to give users queries which take local Access table data and insert it into remote ODBC tables (including open source DBs like mySQL and Postgresql).

        • #3123491

          As an ASP

          by tony hopkinson ·

          In reply to Access and ASP

          backend as long as long as it’s not massive and IIS is doing the connecting it will do the job. A great front end is not the way I’d describe it but it will certainly with a minimal amount of effort bash together something reasonably professional looking as long as it’s not too complex and of a fairly standard design.
          The thing you have to remember is it’s a desktop, all processing is done ‘clientside’ this would be on the web server in an asp solution.
          But the imporant point is each function has to suck all the data it may need out of the dbms in order to operate on it. Which is where the final required design for your system comes in.
          Just converting one over now. In the desktop, it did a select * and locate, very fast, the same thing in MSSQL took 3 seconds. Switch it over to select f1,f1,f3 where fk = 1 and it doesn’t even get a tick.
          Not rocket science and not hard to do, but on a mature piece of db client software, a lot of work.
          Even worse when the select * on the database is globally accessible and may or may not be read or written to all over the damn app.

          Locking, mislocation, field not found, nightmare.
          Even worse previous solution was not ADO. So you get things like it doesn’t post a changed record on close, it cancels it. No nested transactions, still don’t believe that. Can’t have a blob in the select clause if you use distinct. First/Last leaves bof/eof true. Pick the solution you want to finish up with not teh one you can get away with starting with, overall it’s a lot less work.

      • #3127407


        by jkowolf ·

        In reply to Other !!!

        It doesn’t sound like this company is at the stage where, number of users and data size is going to be a problem. My company manages over 130 million dollars in assets with over 10 users with Microsoft Access. Yes, we’re looking to move to SQL server (Our website utilizes it.), but we’re much further along.

        • #3121240

          10 users ?

          by tony hopkinson ·

          In reply to Overkill

          So is that one table, ten records, two columns with a name in one and 13,000,000 in the other ? And ten people take it in turns to open it and spell their name correctly ?

          Not taking the urine, but your description of it’s use is a tad uninformative. Number of concurrent transactions, locking complexity, volume of data, referential integrity. Relational complexity, scaling projection….
          Go big on anyone of these and access will not cut it. Attempt to go big and fail under load, rework coming out of your ears.

          There are much better and much cheaper options. As for overkill , compare access’s foot print to MySQL.

    • #3122926

      MS SQL Express

      by brandon.aiken ·

      In reply to Which database solution is better – Excel, Access, or other

      I would use something like MS SQL 2005 Express Edition (which is free, but limited) or PostgreSQL. You could then use a web-based front end (ASP, PHP, Ruby on Rails) or a .NET app to access it.

      I’d avoid MySQL simply because of personal bias, plus the whole InnoDB thing that might cause licensing problems with MySQL down the line.

      • #3121950

        Lots of options…

        by peteraaaaaa17 ·

        In reply to MS SQL Express

        I’ve been working with MySQL for years now and feel it is a solid option when looking for a robust database. I’m not familiar with this InnoDB licensing issue, could you explain this Baiken? I’ve grown even fonder of MySQL after their latest release that adds enterprise features like stored procs, view, and triggers.

        I have heard great things about PostgreSQL, but have yet to get my hands dirty with it. From what I understand, it is light years ahead of MySQL in terms of enterprise functionality.

        Worth exploring, Oracle has released an express version that targets the same space as MS SQL Express. This would be a good option if you anticipate your database needs scaling to the point of needing an enterprise quality commercial solution.

        Picking a database platform is no easy chore. Alot of homework and proof of concept work should be done to see what fits best in your environment. I encourage avoiding MS Access as an interim solution. Any period of use will result in having to migrate data to another platform at some point.

        • #3128000

          InnoDB & MySQL

          by brandon.aiken ·

          In reply to Lots of options…

          I’m not an expert on the topic, but…

          MySQL uses the InnoDB engine, at least in part. That’s why MySQL has a dual lisencing scheme. The problem is, Oracle just bought InnoDB. So Oracle could essentially nuke MySQL.

          My personal bias against MySQL is because it’s so far from ANSI SQL, and the fact that it still harbors artifacts from being an SQL-like interface to flat text files (the origin of MySQL, IIRC). The MySQL database is still one giant file, making reads fast but making writes horribly slow. I’ve also seen reports that it can unexpectedly silently truncate data… which no DBMS should ever, ever do.

      • #3121431

        MS SQL Express loog good to me too!

        by greg ·

        In reply to MS SQL Express

        The limitations of the Enterprise Edition are few (4 Gig per database, uses only one processor and 1 Gig of RAM) and if you have an application that gets too big for these constraints it’s probably valuable enough to justify an upgrade to the Workgroup Edition (About $3K)

    • #3121861

      Depends on your need

      by master3bs ·

      In reply to Which database solution is better – Excel, Access, or other

      I prefer Access running on an SQL server. That’s just me. Ultiamtely it depends on what you’re using it for.

      I recently had to rewrite a system of sorts that had been done in Excel; but was much to complex for Excel. Excel is just a spreadsheet and has limitations.

    • #3121854

      Not much left to be said…

      by dfirefire ·

      In reply to Which database solution is better – Excel, Access, or other

      All the colleagues in the previous posts are right: first proper design, scalability, the rest is a matter of taste and money. Excel doesn’t count (I even know people who do word processing with it!), Access might end up tackling the scalability (programming rule: never believe a spec that says things will remain small!) when used for more than just a front-end. A true DBMS (MySQL, PostGreSQL, …)should be your choice, with front-ends written as web pages, Access forms, client programs, … Your Universal form can be written in any of them.

    • #3123495

      It depends on your size

      by wilrogjr ·

      In reply to Which database solution is better – Excel, Access, or other

      It depends on your size:

      If your data requirements are small (less than 1 GB), then MS Access may be a valid alternative as it offers some RDMS features along with integrated reporting albeit on a relatively small scale.

      Access offers a relatively intuitive interface for working with data and the wizards and help files can guide the novice user to creating a data repository and generating reports.

      If your needs (or your business growth plan) require more data than 1 GB, you may need to pursue a more robust solution, a la MySQL or MSDE (free SQL Server without EM support).

      If your needs rely on the second solution, be prepared to obtain help (whether from a friend or hiring a consultant). MySQL and MSDE aren’t particularly “novice-friendly”. You need to understand some basic database scripting concepts to work with the development tools.

    • #3123302

      Access Probably…

      by ·

      In reply to Which database solution is better – Excel, Access, or other

      If you a single or a few forms which uses a database to store the data, and make reports based on that data – as a non-programmer; Access is a good match. Access can create your forms in a basic way based on the data you define. The database is really a file, and can be stored on any computer, you can even set it up on a server, and access it from other computers. If you want to make a web based form, and store the data in access, then its more complicated. You could use a data access page, a tool built into access, but you cannot use the form over the public internet. If you want to make a form that can be accessed via the public web, then you will have to get more exotic, possibly using, or or both to get that done. You likely would need a programmer to get that done. The basic Access forms are really to be used on your internal network in basic mode.

      If you are a programmer, and will have multiple clients accessing the database at the same time, and need complex forms and business rules, step up to MS SQL Server, or Oracle.

    • #3123258

      Without more detail, perhaps impossible to say

      by tor24 ·

      In reply to Which database solution is better – Excel, Access, or other

      Can you do everything you want to do using the paper forms? If you can’t do it all on paper, even though it would be difficult, then it may not translate to a reliable process for a database system.

      What results do you expect the system to produce or compare? This is the most important part of the system, because it determines what you need to put into the system.

      Why do you want to add the headache of a database? As you iterate through development cycle, revision, distribution– the entire software engineering process, again and again, even for the smallest system for all of your users/clients can itself become a source of problems. Users will complain — always.

      Do you have resources to enter the data into a database? Spitting out a form with rudimentary data is one thing, but re-keying the results on the form back into the database introduces a source of error, and can become a significant cost if you need to hire clerical staff when this chore loses its fun and appeal to you or your clients.

      Are you really going to look at the output? For years, I produced summary and analysis reports for people who rarely looked at them, but wanted them as if the entire world revolved because of it. When I put them on-line and delivered metrics, I could convince people that they didn’t need many of them. They were fun to create, and I was well paid for them, but what an awful waste of resources.

      Will it be meaningful? Eventually people want information that is on the fringes of the spectrum. Volumes of detail to weed through because something is wrong, the line that simply says how much is in the bank, or the exception lines that indicate when something may not be right.

      Will it add to or subtract from your bottom line in time or money? Often these things become black holes of money and time.

      Make certain you make decisions based on achievable goals and not simply for all of the right reasons. If all of this doesn’t discourage you, then you’re on your way. This question should never as simple as picking a database program.

    • #3127413

      Use Access

      by jkowolf ·

      In reply to Which database solution is better – Excel, Access, or other

      I’d use Microsoft Access. Depending on how complicated your information is, you may need to hire someone to help you setup the tables and link them. This will make data entry and any reporting much easier. Once you learn how things are setup, you can customize and create your own. It is easy to have several people viewing and/or entering information if you have a computer network. One of the drawbacks is everyone needs a copy of Microsoft Access (Not contained in the basic versions of Office.) and it can be difficult to create/edit items when others are working in it.

      • #3127365

        NEVER use Access!

        by greg ·

        In reply to Use Access

        As you said, “One of the drawbacks is everyone needs a copy of Microsoft Access (Not contained in the basic versions of Office.) and it can be difficult to create/edit items when others are working in it.”
        With SQL Server 2005 Express you’d have NONE of those problems. It’s free, easy to set up, and should your application grow beyond the (very few) limits of Express, an upgrade to the workgroup edition is trivial.

        • #3121244


          by tony hopkinson ·

          In reply to NEVER use Access!

          If it’s going to be multi user and or high volume, or complex, Access is the last thing you want to use, it’s a desktop. If your application grows out of it and you need to go client server the conversion process is an absolute nightmare ***. I always treat an access solution, short of maintaining a CD collection as amateurish drivel to be quite honest.
          Some say that’s a little harsh, but they haven’t been faced with trying to scale it up. There’s no excuse for it now anyway. MySQL, PostGre, even MSDE are much better solutions. You can always use access as the front end, though I wouldn’t recommend that either.

          PS You can write an access based db application that does not require the user to have access installed. It still won’t scale though.

          *** Yes you can port an access database into a client server DBMS easily. Switching it over to client server application architecture is a completely different ball game though.

Viewing 9 reply threads