using Oracle as backend for Access

[Djblois]

I posted this question over at VBAexpress yesterday (which I should have posted here first because their Access forums is not as active as here) and was wondering if anyone had an answer:

I have written a few solutions in Access (using access 2007). Now I going to create a few new ones for my company. We use Oracle for our main database and was wondering what would be the advantages and disadvantages of using Oracle as my backend while Access is my front end?

Also, should I create the queries in Access or in Oracle?

finally, is there anything I need to do differentely?

Here is the link:

http://vbaexpress.com/forum/showthread.php?t=4602

 

[Pat Hartman]

I have created several Access applications that are a front for Oracle tables. It is really no different from using SQL Server or any other RDBMS. You just need to use good client/server techniques to minimize the data you request from the server. I.e. no forms bound to tables or queries without selection criteria. You need to include "dbSeeChanges" for any DAO/ADO code that can update a table with an identity column.

I would start with linked tables and "Access" queries. You may find that some queries are slow. You can try making views on the server if certain tables are always used together. When you link to the view, you will be asked to specify a unique identifier. Be sure to do this correctly or you could end up corrupting the Oracle tables when you try an update.

If you have complicated, long running batch processes such as generating invoices, you might waht to create them as stored procedures that run on the server and can be scheduled to run during off hours.

 

[Djblois]

Pat thank you for the reply.

A few clarifications, when I asked what benefit I meant what benefit would I receive by using Oracle as the backend instead of an Access (.mdb or .accde) backend. Is it worth the hassle?

This new database I am going to be creating is also going to have attachments, therefore it will get quite large.

Also, do you know of a good resource either a book or a website that will help me with the Oracle and Access integration?

 

[jdraw]

Do you have a DBA area? Have you discussed your use of Oracle vs Access with them?
Does your company's main business run on Oracle (finance, HR...)?

Perhaps you could tell us about your proposed use of database. Will it be transaction processing, multiuser, multisite...?

You haven't told us much about your needs.

 

[mdlueck]

I have written a few solutions in Access (using access 2007). Now I going to create a few new ones for my company. We use Oracle for our main database and was wondering what would be the advantages and disadvantages of using Oracle as my backend while Access is my front end?

Also, should I create the queries in Access or in Oracle?

finally, is there anything I need to do differentely?

I am headed out for the day, just saw your post, so pausing to share this link since you are considering Access in a Client/Server environment:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[Pat Hartman]

I seriously doubt that you will find a single book that talks about Oracle vs Access. You will find some that talk about SQL Server vs Access. They really don't mean "Access" anyway, they really mean Jet/ACE. Way too much confusion reigns regarding what "Access" even is.

My choice as to whether to use ACE (I don't use Jet anymore) or a RDBMS has to do with how many users and how much data. The more concurrent users, and the larger the table size, the more likely I will choose a RDBMS. In ALL cases, ANY database I develop uses proper client/server methods and so can be "upsized" with no more than an hour or two to transfer all the tables and test all the DAO to make sure it has the optional arguments that the RDBMS needs.

As to which RDBMS, it is up to the client. What ever is standard in his shop, I use. Oracle, DB2, SQL Server, Sybase, Pervasive, etc. You name it, I've used it

 

[Djblois]

Pat, the amount of users at one time will be anywhere from 5-30. I am more concerned with the amount of data. What I am creating is a Customer Complaint Database that people can upload attachments and keep a log of correspondence with the customer from emails to word documents to excel, etc..

 

[Pat Hartman]

If you use linked tables and "Access" queries, the BE is pretty transparent. There is no upsizing wizard for Oracle, either you or your DBA will need to create the database and tables manually. I generally print out my ACE table descriptions and leave it to the DBA to create the Oracle tables. Also, be aware, once you have to deal with a DBA, you are no longer the master of your universe. You will do "it" the way the DBA wants. If he wants a certain naming style, that is what you will have to use. I prefer CamelCase where the words are strung together and separated by CAPS at the beginning of each word. Some like the_underscore. No CAPS, underscore as separator. I have even run into DBA's that like abbreviations and keep lists that you have to choose from and they dictate the order of name parts so you might end up with something like CUSTNO or PRDDELDT (brings me back to the 60's when we only had 8 characters to work with).

So, my advice is, if you even suspect you might at some time in the future want to use Oracle, have a heart-to-heart with the DBA regarding naming standards or you are in for a world of hurt later when he insists you rename all your columns.