Access vs. Oracle

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

 

[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