[Help] Oracle 8 and Access with ODBC

Help me please !!

I have some people in my company that want to develop a software that runs actually on Access in Oracle. The problem is that they know Access, not Oracle. They just want to link the Oracle tables in Access, and develop clioent software with Access.

So they will develop their bull****, and I see the problems coming :

- Data modifications without COMMIT or ROLLBACK in jobs, will cause data inconsistency
- Performance problems, because they will use the client PC and Access to make loops in the tables and modify data... rather than using stored procedures, etc..
- Timeout problems with ODBC driver ?
- Cannot use PL/SQL within Access
- SQL standard differs from Oracle to Access ?

I'm not sure with all the points, be I have a meeting this afternoon to convince them that's not a good idea. Please con you confirm my idea ? Do you see any upcoming problems with this solution ??

I don't see it as a problem so long as they are aware of the issues.

My current company mostly does web stuff, but there are also some Oracle Forms and Access projects. Invariable the access stuff uses linked tables to Oracle. We have no problems with them. If some 'extra' functionality is needed, like prodecure calls, they use ADO to call them from within Access. Remember, can be considered a superset of VB these days. As long as your access developers know their stuff it really isn't a problem.

I'm not advocating this approach, but if they want to do it and accept the limitations, why not?? The biggest problem we've found is security.

I sense a storm of outrage coming my way from die-hard microsoft haters :)

To be more specific:

- Data modifications without COMMIT or ROLLBACK in jobs, will cause data inconsistency

Transactions can be controlled in Access as with VB

- Performance problems, because they will use the client PC and Access to make loops in the tables and modify data... rather than using stored procedures, etc..

Like any client server technology this can be true. Even in Oracle Forms. If they want to use DB packages they can using ADO.

- Timeout problems with ODBC driver ?

Not noticed this on our apps.

- Cannot use PL/SQL within Access

True, but you can call stored packages/procedures/functions

- SQL standard differs from Oracle to Access ?

Access uses ANSI joins rather than old-style WHERE clause joins. In this respect it is more up to date than Oracle8i. Oracle9i has introduced ANSI joins. You would do well to get used to them. I'm sure in the next few years the old WHERE clause join will be a legacy issue :)

I've not seen any issues except when Oracle guys are looking at ANSI joins and don't understand them.

Suggestions:

- Use views alot to hide complexities from Access.
- Make sure developers use DB procs/funcs/packages when complex coding is needed.
- Chill out. it's not as bad as it sounds.

Good luck!

Quote:

---

Chill out. it's not as bad as it sounds.

---

If I did not know that you are even 9i certified I would think you are one of those Microsoft Access fans.

It's just my opinion, but it is as bad as it sounds.

I'm an absolute Access Junkie who only did the OCP so people wouldn't think I'm sad :)

Not really!!!! Honest!

I've used most development and DB products at one time or another. All seem to have their strengths and weaknesses. I really don't give a crap anymore. As with all Microsoft products Access is easy to use, that's why people like it. I'm not saying you get a good result, but near novices can knock up something useful in no time. More than can be said for Java!

Please don't spam me for slagging off Java! I use it! It's my favourite programming language! Aaarrggghhhh! What have I done?????

Alot of companies, including Oracle, should take a leaf out of Microsoft's book and put more emphasis on ease of use. Sure, Oracle9i is easier than Oracle8i, but it's no SQL Server!!

Oh no. Now I've slagged off Oracle on an Oracle biased site. Someone help me!!!!!!

:)

I think MS Access is a great quick-and dirty development tool especially when using Oracle as a backend. As Tim specified, you can call stored procedures and packages via ADO and do all the normal Client/Server type things with it. I was with a group that developed a MS Access app (hooked to Oracle) that was used by 120 CONCURRENT users with very little problems at all. Of course, we had a pretty good idea of what we were doing with Access and Oracle, but still, it can be done.

In fact, my team of 3 developers got a pilot app up in 2 months while the 8 "Java Guys" in the same company were in their 9th month of a 2 year project!




[Edited by marist89 on 12-14-2001 at 10:34 AM]

thanks for answers !!

marist89,

"Duck and Cover" mate!

It's a Microsoft-hatin' world out there :)

Cheers

Tim....

My philosophy is to use the best tool for the job. If the job is to quickly get some forms up with unsophisticated behaviour, MS Access is the tool. If you need more control over the environment, something like VB or Java is warrented. Need fast code, C/C++ is the answer.

I woudn't use a hammer to cut a piece of wood http://www.contrabandent.com/pez//co...dday/laugh.gif ...