Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Property value too large? (Access 2007)

    We have a crosstab query that gives the error "Property value too large" when we try to save it, even if no changes are made, and reparing/compacting the database does not help. How can we fix this? (The PARAMETERS item in the SQL code includes "Text (255)" - is this required or relevant?)

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

    Re: Property value too large? (Access 2007)

    Just like a table, a query can have a maximum of 255 fields. Could the field that acts as Column Header in the crosstab query have more than, say, 250 different values? That would cause a problem.

    Another possible cause is the size of a record. The total size of all fields combined (excluding memo and OLE fields) may not exceed 2,000 characters.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Property value too large? (Access 2007)

    Thanks for your reply. The number of column header fields depends on a query parameter (default = 7, normal range 7-30; this determines the number of date columns, whose headings are calculated forward from today's date). There are 12 other unchanging columns (3 normally hidden). The total number of columns and record sizes don't seem to be the problem.

    I was able to save the query immediately after opening it in design view, but the changes that I need to make regularly (the width of the dynamic date columns) need to be made in datasheet view (unless there is a way to fix the column width in the properties?)

    NB: This problem did not occur in Access 2000, only since conversion to Access 2007. Also, after repairing/compacting the database, its size reduces to about 4 MB, but it quickly increases to over 250 MB as this query is used.

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

    Re: Property value too large? (Access 2007)

    End users should never use queries directly, so the column widths of the query shouldn't matter.

    I don't have Access 2007, so I cannot test anything. Perhaps someone else can help.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Property value too large? (Access 2007)

    OK thanks. This is the only one of our queries that users use directly; all others have forms or reports as front ends. We were not able to make a form for this one because the column headings are calculated from the date and keep changing.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Property value too large? (Access 2007)

    I do have a system with Access 2007 installed, and haven't seen any odd behavior with crosstab queries, but they are generally fairly small. Can you post the SQL for the query? If so, I see if I see anything unusual.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Property value too large? (Access 2007)

    Thanks for your reply. The SQL code is attached.

    We would prefer to create a form rather than use the query directly, but we were not able to make a form for this query because the "Availabilty Date" column headings are calculated from the date and keep changing. Is there a way to create a form that will work with this query?

    If not, is there a way to save the column width of the query in datasheet view to 4 units? As noted in the initial post, we currently need to keep doing this manually, but it only works occasionally; most of the time it just says "Property value too large".
    Attached Files Attached Files

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

    Re: Property value too large? (Access 2007)

    The first two parameters don't appear to be used - is Editor Allocation a query that has these parameters? If not, you can omit them. And I'd define the [Enter number of days availability (Enter=7):] parameter as a long integer:

    PARAMETERS [Enter number of days availability (Enter=7):] Long;

    If the set of columns changes over time, you'll have to use a dynamic setup. Personally, I'd use a dynamic crosstab report instead of a form (since a crosstab query is not updatable, a form based on it will be read-only anyway). You'll find information if you search this forum for dynamic crosstab report - for example <post#=35485>post 35485</post#>, <post#=134439>post 134439</post#> or <post#=248210>post 248210</post#>.

    If you prefer to use a form, see <post#=297,950>post 297,950</post#> - <!profile=Zave>Zave<!/profile> wrote code to create such a form from scratch.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Property value too large? (Access 2007)

    >The first two parameters don't appear to be used - is Editor Allocation a query that has these parameters? If not, you can omit them.

    Yes, Editor Allocation is a query that has these parameters, which are needed.

    >And I'd define the [Enter number of days availability (Enter=7):] parameter as a long integer:

    I tried this, and when I saved the change, it said "Property value too large" again, although the change was saved (and I also got another message that occasionally appears when saving, although with no apparent consequences - "Cannot open database 'Aggregate type'. It may not be a database that your application recognizes, or the file may be corrupt."). However, I still get "Property value too large" when trying to save after changing the column widths.

    >If the set of columns changes over time, you'll have to use a dynamic setup. Personally, I'd use a dynamic crosstab report instead of a form (since a crosstab query is not updatable, a form based on it will be read-only anyway). You'll find information if you search this forum for dynamic crosstab report - for example post 35485, post 134439 or post 248210.

    > If you prefer to use a form, see post 297,950 - Zave wrote code to create such a form from scratch.

    I would like to create a dynamic report or preferably a form as suggested (nothing needs to be updatable) but I am not familiar with using code in Access. Where does the code go?

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

    Re: Property value too large? (Access 2007)

    Perhaps your database is (partly) corrupt. You might try creating a new database and importing all database objects from the problem database.

    Zave's code could go into a standard module, and you could run it from a command button on a form. But I'd suggest that you familiarize yourself with Access VBA before attempting this.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Property value too large? (Access 2007)

    >Perhaps your database is (partly) corrupt. You might try creating a new database and importing all database objects from the problem database.

    >Zave's code could go into a standard module, and you could run it from a command button on a form. But I'd suggest that you familiarize yourself with Access VBA before attempting this.

    This level of solution seems to be beyond my present ability.

    However, the initial issue did not arise in Access 2000, only since conversion to Access 2007. The database contains dozens of linked tables containing thousands of records, and dozens of queries, forms and reports that are used by several users daily with no evidence of any corruption apart from the occasional error message when attempting to re-save this particular query. After repairing/compacting the database, its size reduces to about 4 MB, but it quickly increases to hundreds of MB or sometimes even a few GB when attempting to re-save this query. Could this be normal or does it indicate corruption? Is there a way of finding out what is causing the "Property value too large" message? Is there an alternative way of coding a specific datasheet column width into the query?

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

    Re: Property value too large? (Access 2007)

    It's definitely weird that trying to save the query design would have such an impact on the size of the database. I have no idea what causes it.

Posting Permissions

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