Results 1 to 3 of 3
  1. #1
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83

    Query speed issues


    I'm hoping someone may have some ideas as to why the following keeps happening when I try to build and run queries in Access 2007.
    I'm using linked Oracle tables and am pulling under 200k records.
    Four tables joined with just a couple of IIf statements and criteria.
    Each time I run my query, no matter how many records it is supposed to pull based on the criteria I enter, I end up having to alt-ctl-del to get out of Access as it completely locks up on me.
    I am also trying to create a query based off my main query and joining a crosstab query with it. In the query design grid, I drop my main query, then, when I go "show tables" and try to add the crosstab query to the grid, it locks up on me again.
    Not sure how much info is needed, but I have a Dell with a 1.66 ghz processor, 3 GB RAM and am linking to the Oracle tables through a network.
    The same thing is happening to me when I try to pull data into Excel from Access.
    Could this be my system performance, network? Does 2007 have some speed issues? I would appreciate any help or point in the right direction.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does a query without the IIf() functions run normally? Since Oracle doesn't know what an IIf() is, JET may not be able to pass the SQL to Oracle to run, which would force it to pass all the records down to your PC to run the query on. Normally JET is pretty good about having the server handle it, but functions not understood by the server can gum up the works. If a query without them runs normally,you might consider a pass-through query using some Oracle equivalent to the IIf() function. In SQL Server, that would be a CASE statement, but I don't know Oracle's.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    Thanks for the help. Still running slowly but I removed a problem table and it's much better.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-23-2010, 04:01 PM
  2. Query duplication issues
    By MiCCAS in forum Queries
    Replies: 1
    Last Post: 02-18-2010, 11:31 AM
  3. between query issues
    By jderrig in forum Queries
    Replies: 4
    Last Post: 01-15-2010, 02:30 PM
  4. Replies: 4
    Last Post: 11-06-2009, 09:51 AM
  5. Speed issue
    By hey23 in forum Queries
    Replies: 1
    Last Post: 06-24-2009, 09:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums