Access 2000 Frontend, Oracle Backend

[ponderdj]

Hello,

Our company currently uses an Access DB that we're looking to transition to Oracle. We've found Access to be great in handling our problems, much more so than people give it credit for. Our problem, however, is that we have about 30 users at two different locations that are hundreds of miles apart. The network demand that Access places just seems to be too much and we're looking to move to an Oracle backend. We want to keep the Access frontend and use ODBC so that we can keep all of the forms/reports/etc. that we've spent thousands of man hours creating. My questions are as follows. Will moving to an Oracle Backend greatly improve the performance of our DB? Will ODBC and an Access frontend continue to place large demands on the network and negate the Oracle performance gains? Will existing queries need to be changed to pass through queries to avoid client side processing? What about forms that use recordsets? Anyone with experience in the area that has anything to add would be greatly appreciated.

Thanks,
David

 

[johnwm]

1. Make sure that all your old queries are re-written as true Oracle queries
2. Use stored procedures wherever possible
3. Only pull the data you need

In general Access will do everything on the local machine, so will pull a whole table, even if your query only produces one record, so you will need to review your whole process to optimise performance. There are a number of Oracle tutorials to get you started (google). You should also try the FAQs in the Oracle forum appropriate to youe version.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[ponderdj]

After doing some research on the subject, it appears that the major performance gains that Oracle provides will only be realized after ensuring that the Microsoft Jet DB engine doesn't handle any of the data requests. There is a great Oracle paper (chapter 5) on the subject at:

http://download-west.oracle.com/docs/cd/B10501_01/win.920/a97262.pdf

Will any performance gains occur without changing everything to pass-through queries and removing VBA references to CurrentDB(), etc?

 

[svanels]

Looks like your company has out grown its shoes. Just like we did when we were younger, it would a little difficult to put on the sames shoes used at the age of ten.

The desktop systems (access, db, paradox etc..) are very different from the Client-Server boys.

http://www.sei.cmu.edu/str/descriptions/clientserver_body.html

Change is always a difficult process, but if it is done properly, it would be no headache.

One advantage of Oracle:

It is difficult to access, thus putting maintenance on the ICT plate. It would be "impossible" for somebody who read the book "Learn Oracle in Two Days", walk in from the streets, and by accident screw up all the data of the company. The same thing I wouldn't say about M$ Access.

Steven

 

[ceh4702]

It is a bit of a leap to go from Access to Oracle. You may want to carefully consider your alternatives. Using Microsoft SQL SERVER can dramatically increase prices. So you really need to research this carefully. We even do some reports with an interface that can bring reports directly down to Microsoft Excel (With some Limitations). There are also probably some JAVA based solutions or solutions you can put into a strictly Web based interface.

It is probably possible to use some other interface or to temporarily use Access till you decide. Most of the companies that sell products can come out and demonstrate what they have. They all require quite a bit of customization to get to work so be careful of slick salesman in sheeps clothing. It might be there are consultants in your area that specialize in this type of redesign.

If you do not like my post feel free to point out your opinion or my errors.

 

[ponderdj]

The cost aspect really isn't an issue. We have other DBs running on Oracle 9i, and have servers already setup. This DB was built with a small targeted scope of users and because of Access' RAD abilities and small user base was built on Access.
It's grown and we want to migrate it using Oracle Migration Workbench. It's pretty much been decided to go with an Access frontend/Oracle backend. We want to keep the frontend because of all the time that's been put into it.

The questions I have are really about making the Access frontend perform as a dumb client. We don't want it trying to process any data, only display it.

Questions are:
1. Will moving to an Oracle Backend greatly improve the performance of our DB? (We are guessing it will, but only after we've changed the frontend to keep Access' Jet DB engine out of the picture and make access behave as a dumb client.)

2. Will ODBC and an Access frontend continue to place large demands on the network and negate the Oracle performance gains? (If we make no frontend changes.)

3. Will existing queries need to be changed to pass through queries to avoid client side processing?

4. What about forms that use recordsets?

5. Any other useful tips?

Thanks,
David