Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    14

    Unanswered: Question on ODBC and SQL Differences (Access and Oracle)

    Hi Folks:

    I work at a research facility that uses Oracle (version 11) for the storage of our data. I have been trying to learn more about Access 2010. It seems (so far) much easier to use Access 2010 to design client 'frontends', than the old version of Oracle Forms we have been using.

    I have two questions I'm hoping someone will know:

    1. If I link an Access front end to the Oracle database using ODBC, then package the front end as an application (with install program, etc. as output by access 2010), will the app when installed maintain the link to the Oracle database? (or will I have to install the ODBC Oracle driver and relink the tables afterwards?).

    2. For my queries, am I able to use Oracle's version of SQL since the SQL is being passed to the Oracle database for processing, or do I have to use Access's ANSI-92?

    Thanks for any help or info.

    Matthew
    Last edited by MattFriend; 06-21-10 at 22:37.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Matthew,

    I would urge you to get a test machine so that you can test your installer package


    Quote Originally Posted by MattFriend View Post
    I have two questions I'm hoping someone will know:

    1. If I link an Access front end to the Oracle database using ODBC, then package the front end as an application (with install program, etc. as output by access 2010), will the app when installed maintain the link to the Oracle database? (or will I have to install the ODBC Oracle driver and relink the tables afterwards?).
    You will need to install the Oracle ODBC driver and configure it on each machine.

    When you launch the Access front end, it should use the ODBC connection without having to relink.

    Quote Originally Posted by MattFriend View Post
    2. For my queries, am I able to use Oracle's version of SQL since the SQL is being passed to the Oracle database for processing, or do I have to use Access's ANSI-92?
    I have never used an Oracle back end before. I would say you need to just test it to see.


    I normally do not use the P & D wizard in Access. It does a good job of installing the Access Runtime if needed. I find it is not good for installing updates. I owudl only use it for a first time install.

    For deploying updates see Auto FE Updater
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2010
    Posts
    14
    "You will need to install the Oracle ODBC driver and configure it on each machine.

    When you launch the Access front end, it should use the ODBC connection without having to relink."

    Thanks for the help HiTechCoach.

    One question: if I install the latest Access Runtime, does that automatically install the ODBC drivers?

    Thanks again,
    Matt

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by MattFriend View Post
    One question: if I install the latest Access Runtime, does that automatically install the ODBC drivers?
    Matt,

    Nothing else is automatically installed.

    I have found that it is very helpful to have test machine for testing deployments. I use a machine with a new install of Windows. I use software to make a image of the drive. This way I can easily reset it back for each new test.

    I would suggest that you check into using SageKey's installer (click here).
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2010
    Posts
    14
    Hi HiTechCoach:
    Thanks for the advice. I've done some testing in the last few days and I believe Access will meet my needs. I was concerned that with linked tables all of the data having to be transferred to the local machine before the data could be manipulated by the Access SQL engine.

    After further reading I discovered the option to "Pass-Through" data to the server (Oracle) so that the sql is run on the server and not on the local workstation.

    When using "Pass-Through" I am able to write the SQL according to Oracles SQL standard instead of Access's (so my co-workers will be able to more easily understand it).

    Thanks,
    Matthew

  6. #6
    Join Date
    May 2010
    Posts
    601
    Sounbds like you are making progress.

    Good luck with your project.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

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