Results 1 to 9 of 9

Thread: MS Query (2003)

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query (2003)

    I have an Access db table with about 50,000 records. If I create an MS Query in Excel only shows 32000+ records . If I try and use the record selector to go to last record a message pops up saying "can't show all records". Is there a limit to how many records I can have in MS Query? I haven't applied any filters or criteria).

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MS Query (2003)

    I don't think there is a limit (other than the 65536 record one). in XL97, I created an mdb table with 64000 items and used a query to import into excel and could see all the items, and I would not imagine that xl2003 would have a more restrictive limit.

    Is there anything weird about the data? is the file already opened?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (2003)

    I did some testing and it seems to only be happening when I include a MEMO field. Guess I just won't include that field, or make it a text field.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MS Query (2003)

    At the record where you have a problem (or maybe the one right after it), how large is the text in the memo field?

    I don't use access a lot, so I am not sure on the limits of the memo field, but I recall them being able to hold much more text than a normal field and it is stored differently, since it actually stored information on the source of the information and did not store the information itself.

    Just curious to the "real problem":
    Number of records
    Size of memofield

    If you move the record where the problem occurs to earlier in the database file does it give you a problem earlier than the current record, indicating a "problem" reading that particular memo field.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (2003)

    I deleted all notes and left the field blank - still had same problem. Tried changing format from MEMO to TEXT - 255 characters - still had same problem.
    Total records in table are 41890. It's stopping at 32768.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (2003)

    Very strange. It is not a limitation of MS Query for Access tables per se - I tested on an Access table with 60,000 records, and a memo field that contained over 255 characters in EVERY record (about 36 MB of data!) It took almost a minute, but all 60,000 records were imported correctly.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (2003)

    If I understand your problem correctly, it's not that MS Query isn't returning the correct number or records into Excel, it's that MS Query won't let you view all records within MS Query itself. Is that accurate?

    If so, I think this is a limitation of MS Query in some way. I have the same problem when retrieving records from an ODBC database. What's even better, if the number of records in the database is greater than 32,768 but less than 65,536, it limits the number of records I can view to the 32,768 as you mentioned. However, if the number of records in the source file is greater than 65,536 (so they won't all fit onto one spreadsheet), the number of records returned within MS Query is limited to 16,384.

    Regardless, when I run the query into Excel, all of the records (within the usual 65,536 limit) are returned into Excel.

    So, to sum up, I think it's just a problem with MS Query. I don't know that there's a way around it.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (2003)

    yes, you understand the problem correctly. There will never be a time that I need to look at all 40,000 records - each time I'll be filtering out specific records.

    I guess my concern is, if I only see the 32,678 records, will it filter on those records, or all 40,000+.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (2003)

    It is a display problem in MS Query, not a data problem (good catch, BAN!), so you don't need to worry - the filtered records will be from the complete set.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •