Best Answer

There are many differences between Oracle and Access. First, you need to know that Oracle and Access are both relational database engines. Oracle is sold by Oracle Corporation and Access is sold by Microsoft. The biggest differences between the two are the size and number of users each supports. Oracle is a scalable enterprise database engine that supports from one to many (tens to hundreds of thousands) of simultaneous users. Access on the other hand is most commonly used as a single-user database but since the release of Access 97, can be augmented with Visual Basic code and can easily support 100+ simultaneous users (Top limit of 200 users). Oracle can support very large databases (Tens of Terabytes in size or larger), while Access is designed for much smaller databases in the 1 to 2 gigabyte size range. Oracle has a wide range of support features that can be used to monitor and tune database performance - Access is much more limited in its performance tuning capabilities. Microsoft's SQL Server rdbms is a direct competitor with Oracle and has a great many more similarities with Oracle. There are numerous incompatibilities between Access/Jet SQL and Oracle SQL, including, but not limited to the following:

1. The date delimiter is "#" in Access and " ' " (single quote) in Oracle and SqlServer.

2. In CHAR Datatype, Oracle returns trailing spaces. Access does not return trailing spaces in String data type. In Oracle, use VarChar2 Datatype.

3. Jet allows use of the "Join" keyword. Oracle uses "(+)" following the joined field(s). Join is not a recognized word in Oracle.

4. IIF() is allowed in Jet but in Oracle, you must use the DECODE function. See Oracle documentation.

5. The following functions in Jet must be translated in Oracle.

Mid(fld, 2, 2) - Substr(fld, 2, 2)

Left(2) - Substr(fld, 1, 2)

Right(n) - Substr( fld, Instr(...),n)

Len() - LENGTH()

Year(Fld) = 1997 - to_char(Fld, 'yyyy') = '1997'

Month(Fld) = 6 - to_char(fld, 'mm') = '06'

Trim(Fld) - LTrim() or RTrim()

6. Generally, you need to use "to_date('01/23/1978', 'mm/dd/yyyy')" to format a date for comparison in Oracle SQL whereas in Jet Sql you could simply use "#01/23/1978#".

7. "RENAME" is a reserved word in Oracle and is therefore an illegal field or table name, but not in Jet.

8. If a memory variable is used in a SQL statement, and the variable is a string with trailing spaces, Jet will trim the value before comparing to the database field. Oracle will not! Therefore, the following will work in Jet but not Oracle, assuming the Oracle field in VARCHAR[2].

Sql = " ... where fldname = 'abc ' ...."

