Need Advice - Can we use Oracle in this situation?

[DataChick]

I am an MS Access developer working for a very large corporation. I have designed a database in Access to manage a process that has been thus far managed (poorly) in Excel and an eRoom.

At the time that they gave me the specs on the project, they said it would have no more than 50 users - which we have managed in an Access system before. But now, it is looking like there will be WAY more users than that.

The front end is saved to each users hard drive and links to the data tables so I am not concerned about the GUI but I am concerned about the data tables (that they are going to get too many hits and possibly crash).

I think that Oracle tables to hold the data would be the best solution BUT this is all being done in "shadow" IT and we don't want to get the IT department involved. So, I'm doing some research to see if we can create the Oracle backend ourselves.

Right now, the Access backend is just sitting on a shared (network) drive. Can we do that with an Oracle backend or do we have to have a server?

Any other suggestions? If the answer is we have to get IT involved to fully manage this project, then I'll advise my management of that, but I'm searching for alternative solutions.

Thank you in advance for any assistance you can provide.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jimirvine]

Short answer? You are going to need IT to maintain this.

 

[Dagon]

I tend to agree. You would need to install it on a server and you would need all the client software and ODBC software installed on the users' PCs. There might also be licensing implications. Does the company already have a license for Oracle ? If so, how many users/servers etc does it cover ? Would that need to be increased ? Also, I don't think you'd make yourself too popular with the IT department by trying to do this behind their backs.

 

[DataChick]

Maintain the backend data tables? Or are you saying the whole process...because that is not an option.

We have attempted to have IT develop a system completely within Oracle and it failed miserably. The Access front end works great and everyone loves it - which is why it's now looking like we are going to have so many more users because they want to expand it to other departments. I just need to manage the data tables because I think that it's going to be too much for it all to be in one Access database.

If you could give me a little more detail...maybe a medium answer...that would be great. I can't just go to my management and say "give it to IT" without understanding why.



"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jimirvine]

Does Dagon's response more fully answer your question?

 

[KenCunningham]

Much as I am dyed in the wool Oracle, you might find it easier to use SQL Server as the back-end in this instance.

Some days are diamonds, some days are rocks - make sure most are the former.

 

[Dagon]

Traitor !

 

[KenCunningham]

*Hangs head in shame*

Some days are diamonds, some days are rocks - make sure most are the former.

 

[Turkbear]

Hi,
To handle large numbers of Users concurrently accessing the data ( especially if updating, deleting, etc. ) a 'real' database management system is really the only way to go.
A file system based tool like Access is not designed to handle that kind of activity, so your options are mainly Oracle or SqlServer - What was the problem with ITs Oracle system?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

I'm an Oracle DBA and have seen the frustration of many users when the IT Department does not provide a fast or adequate solution for their needs.

Also I have been in the position of having to "take over" these "rogue" databases that suddenly pop up.

Even though I favor the Oracle alternative, I agree with KenCunningham on the SQL Server route, easier to implement and compatible with your access front-end.

Now, on the other hand, if the data does not need to be consolidated, perhaps you could create an Access database per department?.

Good Luck, try to avoid conflict w/IT.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

I've seen an IT department get involved and use overblown procedures relevant to multi-million pound projects on a tiny in-house development. Needless to say, it failed to deliver, and was a complete waste of everyone's time. The original Access solution was forced to continue in use for ages.

If you have many users and want a fast application, then you really do need a multi-user system such as Oracle or SQL Server.

However, Oracle has APEX which has a utility for converting Access applications to Oracle. It might be possible to get a schema on an existing unimportant database, and work in that, using APEX. Then you wouldn't need to buy any new hardware, and the licencing issues would be taken care of by existing agreements. However, I confess, if t'was me, I'd want a dedicated database to play with. Since adding a db doesn't affect licencing (it's usually cpu or user based) then I can't see what IT are moaning about.

Regards

T

 

[DataChick]

Thank you for all of the feedback.

First off, we have lots of data that is managed through Oracle and we use Access a for reporting and analyzing the data stored in Oracle (so user licenses and ODBC connecting is not an issue).

The fact that it needs to be installed on a server makes it a solution that my management is going to be very reluctant to pursue since it gets IT involved.

LKBrwnDBA - You basically nailed down the solution my management is leaning towards - managing the data on a department by department basis and only pulling it together for reporting (which will only be done by a select few people and time contraints are not an issue).

To answer Turkbear's question...the company bought an Oracle based system and was promised that, with a few tweaks, it would do everything that they needed. They spent a year "tweaking" the system, migrated ONE department to it and it was NOT user friendly, full of bugs and didn't meet the basic needs as the developers had claimed so they had to take the next year migrating that ONE department back. It was a HUGE fiasco and has completely soured the companies opinion of Oracle (as a front end). IT recommended the system and did the "tweaking" and cost the company a LOT of money.

Personally, I agree that Oracle or SQL Server (which we rarely use) would be much better for the back end - hence why I'm here trying to find a solution that will appeal to my management - but IT does not have anyone on staff even remotely familiar with Access and would start pushing for this to be in a mainframe or Oracle (for the front end as well) so they can take it over...and that's exactly what we don't want or need.

So far, the only data issues we are seeing is in performance but management considers them acceptable compared to the time the process used to take...my only concern is data loss and corruption (which we have not seen yet). My concerns may be unfounded...we may be fine...but I was just trying to get all of my options to make a more educated recommendation.

Thank you all for your input.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[HarleyQuinn]

Surely IT only need to be involved in the initial set up, backing up of data (as I would hope they already do with the other DB's) and general housekeeping/data management (which again they should be doing already for the other DB's).

There will be a fair bit of initial work for them but after that the owness falls on you to make the access app work with the Oracle version of the DB rather than the current one. As long as the work is spec'd and agreed by both parties I also can't see what IT would have to moan about.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Beilstwh]

I suspect that the oracle project was messed up by incompetent programming and design work in the IT department. That being said, a schema setup in an existing oracle database and some ODBC links to migrate the local access tables into oracle would probably be all you need and you would have no problems with concurrent users.

Bill
Lead Application Developer
New York State, USA

 

[ChrisHunt]

It's entirely possible to have the tables and any batch processing living on an oracle database, and to use Access as the front-end. I set up a system to do that once and it worked very well.

I think you're going to have to get the IT department involved though. Frankly, your company is doomed if you have an IT department but dare not get them involved in (what sounds like) important IT systems.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd