General discussion


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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel is not a true database

by jdgretz In reply to Which database solution i ...

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.



Collapse -

Excel is a spreadsheet,

by Jaqui In reply to Excel is not a true datab ...

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

Collapse -


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....)

Collapse -

Design, design, design

by dmarston In reply to Which database solution i ...

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).

Collapse -

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.

Collapse -

Other !!!

by Tony Hopkinson In reply to Which database solution i ...

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.

Collapse -

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.


Collapse -

If it's small and local then OK

by Tony Hopkinson In reply to I think you're being a li ...

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.

Collapse -

Access and ASP

by rodp In reply to If it's small and local t ...

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).

Collapse -

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.

Related Discussions

Related Forums