9. Oracle SQL will not find uppercase data in the database if the value in the SQL statement is lowercase. Assume that the LastName field contains the value 'SMITH' in the Oracle table Policy. Access will make a find on the following SQL and Oracle will not (Like character would be "*" for Access.

select * from Policy where LastName Like 'smith%'

10. Conversion of Memo (Jet) fields to Oracle can be a problem. The only data type in Oracle that is analogous to the Memo in Jet is the Long. Although it can hold up to 2gb of string data, you cannot append more than approximately 5,000 characters to the existing Long field because you exceed the length of the maximum string length in an Oracle SQL Statement.

Additionally, you cannot use such expressions, as the following, usable in Jet, in Oracle. They result in the error, "Invalid use of Long Datatype."

select distinct LONGFLD


It seems impossible to use a query to determine rows where there is data in the Long field since a Long field that has '' in it is not Null.

Oracle 8 has Append and Get Chunk commands, but the documentation in the Oracle 7.2 Book does not mention these functions, nor are there any examples of how to update a Long field that exceeds 5,000+ characters.

11. Use of the following sql can be extremely slow in Oracle.

select * from table where policy in

(select policy2 from table2 where .....)

Try using the Oracle Minus operator instead. It uses indexes where the "in (select...)" does not. See the Oracle documentation for usage of the Minus.

12. It is normal, when using Jet, to fill the recordsource property of a data control at design time, even if you are going to fill it with a specific SQL sometime after Form_Load. This is done in order to use the Grid Designer when a data control is bound to a Grid or Combo, etc. However, when the form loads, all such data controls are refreshed. If you have the table name as the data control recordsource, which would be normal for designer use, and assuming that dbSQLPassThrough is being used, the entire table is returned to the client machine and refreshed into the grid, even if the grid is not visible, i.e., on a different tab. In Jet, this will not be the case if "UseExactRow" property is not set and therefore will not take an inordinate amount of time, but in Oracle it could be disastorous!

An easy way to deal with this is to set the recordsource to "" in the Form_Load event, especially if you are connected to Oracle. Later on, your code will set the recordsource to the specific SQL as required. Note that this must be done in the Form_Load because the Grid will be refreshed by the time the Activate Event is fired.

13. Be aware that, when using dbSQLPassThrough, the entire recordset, specified by the SQL, is returned to the client machine, and is therefore readonly. At this point the recordset belongs to the client, and Oracle is out of the picture. In praticality, there is no difference between a Snapshot and Dynaset in passthrough mode.

14. Use of apostrophes (') in names and abbreviations are a problem in SQL. In Access, it may be circumvented by use of the double quote (") to encapsulate string data in the SQL statement instead of the apostrophe, thus allowing the use of the (') in the string. In Oracle, you can use two single quotes ('') to denote an apostrophe. The string below is valid in Oracle Sql.

... Set Lastname = 'O''Toole',...

15. In Access, an empty string ("") is not Null. Oracle's default parameter for Null is set to '' (double single quotes or empty string). In other words, Oracle, by default, treats an empty string field as Null. Therefore, the following code which works in Access, does not work in Oracle.

...Where RTrim(Fld) <> ''.....

In Oracle, change the SQL to:

... Where Fld is not null....

Also, the following sql will not work if a field is Null.

... Where THSTATE <> 'Z'...

Even though the field is null, and therefore <> 'Z', the row containing the Null field will not be returned. In order to get the row, you must enhance the SQL as:

...where (THSTATE <> 'Z' or THSTATE is Null)...


16. When using dbSQLPassThrough with DAO, the RecordsAffected Property will always return -1, and there is apparently no way to determine whether any records were affected by an action sql or not.

17. When printing an error message from ODBC, if you print Error, as in the errors object, you will print "ODBC Call Failed", which is useless. Instead, print Errors(0).Description (or both) and you will get the true error description returned from the ODBC data source.

18. If you are having trouble with a bound data control returning less than the correct number of row, i.e., returns 100 rows when it should have returned 200 or more, set the DefaultCursorType to 1 (ODBC Cursor). For some reason this is not always required, but it will solve the problem of truncated recordsets in a data control connected to Oracle.

19. Access allows an Order By clause at the end of a query that uses a Union, but if you try this in Oracle, it will not work because Oracle ignores the column names in the result set from the Union. Not to worry! You can Order By column positions from the select statement. In other words, if Policy is returning in column 3 of the result set, and you want to order by Policy, then use "Order By 3..."

User Avatar

Wiki User

โˆ™ 2008-05-23 02:05:28
This answer is:
User Avatar

Add your answer:

Earn +20 pts
Q: What are the differences between Microsoft Access and Oracle?
Write your answer...
Related questions

What are four database programs?

Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.Oracle, MySQL, Microsoft Access and Filemaker Pro.

What are the differences between Microsoft Excel and Oracle Database?

MS excel is a spreadsheet. Oracle is a database system.

What are the differences between Access and Oracle?

The difference between Access and Oracle is Access has minimum security features and you can create a small to medium sized database. Oracle has NY security, which allows you to determine who can or can not get access to your data. Access you can't add too many users without it getting slow, and Oracle allows you to add thousands if you want to.

What are some advantages of Oracle over Microsoft Access?


What are the major differences between oracle 10g and oracle 11g?


What is the difference between Oracle and Microsoft Access?

Oracle is like a Ferrari. MS Access is like a Saturn. Oracle is intricate but also very powerful and well written. MS Access is a database program that general users can use--more user friendly and easier to learn. However, it is not designed for large databases.

What is the difference between oracle and MS access?

Oracle is multi-user where MS-Access is not. Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle. We can't create sequence in Microsoft Access.We craete sequence in Oracle. Oracle database is more secured than MS-Access. Oracle can handle a database having million records very efficiently, but MS-Access has no such quality like Oracle. oracle is client server technology based while Ms-Access is not. Oracle follow 11 rule of E.F CODD and access follow 7 rule, oracle give better query performance in comparison to access, oracle perform high security feature&Acirc; like system manager, oracle can store unlimited data in access limit.

What is an example of a computerized database?

MySQL, Oracle, Microsoft Access, Borland Paradox, etc.

How do I create a form which will then upload to a database that I can access in Microsoft Access?

Have your form's input insert into a database (SQL, Oracle, etc) then access that database through Access's ODBC connection.

What is the deffrence between sqlserver and sqlplus?

SQL Server is Microsoft's relational database product. SQL*Plus is the command line tool to access Oracle's database product.

Is there accounting software that has ready-made compatibility with Oracle databases?

Microsoft Excel and Access offer the ability to interface with a Oracle database. This is provided through the Office Suite.

Is Excel a true database system like Access and Oracle?

No. Microsoft Excel is an accounting worksheet application, not a database. Microsoft Access is a small, simple, but capable, individual-user database application, and Oracle is among the largest of Enterprise-class database applications, and can accommodate the needs of gigantic corporations.

Examples for dbms and rdbms?

My sql, Microsoft sql , Microsoft Access, dBase, Filemaker pro are the examples for dbms and oracle and sql server are the examples for rdbms

What is the difference between Microsoft Excel and Oracle?

Excel is a spreadsheet program. Oracle is for databases. Excel has some databasing capabilities, but it is not its main purpose.

What are the differences between MySQL and Oracle?

The Oracle link, below, gives an objective comparison between MySQL and Oracle (n.b., a great deal of the lengthy page contains computer terminology that the average layman may not understand). what is the difference between oracle and MySQL?

What are three examples of a database?

Microsoft Access, MSSQl, MYSQl, ORACLE etc. are the examples of widely used Database Systems.

In what situation would a company choose to use a Microsoft Access database over an Oracle database?

To save lots of money.

What is the difference between oracle and visual basic which one is the best suited for backup database system?

Oracle is a RDBMS software, means, Oracle holds the database (data created/updated using application). Visual basic is tool to develop applications &amp; this is from Microsoft. You can write programs/application to access the data from the RDBMS like Oracle, Microsoft SQL Server, etc. Since the functionality differs (i.e. Oracle = RDBMS &amp; Visual basic =Application development Tool) for the two software applications, "which one is the best" can not be answered.

What are the DBMS available in market now?

Available DBMS are DB2, Oracle, Sybase, and Microsoft SQLServer. There are also Microsoft Access, mySQL, FileMaker. Hyperion, hSQL, Firebird, Postgres, and more.

What are the differences between Oracle and SQL?

Oracle is an RDBMS aka Relational Database Management System and SQL (Structured Query Language) is a programming language.

What are two examples of Database software?

Database Softwares include Oracle, DB2, Microsoft Access, Microsoft Report Server, PostgreReport, MyReport, Reportite, FileMaker and Sybase Adaptive Server Enterprise.

What are some examples of database?

There are a variety of examples of databases. These include Microsoft Access, SQL Server, Oracle, SalesForce, SAS, as well as VAX.

What is the name of software application to create a database?

Database Software include PostgreReport, Oracle, , Microsoft Report Server, MyReport, Reportite, FileMaker DB2, Microsoft Access and Sybase Adaptive Server Enterprise.

What is database package and give example?

A database package is a computer program that allows you to create and manage databases. Examples include Oracle and Microsoft Access.

What are 3 benefits of Microsoft Access?

Relational tables if stored locally (vs Excel). Can use ODBC interface to access SQL/Oracle/etc databases. Idiot friendly with drag and drop joins.

Study guides

Create a Study Guide