Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2002
    Posts
    31

    Unhappy Unanswered: Access with Oracle database

    Access 2000 can be used as a front end (as .dap file) of SQL Server. But do you know how to
    connect Access with Oracle database? Is it possible?

  2. #2
    Join Date
    Mar 2002
    Posts
    32
    Access can be used as a front end for oracle tables, in fact I use it all the time.

    First you need the Oracle Driver.
    Then you need to have an ODBC datasource that connects to your Oracle database.
    Next just open a database, and go to the add table wizard.
    Choose the link tables option.
    When it asks you where you want to link the tables from change "Files of type" to "ODBC Databases."
    Choose your datasource, and click OK, you may be prompted for a log on.
    Select your Oracle tables from the list, and they will be linked to your Access database.

    You can also use an SQL Passthrough query to connect.
    After you have linked a table you can go to the table properties, and grab the connect string, go to the Query properties, and paste the connect string in, then paste your SQL statement in the SQL section of the query.

    If you have any more questions let me know, and I can walk you through it.

  3. #3
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Hi,

    I am linking my oracle backend DB to my ACCESS frontend DB. I can view my data in tables and views, so evething goes well so far.

    Problems occur when I try adding or updating data. The problem is that I can't seem to insert records in my oracle table if I have made my primary key of the type number. Nore can I change the value of the primary keys. I keep getting the following error message:

    ODBC--insert on a link table 'table name' failed.
    [Microsoft][ODBC driver for ORACLE][ORACLE].ORA-01722:
    invalid number (#1722)

    Now if I insert the same data using SQL*PLUS I have no problems

    What am I doing wrong?
    Any ideas?

    Thanks for helping,

    anna

  4. #4
    Join Date
    Mar 2002
    Posts
    32
    I use access mostly as a reporting tool, and don't edit records very often. When I have run an update query on oracle tables it seemed to work fine.

    Are all your updates failing?

    Does it only fail when you update the primary key?

    Does it fail on any numeric field?

    Does it only fail when the Primary key is numeric?

    Open the query in access, and go to SQL view, and make sure it is identical to the one you run using SQL Plus.

    If you are using linked tables, maybe try a pass-through query using the your SQL Plus SQL statement instead.

    Let me know how it goes

  5. #5
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Hi Ben,
    Thanks for helping me.

    I have tried everything: queries, passthrough, manually in the table,...
    Nothing works with numbers

    BUT

    I have an other existing database in ORACLE. I tried doing the same thing with this one and guess what it worked. Now I am trying to figure out what I did wrong when I created the database, tables, users,...

    Is it possible that I (the user I log on whith) need an extra privilige. I have given the user all the roles and priviliges of the sys. So I think I have got it covered.

    Also I created a table which has only varchar2 column and now I was able to add rows using ACC.

    I am a bit dazled!!

    Thanks a lot for the help so far!!
    I really appreciate it!!

    anna

  6. #6
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    I think have found it

    It seems to have something to do with the definition of the columns in my table. If I set the precision of a number column to 0 I am able to add rows using. If I don not specify this I have problems.

    Thusfar everything runs smooth

    TNX again

    anna

  7. #7
    Join Date
    Mar 2002
    Posts
    32
    No Problem, glad it worked for you

  8. #8
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Dear Ben,

    I'm working access front end, oracle back end. And I love it!!!

    But my boss, being a little bit sceptical towards access, has still to be convinced. So, I would like to ask you the following question:

    In your experience, what is performance loss when using access instead of f.e. VB as an user interface? Is there a disadventage, when using Acces, because of the storage/memory it asks?

    The reason I ak is: We are currently maintaining an Oracle DB whith an Oracle forms interface but we are thinking about moving to an VB-like environment.

    Thanks a lot,

    anna

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Anna,

    If I might respond to your Access versus VB question.

    As far as I am concerned, there is no discernable performace difference between Access and VB, when used as a front-end.

    An Access application may take longer to launch, initially, to the first screen. But we are talking about, at the most, a single second.

    Once loaded, there is no performace difference between the two products.

    Each product has it pluses and minuses.

    Access requires a larger footprint of disk space and memory.

    Access requires a greater effort to hide your program's architecture.

    Access does not handle third-party controls very well.

    Access installations are vulnerable to changes in their environment, namely upgrades to Access.

    On the plus side, and we are getting in to the great Chevy vs. Ford, PC vs. Mactinosh debate here . . .

    . . . in a normal business environment, there is nothing you cannot do in Access that you can do in VB. Development in Access is simpler and faster because Access provides you with a more complete environment of development tools, right out of the box.

    Access has always suffered from the fact that is practically given away with the Office package and is available for everyone to play with, even though they may not have any programming experience. Hence, most people dismiss Access because "if it doesn't require a guru to program, it must not be a very good programming environment."

    As I have mentioned in other threads here, I currently have a customer running almost 2,000 CONCURRENT users on an Access front-end, SQL Server back-end application. The exact number, last week, was 1,811 users. And this is not a trivial application. It involves over 60, complex forms and almost 500 queries, etc., etc. I think that that is a pretty solid testiment to the ability of Access to handle the task.

    Of course you can do this in VB, but it will cost you in having to have a programmer skilled in developing SQL syntax (Access doesn't require that), skilled in developing in CrystalReports (Access has it's own, internal report writer that, I think, is better, in many ways, than Crystal Reports), etc., etc.

    Again, VB is a great environment for doing certain things. But as long as you are aware of Access' low spots, and are willing to accept and work aroung them, Access is completely up to the task of dealing with the development requirements of most small- to medium-sized businesses.

    Ken

  10. #10
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Hi Ken,

    Thanks for your elaborate answer. This is something I can work with.

    anna

  11. #11
    Join Date
    Mar 2002
    Posts
    32
    I'm late, PracticalProgram beat me to the answer , but I concur with everything he has said

    I would also like to add that from a development stand point working with databases through access is alot faster and easier then trying to code everything out in VB. And as PracticalProgram mentioned reporting is a lot easier to do in Access so if your front-end is going to have alot of reports then access is definatly the way to go. I could be a bit biased though . I have created about 10 databases in the past year and I am constantly pressed for new features so I just don't have the time to code everything out in vb. Access is a life saver.
    If you have Access 2000 or greater you can also use data access web pages (which are very cool).
    The only real downside to access is the larger disk and memory footprint on the clients computers as PracticalProgram mentioned, but using it will save you a lot of programming time


    If you e-mail me bcustalow@tseinc.net I can give you input on the best road to take based on exactly what you are doing, as well as try to help you with any questions you may have.

    Happy to help,
    Ben

  12. #12
    Join Date
    Feb 2002
    Posts
    31

    Which database to select?

    I am excited that you people replied so much to my quieries. But now I have a bigger question. Though I already posted it in many forums (incl. here) but not got any concrete answer.

    So, the question is,


    How major databases in the world (like Oracle, SQL Server, Sybase etc.) compares with each other?

    Thanks in advance.. :-)

  13. #13
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27

    security

    Here is another question:

    Do you work with linked tables?
    And how do you apply the oracle security in your fe DB?

    Or should I use the famous Pass-through queries and when I need a table just use "select * from table"?

    anna
    Last edited by annavp; 03-22-02 at 11:22.

  14. #14
    Join Date
    Mar 2002
    Posts
    32
    I'm not sure what you mean by oracle security.

    When you link in tables you can choose to save the password with the link so that you do not have to enter it every time. I assume that is what you mean by security.

    I would probably use pass-through querys for your final app though.
    I only use the linked tables for building and trouble shooting my SQL for use in a Pass-through Query.

    Just my $.02 though

  15. #15
    Join Date
    Nov 2003
    Posts
    1

    Smile

    now i need to create an access as fornt end with oracle as backend but i dun relaly know how to go about doing it...

    do someone had a sample or a simple example to let me see?? if hav, do u all mind email to jennysee11@yahoo.com.. specifying the subject as maybe like "access link to oracle" so that i won't accidentally delete the email...

    Appreciate your help.. thanks

Posting Permissions

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