Access vs. Oracle (1 Viewer)

[Joshann]

What are the major differences between Microsoft Access and Oracle databases?

I have an Access database that I designed and have used for many years. In addition to the tables and queries, it has numerous forms and reports, and a lot of VBA code. It can also generate emails and Microsoft Word documents. We only have three users, and the back end is only 19mb. The agency I work for does not support Access. The large databases our agency uses are in Oracle. I have been asked if it would be better to move our Access database to Oracle. Of course, I would rather not, since I know nothing about Oracle.

That being said, I don't know enough about Oracle to argue intelligently about why Oracle is not as good as Access for our needs.

Could anyone explain the major differences between Access and Oracle to me?

 

[theDBguy]

Hi. I'll give it a little bit of a try since I am more familiar with SQL Server than Oracle but Oracle is similar to SQL Server, so here it goes. If Oracle is anything like SQL Server, then you might need a separate Front End to access the data. If you can't use Access, it would mean using a custom desktop app created using C, C++, or Java. Or, you could use a web page to access the data. If you can still use Access, then you can move the data to Oracle and continue to use your Access front end. For the size of your data and the number of users, moving the data to Oracle is not really necessary, and will probably cause a little bit of disruption for a while during the migration and immediately after. Hope that helps...

 

[plog]

A lot of my jobs have been because of your situation. And if I am reading your situation correctly, your organization's concern isn't technology, but continuation of support:

The agency I work for does not support Access.

You are the sole person in your organization that knows how to fix/maintain/extend this system. That's just bad management, so they are looking to find a way to make this fixable/maintainable/extendable by others. The others they have know Oracle so that's they way they want to go and it makes sense.

When they hire a guy who knows Oracle he can work on all of their projects. If you leave they need to hire a guy who knows Access and will work on this just 1 project.

I don't think this is an Access/Oracle discussion your company is having. It's a contiunation of support discussion.

 

[kevlray]

Our organization does officially support Access (i.e., if you create or use an Access database, you may or may not get help from the organization). That being said. The organization does not stop staff from creating/using Access databases. Many times I have been asked to create/modify/support staff using Access databases. Sometimes I can help, sometimes I cannot (especially with vendor supplied Access databases). So it may be the case in your organization. If you have a problem with your Access database, you are own your own.

 

[Joshann]

A lot of my jobs have been because of your situation. And if I am reading your situation correctly, your organization's concern isn't technology, but continuation of support:



You are the sole person in your organization that knows how to fix/maintain/extend this system. That's just bad management, so they are looking to find a way to make this fixable/maintainable/extendable by others. The others they have know Oracle so that's they way they want to go and it makes sense.

When they hire a guy who knows Oracle he can work on all of their projects. If you leave they need to hire a guy who knows Access and will work on this just 1 project.

I don't think this is an Access/Oracle discussion your company is having. It's a contiunation of support discussion.

Thank you, but I am aware of why I am being asked this by my organization. It is too long to explain here, but I still want to know the differences between Access and Oracle.

 

[Joshann]

Hi. I'll give it a little bit of a try since I am more familiar with SQL Server than Oracle but Oracle is similar to SQL Server, so here it goes. If Oracle is anything like SQL Server, then you might need a separate Front End to access the data. If you can't use Access, it would mean using a custom desktop app created using C, C++, or Java. Or, you could use a web page to access the data. If you can still use Access, then you can move the data to Oracle and continue to use your Access front end. For the size of your data and the number of users, moving the data to Oracle is not really necessary, and will probably cause a little bit of disruption for a while during the migration and immediately after. Hope that helps...

Thank you very much! This is what I was wanting to know. So as far as you know, Oracle is pretty much just the back end?

 

[theDBguy]

Thank you very much! This is what I was wanting to know. So as far as you know, Oracle is pretty much just the back end?

That is my impression. At least, I know SQL Server is like that, and the two are supposed to be similar. The same with MySQL, DB2, others.

 

[Pat Hartman]

Access vs Oracle is a non sequitur. Access is an application development tool. Oracle is a database engine. They do completely different things. Access is not a database engine (you are probably using Jet or ACE to hold your data. Both are separate products and frequently confused with Access since Access comes bundled with ACE and Windows includes Jet plus Access stores its objects in an .mdb (Jet) or an .accdb (ACE) but it's data can be stored in ANY ODBC compliant RDBMS) and Oracle doesn't create applications although Oracle does sell an application creation tool called Oracle Forms.

If you want to convert your Access application to Oracle Forms, it will be a complete rewrite. i seriously doubt you would ever find a conversion tool. If you want to convert your data (Jet/ACE) to Oracle, that is pretty easy. You can do it manually or purchase a tool that can do it for a couple of hundred dollars. I have one as part of SQL Examiner Suite. Oracle may also offer a tool itself similar to the one offered by SQL Server.

Converting your BE to Oracle is overkill at this point in time and would not solve your company's problem of support. The Oracle DBA would NEVER be able to support your application. He could only manage the tables.

I personally have create Access applications with Jet/ACE, SQL Server, Oracle, DB2, Progressive, and Sybase BE's that I can recall off hand. There may even be other more obscure RDBMS'. Essentially, Access can act as an application FE to ANY ODBC compliant RDBMS.

Very few medium or large organizations actually support "Access" applications. I have three smallish (under 200 employees) clients whose operations run exclusively (except for accounting) using Access applications. All use Access FE's and SQL Server BE's. The reason for the SQL Server BE's is the number of concurrent users is larger than what is recommended for Jet/ACE. For larger organizations, Access is typically used to create department level applications. At some point in time, the Access application might become mission critical and rise to the level of needing to be supported by IT. IT then makes the choice of learning how to support Access or takes on the task of rewriting the app from the currently working template in some environment that they are willing to support.

For the rest of the world, we're here to help you to help yourself. You are not alone. If you want a real external backup person, your company can offer to pay a retainer to some local or remote expert who is willing to invest the time to learn the app and back you up when necessary.

 

[The_Doc_Man]

We had ORACLE at a U.S. Navy site (as well as SQL Server and native Access backends). If you can establish the ODBC connection and don't mind doing a few things by hand, you are good. Like Pat says, conversion isn't automatic.

A few data-type issues will crop up between Access's JET or ACE engines and whatever other DB you want for the backend. Dates typically need tweaking because ORACLE uses a different reference date than either Access or Windows uses. I recall a few minor issues with strings but I stress MINOR issues - like what constitutes a delimiter for feeding in records that potentially have multiple lines of text in a long text field.

Field naming is maybe marginally different because (if I recall this correctly) you can't have spaces in ORACLE object names. It's all little things. Once you have the data in ORACLE, it is no big deal to get to it.

 

[gemma-the-husky]

coincidentally, this post appeared yesterday. It's worth looking at the underlying article.

https://access-programmers.co.uk/forums/showthread.php?t=307672