What are the differences between Access and Oracle?

Question

I'd like to know what are the differences between Access and Oracle.

Answer 1

What are the differences between Microsoft Access and Oracle?

Difference One: Access is a small database system, which will allow you to create a small-medium sized database with minimum security features. Oracle can be integrated with NT security, which gives tight control over who can and cant get to your data.

Difference Two: Access is a lot slower over networks as all the processing is performed on the client machine, whereas with a large DBMS like Oracle (Or SQL Server), you send only a query (such as "SELECT * FROM Customers WHERE CustID=23;"), and only the required results are returned. If you ran this across a network with Access, all the records would be sent across the network, leaving the client machine to select the required record.

Difference Three: Access gets really slow if you try to connect more than 10 users (depending on the size of the DB and how it is being utilized), where are Oracle can accept hundreds/thousands of simultaneous connections (Depending upon the server it is sitting on).

The final major difference is that of triggers and stored procedures. Access can NOT store procedures which can be executed remotely. You can write functions and with a bit of "jiggery-pokery" get the server side to execute them vy setting flags or something in your database and getting the server to check at pre-defined intervals if the flag is set. With Oracle/(Other Large DBMS's) you can call the stored procedures and they will be executed on command on the server side. Triggers are like events on an Access form, such as "After-Update". When you update a record into Oracle, you can set triggers to automatically run procedures to check for pre-defined rules or whatever.

from here

Answer 2

Difference in what terms?

Access is a database provided by Microsoft. Oracle is a database provided by Oracle corporation.

Refer to this link for a detailed discussion for differences in how they implement SQL.

Answer 3

One is a heap of rubbish, and the other one is from Microsoft.

No, seriously, someone set me straight if I'm wrong. Can Oracle tell the difference between an empty string and a NULL yet? I'd really like to know.

Ah, my beloved DB2 ;-)

Answer 4

Adding on to the previous answers, another major difference with Oracle and MS Access is concurrency. Oracle as well as MySQL and postgreSQL and some others can handle multiple 'transactions' and 'commits' Basically, if you use a database with a website, a more secure and stable database would be Oracle, likewise an application.

If you use MS Access, you're likely to run into problems with a lot of web traffic, especially if the content in the database is being written and updated by multiple people.

Oracle and the like can be scaled to very large sizes....think Myspace or a Country's financial system (ERP).

Answer 5

Again, a thread in which all the answers miss the point.

Access is a development tool for creating database front ends.

Oracle is just a database, not a development tool (though it apparently now ships with a set of development tools, that doesn't change the fact that Oracle is still a database, not a development platform).

Perhaps you think your question is about two database engines, but as written, it's like asking "what is the difference between an automobile and a restaurant?"

Of course, Access ships with a default database engine, Jet/ACE, and it's likely that's what you mean to be asking about. I certainly can't blame some people with only casual contact with Microsoft products for failing to know this, as Microsoft has obfuscated the distinction between the two, both in its documentation and in its naming of the data providers that you have to use in connect strings.

But that's no reason to remain ignorant.

Key differences between Jet/ACE and Oracle (or any database server):

A. with Jet/ACE there is no server-side process managing and martialing client data requests. All clients to a Jet/ACE database are operating directly on the file, using a locking file. This means certain things:

• data in Jet/ACE files can never be as secure as the data stored in a server database.

• Jet/ACE will pull more data across the wire (it needs metadata and index pages in order to request the desired data pages from the file system), although with proper indexing and query design, it's not nearly as inefficient as the anti-Access bigots would like to believe.

• Jet/ACE has no transaction logging, and because its data file is being opened simultaneously by multiple users, is open to the risk of corruption.

B. Oracle is significantly more expensive to deploy and maintain than a simple file-based system like Jet/ACE. This means that:

• For small businesses with small user populations and no significant security issues, a Jet/ACE back end can be perfectly suitable, assuming a properly-designed front end and proper deployment (e.g., splitting front end/back end, not sharing front end, etc.).

• Some businesses are small enough that they don't have a dedicated server on which to run a behemoth like Oracle. No doubt Oracle has a desktop version, but it's inadvisable, in my opinion, to run a program like that on anything other than a dedicated server, since user operations could interfere with the reliability of the database back end.

• Because of the tight integration between Access as front-end development tool and Jet/ACE, it's significantly easier for non-technical users to put together useful database applications without needing to be an expert on Oracle (or SQL Server or whatever).

So, ultimately, choosing between a server back end and a file-based solution like Jet/ACE is going to depend mostly on how valuable in terms of money the increased security, scalability and reliability of a server database is. For any number of small businesses, there is really no contest -- Jet/ACE wins hands down.

But there are a lot of applications out there that have started life with a Jet/ACE back end that really ought to be upsized to a server back end. Oracle doesn't seem to me like a very good candidate for that unless the upsizing is being done within an organization that is already an Oracle shop. SQL Server Express seems to me like the much more natural upgrade path for such an app (though it has a 4GB limit on the total size of the database, so you're not gaining all that much over Jet/ACE's 2GB limit), and one could certain consider open-source databases like MySQL and PostgreSQL that are entirely cost-free in terms of licensing.

The little old lady doesn't need a Mack truck to drive to church every Sunday morning. Likewise, the farmer shipping potatoes cross country would be wise to use something other than a Toyota Yaris. But the Yaris may be perfect for the little old lady, and the Mack truck exactly right for the farmer shipping his potatoes.

It's all a matter of picking the right tools for the job at hand.

Answer 6

A quick summary:

Access is cheap, simplistic, lightweight, but inflexible and historically buggy. Oracle is expensive, heavy, has no front end but sophisticated and well tested.

Answer 7

A.) Oracle to Access is like Unix to Windows 95. B.) Thousands of Dollars C.) Access has a database engine, and application front-end, hooks into other Office Apps and that paperclip thingy. Oracle has a command prompt and a blinking cursor with a big ass database behind it that can process more data than God and/or Larry Ellison.

Answer 8

I can't beleive no one has said it yet....

About $400,000.

Answer 9

Turn the question around: what do Access and Oracle have in common?

1. They both store data logically as rows, columns and tables (so-called 'relational' but not truly relational).
2. Erm...
3. That's it!

The Access database engine is a nifty file sharing system with a SQL-style data gateway; not truly SQL because, although some of its ANSI-92 Query Mode resembles the SQL-92 Standard, other areas are nowhere near compliant.**

Oracle is an industrial-strength server database management system with a high degree of compliance with the SQL Standards plus extensions (tiggers, procedures, etc).

---

** The SQL Server team wanted to make Jet SQL-92 compliant for the Jet 4.0 release in the late 1990s but were overruled by the Windows team because doing so would have broken internal Microsoft systems that had been coded on assumptions e.g. the quirky way that OUTER JOINs work where the ON and WHERE clauses are not distinguished between. It was later made Microsoft 'law' that code written against one version of Jet would be guaranteed to run against all future versions of Jet. I assume this is why from Access2007 the engine is no longer Jet but now ACE: the Access team couldn't develop Jet because of the 'law' so they started anew with their private branch of the engine code. So don't go calling ACE 'Jet', it is decidedly not Jet for very good reason! That said, most (all?) of the changes to the engine for ACE 2007 was for SharePoint integration (multivalued types, attachment type) and not qualms about ditching functionality they did not need (replication, user level security). So there's another difference: whereas Oracle arguably develop products for end users, Microsoft change (or prevent change) to the Access data engine for their own internal purposes then peddle it on (or force their decisions upon) MS Office Access users ;)