Results 1 to 10 of 10
  1. #1
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56

    Access query from ODBC - DB2 - Millions of records

    So I have access to this DB2 table. I used Access to link to the table. When I open the table is takes a long time for it to open because of how many entry's are in this table(i'm assuming).



    How can I get this to run faster?

    What I really want to do with this table is setup a query to only return quarters of each year I specify. So for example I needed to run a query last night on quarter 4 of 2013.

    What I ended up doing was I built a query to view the table and tryied to filter the results based on a column. That was a mistake, when I clicked on the header in Access to the column I wanted to filter and selected a date to filter by it ended up locking up after 15 minutes. It just sat there greyed out running the query. Lots of records in this table.

    Thanks in advance,
    Tom

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Don't know how the quarter is formated? (Is it a year [2013] and quarter [Q1]/[1] field? or [2013Q4], .... etc)

    2 ways to accomplish this.

    1) Build a query that use the criteria for quarter to filter the result.

    2) Build a pass through query and let the DB2 database run the query and return the result.
    Not the best article that I can find. But it will give you some idea how to build a pass through query.
    http://www.mssqltips.com/sqlserverti...to-sql-server/

  3. #3
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    Ah, I forgot to mention I had to already use the "pass-through" query because there was so much data in the table every row of data only contained "#DELETED" in them. So that's what I am using is a pass-through query.

    However, I can't build anything in DB2 I only have read access to this table and an ODBC connection pre-made to connect to it. So I have to figure out how to make this work. My main issue isn't getting the formatting/criteria for the quarters, I can figure that out. My main issue is there are so many records in the database it's taking forever to run a query or get any data returned. How can I make this faster? Can I build a query that only returns a few months of data(like quarters) at a time and that will help speed it up? Or is there something else I can do?

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    There should not be anything to build. Just pass through query that can read the table in question and use "WHERE"/criteria clause to limit the data in return. Have you successfully done that?

    The other is VBA but that is just build a SQL like you would with the pass through.

    The only other thing is to use the DB2 or other query tool like "Toad".

  5. #5
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    Ah, that's probaly it then. I have not built a "WHERE" criteria yet.... I was so focused on how slow it was I had not tried to move on...

    I did however try that "Max Records" in the access property sheet for my query. It still took forever when I specified 25 in there.

    I will try and get a WHERE statement in my query and see how that works.

    So I have a column called "LST_UPD_TS". It's formatting looks like this:
    4/17/2014 2:11:56 PM

    So I am trying to come up with a criteria to give me back the last quarter of 2013 so that would be between these two dates:
    10/1/2013 12:00:00 AM
    12/31/2013 11:59:59 PM

    Then eventually I will try and come up with a macro that will be interactive for my users so they will be questioned for the dates first and they will be able to specify their own date paramaters.

    Anyhow, I will try and come up with this criteria and see if it works.

  6. #6
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    What am I doing wrong here?

    SELECT My_Table.DOC_ID_NO,
    My_Table.ACCT_SC_ACCT_NO,
    My_Table.DOC_TYPE_CD,
    My_Table.ACCT_KEY,
    My_Table.LST_UPD_TS,
    My_Table.ACCT_NO_LN_OPN_TX
    FROM P1.My_Table My_Table
    WHERE My_Table.LST_UPD_TS BETWEEN #4/1/2014# And #4/30/2014#
    ORDER BY My_Table.LST_UPD_TS DESC
    Comes back with this error:

    ODBC--call failed.

    [IBM][CLI Driver][DB2] SQL0104N An unexpected token "#" was found follwoing "". Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601
    (#-104"

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This DB2 is an active database, regularly changes? If not, consider importing all to Access table.

    Be aware, MS removed all dBase support with Access 2013 version.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    Turns out my string representation was incorrect.
    WHERE My_Table.LST_UPD_TS BETWEEN #4/1/2014# And #4/30/2014#

    Needed to use the ODBC string representation of a timestamp. The ODBC string representation of a timestamp has the form yyyy-mm-dd hh:mm:ss.nnnnnn
    (microseconds may be truncated or entirely omitted).
    WHERE My_Table.LST_UPD_TS BETWEEN '2014-04-01 24:00:00' and '2014-04-30 23:59:59'

  9. #9
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    If I import the table from ODBC, how would I keep it updated with fresh data each day or even each hour? This table gets updated on a constant basis 24/7.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I did say "If not, ...".

    Never upgrade to Access 2013.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  2. odbc to another access db
    By bbxrider in forum Import/Export Data
    Replies: 6
    Last Post: 02-08-2011, 10:57 AM
  3. Access 2000 change ODBC query
    By btamulis in forum Queries
    Replies: 1
    Last Post: 02-07-2011, 11:00 AM
  4. ODBC -- call failed. | Oracle ODBC
    By drdexter33 in forum Access
    Replies: 1
    Last Post: 04-03-2010, 09:32 PM
  5. Replies: 0
    Last Post: 05-14-2009, 12:34 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