Export runs too slowly

[KathyUMN]

Hello. I run several queries daily to help my users identify cases from our hospital database. I have a view of the Oracle db in Access that allows me to do this. It is a highly normalized design with tons of tables required to make a nice report. Periodically, these tables are updated in production, or new ones are added, after which I need to update the views I have by using linked table manager. As you know, Access decides what to call the primary key and this is often wrong.

Since the last two times this occurred, my technique of running a macro to output a report via email is running ridiculously slow. I have had our Oracle/Access person over to help me, and he used VBA to delete the extraneous indexes, and in some cases all of them. The problem persists. I have tried running the steps manually, and exporting a report to any type of file runs too slowly. The only thing that works is to export a query to Excel. This is obviously not as nice for my users.

I'm not sure how much more detail to provide. It's a little frustrating because the IT person I need for help doesn't have time to resolve this issue for me and I'm not sure I can be clear enough.

I am using queries and reports that have always worked quickly in the past. I am an old DB-2 programmer so I understand SQL, but not Visual Basic. This is a sideline on my job today.

I was on Access 2003, upgraded to 2007 recently.

Thank you, Kathy

 

[RuralGuy]

Have you disabled the SubDataSheets in the tables yet? That slows down ac07 quite a bit. And of course Name AutoCorrect should be disabled.

 

[KathyUMN]

Where do I disable the subdatasheets? I found the other and reset it, but it has not helped.

 

[RuralGuy]

Open the tables in design mode and in the top left there is a little flap. Right click and go to properties. Change [Auto] to [None].

 

[KathyUMN]

I finally had a chance to try the suggestion of changing subdatasheets to [none]. Unfortunately, the results are still taking a long time to run.
any other ideas? Thank you.

 

[RuralGuy]

Reports require the use of a printer driver so that could be a source of the problem.

 

[RuralGuy]

BTW, you have done the usual culprits right? Turn off Name AutoCorrect, persistant connection. The other things mentioned in this link?

 

[KathyUMN]

Thank you for the link. Some things I can change, but these are linked tables from an Oracle system that I do not have control over (views of the live data). However, I did run the performance analyzer and was able to delete some extraneous indexes. It's still not great, but I am doing a lot in these queries. Thanks and that's it for now.

 

[RuralGuy]

Thanks for posting back with the update.