Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post

    table or row limit (2000 SP3)

    Hi,

    I Have 14 spreadsheets that all link to one other spreadsheet.

    I want to combine them & run queries on all 14.

    I've linked to them and I tried to combine them with a Union query. Access seems to let me union 6 tables (linked Spreadsheets) but if I try to link all 14 I get a "number of columns do not match" error.

    All spreadsheets have the same number of columns. Is there a limit to the number of tables you can Union, or the number of rows ( I have 4500)?

    Thanks
    Jim MacLeod
    Shetland Isles

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

    Re: table or row limit (2000 SP3)

    I don't think there is a limit to the number of UNIONs in a query, or to the number of rows returned. The error message probably means that one of the columns is interpreted as text in one of the spreadsheets and as numeric in another one. Access determines the data type in an Excel spreadsheet by looking at the values in the first few rows.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: table or row limit (2000 SP3)

    Thanks for that reply Hans, it made me find the problem.

    FYI, although the spreadsheets had the same number of columns, Access created another field on one of the spreadsheets. This extra column only appeared on the Excel print preview, the column had no data, no formatiing (other than a non-standard width), and no borders. I guess it used to contain somehing.

    Bizzarly, deleting the column didn't help. All columns to the right of the last column had to be deleted before Access would stop adding an extra field.

    Thanks again,
    Jim

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

    Re: table or row limit (2000 SP3)

    Glad you found it. This kind of problem can be hard to pin down.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: table or row limit (2000 SP3)

    There used to be a limit of 49 for UNION queries back in Access 97 days, i don't know what it is in later versions.

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

    Re: table or row limit (2000 SP3)

    I have no idea whether the limit has been increased in more recent versions. You'd have a problem with the design of your database anyway if you needed a union query with that many SELECT statements...

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: table or row limit (2000 SP3)

    Not really, i had an application which had 84 databases spread around a newwork that contained pays per month for a betting organisation.
    I was building this UNION query in VBA and i struck the limit.
    So when i reached 49 i then ran that query and started to build the UNION query again.

Posting Permissions

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