Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Analyze It with MS Excel

    I'm trying to export data from Access 2000 into Excel 2000. After opening the table (which has 64,000 records), I'm selecting Tools | Office Links | Analyze It with MS Excel, but I get an error message telling me that there are too many records. It's evidently creating an Excel 95 format workbook, but I don't know why. Does anybody know how to change it to create and Excel 97/2000 format file? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Analyze It with MS Excel

    It should be creating an Excel 2000 worksheet. Is the error message an Access error or an Excel error? What exactly is the message and the error number?

    You may be hitting some other limitation, or combination of limitations, besides the simple number of rows and columns allowed in Excel. For example, do you have multiple memo fields in the table? Do you have any memo fields that exceed 32K characters (allowable in Access 2000, but not in Excel 2000)? Is every field 255 characters in width (that happens with make-table queries fairly often)? In that case, even single-table Access queries may fail.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Analyze It with MS Excel

    The message is an Access error message. No message number, just a dialog box stating:
    "There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access."
    I've noticed before that the Excel file that gets created (when I've extracted < 16K rows) is an Excel 95 file, so I'm curious how my installation of Access is somehow set to extract in the older format. Plus how would I change it...

    Not a big deal, though. I took the extreme path of actually looking at a book last night (Woody's Office 2000 book), and in there he talks about using File | Extract rather than Office Links. If I do that, I do get to choose the file type.
    Thanks for your help.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Analyze It with MS Excel

    I should have said File | Export, not File | Extract.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyze It with MS Excel

    Have you tried using the Get External Data command in Excel? It will set up an ODBC-type of connection to Access (and other stuff) and has a lot of details on what columns to import, etc. I have done this to import tables from Access into Excel when they are apparently too large to do via File|Import.

    A warning, tho: the table you get in Excel will be 'married' to the Access table (it will be connected for refreshing data, etc.) -- if you don't want that functionality, you will have to delete the Name Reference in Excel that is created when you Import External Data.

Posting Permissions

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