Results 1 to 7 of 7

Thread: ODBC (2000)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC (2000)

    We currently use a report generator called Impromptu. I would like to use Access to generate reports by linking to the oracle database tables. Someone said that using Access to generate reports would be a nightmare. I imagine I would have to link to the Oracle tables (which there are many). Does anyone know what this would entail and why it would be so difficult.

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ODBC (2000)

    There's no reason why a report based on linked tables should be more troublesome (except possibly from a performance perspective) than one based on native Access tables.

    If you're used to using Access to design reports based on its own tables then you already know what the report designer looks like, and most people are either perfectly happy with it, or dislike it based on a comparison with other tools they're more familiar with.

    Do you know why others are saying this process might be troublesome??

    Jeremy

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC (2000)

    The person said that I didn't want to go there - I took it to mean using Access to generate the reports would be more difficult than using Impromptu. Do you know of an article on the Microsoft knowledge base that would have any information on this subject? I am searching the knowledge base now but I not finding a generic article outlining the procedure.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ODBC (2000)

    I don't know of a specific article, but the general principle is the same for all server databases (Oracle, SQL Server, etc). To link tables, start with the tables tab of the database window active and then go into File, Get External Data, Link Tables...

    From the dialog box that appears, change Files of Type to say ODBC databases. You will then be prompted to create a data source if one does not exist already that points to your remote database. After you create the first linked table, you can just select your new Oracle data source each time. Click the New... button and select the ODBC driver for Oracle - which I'm assuming you have installed already(!) - then just follow the prompts to do with server name, database name, login details etc. Give your data source a meaningful name, and then you can select this from the list of sources each time.

    I think there should be some straightforward details in the Access help file for once if you search for "link tables" and follow the help for Importing or linking ODBC data.

    Once you have a table linked, it shows up in the tables window with a special icon next to it and you can use it like a normal Access table in queries and reports.

    Jeremy

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC (2000)

    I am not familiar with the Impromptu product, but it looks to be a user oriented reporting tool that is reasonably powerful. On the other hand, I've done reports in Access quite frequently, and I find it pretty straightforward as long as you don't have complex formatting requirements. Where those exist, we use Automation in Access to create Word documents.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ODBC (2000)

    It would be interesting to find out what the reason is? As was pointed out in an earlier post performance may be a problem if Oracle decides to execute Full Table scans.
    Why don't you ask him? It is all too easy to say don't do something and not back it up by reason.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC (2000)

    Linda,

    I am very familiar with Impromptu and Powerplay and the impromptu report writer provides alot of flexibility with regards to the catalogues set up in the oracle database. In the past, when I have worked with personnel using Impromptu, they complained that it could not provide them with the data they needed. Upon further evaluation, is almost always came back to the users did not know how to use the tool and/or they did not understand the data and the tables the data were stored in which were accessed via catalogues. In a nutshell, impromptu connects to the oracle data via catalogues, creates the sql code for you, and gets the data.

    Even if you use access to connect to the data via ODBC, you will still need to know how the data in the various tables relate to one another and rebuild these relationships in access else the data is still meaningless. Using access to create reports can work as well if not better then impromptu if the data is linked and queried correctly. However, you already have a very flexible tool.

    Any chance you are using Impromptu to connect to a financial database? If so, I can understand the comment that it would be a nightmare as the financial databases tend to have 1,000's of tables that are hard to understand their links. Try looking at the ERD for additional information.

    HTH
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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