Access/Oracle Report troubles

[teqtaq]

Hi!
I ran into the issue I am not familiar with and I hope someone can give me a hint on how to resolve it.
First of all Server where my Oracle bakend is is faf far away so connections are terribly slow and it takes me forever to get tables open.
My Acees Reports using 4 Oracle views and I had made those imported not linked until Reports are designed and tested and it all worked fine.

After my Reports were re-connected to a live Oracle tables(views) using MS ODBC (I had also tried Oracle ODBC) connection here started troubles.

1. When Report ran once - it takes about 30 min to an hour for Oracle to release table. I am getting error 'Table is used by another person or process'.
I was given an advice to contact Oracle DBA for timing problem.

2.If Report was ran on my machine - another user will receive an error 'ODBC connection to Oracle failed' - so resolution is to refresh all linked tables. Then it works.

3. We were also hoping to have it for multi-user environment but considering all above I am not even going there yet but if you can tell what needs to be done I will listen.
So far access Read/Write to a Share was granted to a whole group. Do I need Createand delete if Report running Made table queries?

Thanks and sorry for rumbling

 

[dhookom]

Are you using a pass-through query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[teqtaq]

No I am not using pass-through query. Could all of these be a problem because of this? I'll go read up on it.

 

[teqtaq]

Well, pass-thru query did not ran. It asked for the connection string and gave me an error message.

I want to ask - if I am willing to lwt user to enter password while running Reports - isn't this the same as using pass-thru query? Seems like if user will log onto Oracle once and able to run the reports - why to use pass tru queries where ODBC link is entered per each query?
How these pass trues are efficient? Thanks

 

[dhookom]

Pass-through queries are generally much more efficient that creating queries from linked tables. You can't use local tables in your pass-through. You should be able to use the same basic connection information as you are using with your linked tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[teqtaq]

All right, what so I do if all of my queries are combinations of local and linked tables?
Also you mean I do not even have to have these Oracle tables linked into Access to use them? Cool!

 

[dhookom]

Since you are using both Access and Oracle tables in your application you might want use pass-through queries to populate local Access tables with only the required records/fields from Oracle. Then use all local tables for your reports.

I set this up at a client several years ago and reports that had taken 20-30 minutes to run were reduced to less than 30 seconds.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[teqtaq]

I hear you and I would have tried it, I just need a lot of time to re-do all of my work. Thanks, it makes sense. Except I can not always connect to Oracle period. Maybe your solution will work, thanks!