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.

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.

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.

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.

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.

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.

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.