[Help] Oracle 8 and Access with ODBC
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: [Help] Oracle 8 and Access with ODBC

  1. #1
    Join Date
    Oct 2001
    Location
    France
    Posts
    7

    Exclamation

    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 ??

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.


  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!!!!!!


    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Oct 2001
    Location
    France
    Posts
    7
    thanks for answers !!

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    marist89,

    "Duck and Cover" mate!

    It's a Microsoft-hatin' world out there

    Cheers

    Tim....
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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 ...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width