×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

improving speed of queries of linked tables

improving speed of queries of linked tables

improving speed of queries of linked tables

(OP)
I have a number of databases that have back-end table to both access and oracle.

The oracle tables seem very slow, especially in larger queries. I am using the 10g driver for oracle.

Does anyone have any ideas about speeding up the connection or other ways of increasing the performance of the oracle tables?

David Pimental
(US, Oh)
 

RE: improving speed of queries of linked tables

I haven't queried Oracle data for a number of years. I believe we had performance issues until we used the Microsoft drivers for Oracle. If your queries are limited to read-only Oracle data, you should be using pass-through queries.

I found significant performance improvement when I could use pass-through queries to populate temporary, local Access tables.

Duane
Hook'D on Access
MS Access MVP

RE: improving speed of queries of linked tables

(OP)
Yeah, unfortunately, the issues that I am having are with data entry on a form. Any other ideas?

David Pimental
(US, Oh)
 

RE: improving speed of queries of linked tables

Did you try the Microsoft Drivers for Oracle or not?

Are you opening forms that are filter to one record?

Duane
Hook'D on Access
MS Access MVP

RE: improving speed of queries of linked tables

(OP)
I am using the oracle driver (10g) and no, I am not filtering the records.

David Pimental
(US, Oh)
 

RE: improving speed of queries of linked tables

When I asked those questions, it was a hint that you should try them not just reply with a yes/no answer.

Duane
Hook'D on Access
MS Access MVP

RE: improving speed of queries of linked tables

(OP)
the microsoft driver is slower than the oracle driver. So, I am not going to switch. I have tested both. the microsoft driver also has other issues.

I can't use pass-through queries on updates and inserts.

It is not like I haven't tried this stuff before.

Thanks for the suggestions though.

David Pimental
(US, Oh)
 

RE: improving speed of queries of linked tables

Quote (dpimental):

It is not like I haven't tried this stuff before.
I suppose you have open forms based on a very limited number of records also.
You might try to begin a thread with what you have tried so we don't waste our time.

Duane
Hook'D on Access
MS Access MVP

RE: improving speed of queries of linked tables

dpimental,
Why can't you use PT queries on updates and inserts?  I've done many mdbs that do this using unbound forms.  dhookom's suggestion of using a temp Jet table will work on this as well.

In my opinion, when using Access as a front-end to a sql db, it's best to use the web paradigm--look at the forms like you might an html form--fetch/load the data, then on an update or insert button, scrape the screen, create the pt sql and do the update/insert.

The only place I'd be using directly linked tables is in a 'browse' form, where one chooses the data to edit or insert.

If you're editing or inserting multiple records, ie, in a continuous form or datasheet (like subform data), then the local jet table is easier to implement but it's still doable in unbound form--it's just more of a pain.

Bottom line--in my humble opinion it's best to look towards breaking away from the comfort of Access' form/subform model and look to doing as much as possible on the server and treating the UI as unbound.  This makes the skillset very portable to web development as well.
--Jim



 

RE: improving speed of queries of linked tables

(OP)
Guys, thanks for the input. I certainly appreciate the experience and wisdom. I will review with my manager, as it is his call.

Thanks Again.

David Pimental
(US, Oh)
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close