Re: Oracle8 vs. MS Access

Greg Williams wrote:

> I have been asked to research the difference between using a Oracle8
> database vs. MS Access 97 database. If you could give me specifics on the
> differences and advantages/disadvantages of the two databases I would be
> most appreciative. Security opinions/detials would be a plus!
>
> Greg Williams
> Lambda Consulting
> greg.williams_at_lambaconsulting.com

Where do you want to start?
Access 97 is not designed for a corporate data warehouse. Essentially, it is useful for smaller, workgroup solutions, and also makes a good, easy to develop front end. It is also extremely useful if you are interested in rapid prototyping. The downside to Access is that it uses the client PC to perform all of the data selection, updating etc. This means that generally, the entire database is transmitted across the network, in order to obtain maybe one record of information. Not the most efficient use of bandwith. Another problem with Access, (and databases similar, Paradox, FoxPro, Dataflex etc.) is that it relies on each individual client to ensure that the database is maintained in a stable state. Transaction Rollback is not as effective, and if client PC's crash, the database can continue on other PC's, however the update may remain inconsistent.

The strength of Oracle, and other like databases is that the Oracle Server is the only means of accessing the data. This ensures that the database integrity is more effectively maintained. Another advantage is that network bandwith is more effectively utilised. By having a client submit a query, and only the resulting dataset being transmitted over the network, the network traffic is reduced. Further, the client utilises it's processor for what it does best, managing the user interface, whilst the server does what it does best, maintaining the database.

Unfortunately, the complexity of Oracle is far greater than Access, and requires some actual learning to be done. Yuk. Access 97 is simple to install, and can be picked up quite quickly. A database administrator for Access '97 basically has to occassionally repair and compact the database to ensure that it continues to run. With Oracle, the administrator has to actually understand what is going on. This means that the initial setting up of the database can take far longer, however the resulting performance and flexibility is far greater.

One simple note, having set up an Oracle Server, and an MS SQL Server, the SQL Server was the easier. However, Oracle did outperform it in just about every other way.

One final note... Oracle has an upsizing tool that converts an Access 97 database, so the rapid development approach using Access could be translated to Oracle. (I haven't used this yet though.) I like Oracle, even if I don't quite fully understand it yet. I can see where it is powerful. I also like Access, it is easy to program, easy to develop, and easy to maintain. I am currently looking at using Oracle as the backend, and Access as the client end.

Piquet Received on Tue Dec 08 1998 - 00:00:00 CST