locked
Property Value is too Large when storing metadata - is there a workaround? RRS feed

  • Question

  • Access 2007/2010/2013

    I am storing metadata about queries in a QueryDef's DAO properties.  This allows me to carry the metadata to other databases when the queries are exported.

    This has been working very well, until the other day when I ran into an issue with a large property, and received a "Property Value is too Large" error.  I assumed I could work around this issue by simply copying the overflow into another property, but this did not work.  I still got the same error. It seems there is a hard limit on the total number of bytes stored across all properties in the QueryDef.

    1. What is the hard limit on the total number of bytes for all properties in a QueryDef? (In Access 2013 it appears to be ~32k, can you confirm?)

    2. Is there a workaround that would allow me to keep these properties in the QueryDef?  If not, I plan to store the overflow in a table, but would prefer not to.

    Here is VBA to reproduce this:

    Function PropertyLimitExample()
        Dim p As DAO.Property
        Dim qd As DAO.QueryDef
        Dim queryName As String
        Dim propertyValue As String
        Dim i
        
        Randomize Timer
        
        queryName = RandomString(20)
        
        Set qd = CurrentDb().CreateQueryDef(queryName, "SELECT [Name] FROM MSysObjects")
        
        propertyValue = RandomString(1000)
    
        On Error GoTo DisplayError
        For i = 1 To 100
            Set p = qd.CreateProperty(RandomString(20), DAO.dbMemo, propertyValue)
            qd.Properties.Append p
        Next
        
        Exit Function
    DisplayError:
        Debug.Print Err.Description
        Debug.Print "Number of Properties Stored: " & i - 1
    End Function
    
    Function RandomString(numChars As Integer) As String
        Dim i
        RandomString = ""
        For i = 1 To numChars
            RandomString = RandomString & Chr(Int(Rnd(1) * 26) + 65 + (32 * Int(Rnd(1) * 2)))
        Next
    End Function


    • Edited by Transistor1 Wednesday, April 1, 2015 12:57 PM
    Wednesday, April 1, 2015 12:56 PM

Answers

  • The only way I see is to store the value of a property in a table with an ID as a Primary Key and to store the same ID as a QueryDef's property. So you keep them "connected" after export is done: export of queries and this "dictionary" table of properties.

    I doubt about existence of some workaround too.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, April 1, 2015 2:06 PM

All replies

  • Why dbMemo? Isn't dbText enough for this string?

    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, April 1, 2015 1:22 PM
  • Hi Andrey,

    Thanks for your reply.

    The strings can get relatively long, so no.  Putting that aside, dbText and dbMemo behave identically in this particular situation (try substituting dbText in the code I posted).

    Wednesday, April 1, 2015 1:25 PM
  • Hi,

    yes, I see now. Mine always fails with the result:

    Number of Properties Stored: 31

    Changing RandomString argument to 100 leads to:

    Number of Properties Stored: 261

    Your idea about total size of data seems to be correct.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, April 1, 2015 1:47 PM
  • Hopefully there is some workaround, but I doubt it.  I'm hoping someone from Microsoft will reply.  I think I'm going to have to use a table, and the data will be lost when exporting the queries through Access's native Export feature, which is unfortunate.

    Wednesday, April 1, 2015 1:56 PM
  • Access 2007/2010/2013

    I am storing metadata about queries in a QueryDef's DAO properties.  This allows me to carry the metadata to other databases when the queries are exported.

    Hi Transistor1,

    I have no answer to your questions, but I am intrigued by them. I would like to know what kind of information you store in the properties. I then can see whether it matches with my systematics.

    About 95% of all the functionality in any of my applications is driven by generalized code in a library database, all tuned by meta data tables. Exporting to a different database is just a copy of a couple of records from the appropriate meta data tables.

    Imb.

    Wednesday, April 1, 2015 2:03 PM
  • The only way I see is to store the value of a property in a table with an ID as a Primary Key and to store the same ID as a QueryDef's property. So you keep them "connected" after export is done: export of queries and this "dictionary" table of properties.

    I doubt about existence of some workaround too.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, April 1, 2015 2:06 PM
  • Hi Imb-hb,

    Thanks for your reply.

    I sell an add-in for MS-Access, called Access SQL Editor. This allows users to save SQL queries with formatting and comments:

    https://www.fieldeffect.info/trial/

    I save the formatting information in the DAO properties. This conveniently allows users to keep their formatting when moving queries to another database.

    A user ran into this issue a couple of days ago with an extremely large query (>20000 chars).

    Not having a workaround will mean that I'll have to create my own export, and that users can't use the native export [for long queries].



    • Edited by Transistor1 Wednesday, April 1, 2015 2:20 PM
    Wednesday, April 1, 2015 2:11 PM
  • The only way I see is to store the value of a property in a table with an ID as a Primary Key and to store the same ID as a QueryDef's property. So you keep them "connected" after export is done: export of queries and this "dictionary" table of properties.

    I doubt about existence of some workaround too.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Hi Andrey,

    Yes, your method is exactly what I had planned on implementing if there is no workaround; except that I only intend do this if the total size of all properties is over some set limit. This way, native Access export can still be used for shorter queries.

    Thanks again for all your assistance.

    Wednesday, April 1, 2015 2:16 PM
  • I sell an add-in for MS-Access, called Access SQL Editor. This allows users to save SQL queries with formatting and comments:

    https://www.fieldeffect.info/trial/

    I save the formatting information in the DAO properties. This conveniently allows users to keep their formatting when moving queries to another database.

    Hi Transistor1,

    I am afraid we are too far apart, for the sole reason that I do not use QueryDefs! In my opinion QueryDefs are far too intransparant. But, I agree, they have their function. The queries that I need are generated on the spot, both for display/input and for reporting.

    For display, this sql-string is assigned to the RecordSource of a generalized form, that gets all its further information from a meta data table. There is very much flexibility build in through dynamic menus based on the meta data.

    Succes with your project.

    Imb.

    Wednesday, April 1, 2015 3:15 PM
  • Hi Imb-hb,

    I think I follow what you are saying - you store templates for your SQL, and "fill in the blanks" using data from tables; sort of like using SQL parameters (except more flexible).  That is a very good technique, but in this particular case not exactly what I was hoping to do.

    Thanks again for your help!

    Wednesday, April 1, 2015 4:13 PM
  • Hi Imb-hb,

    I think I follow what you are saying - you store templates for your SQL, and "fill in the blanks" using data from tables; sort of like using SQL parameters (except more flexible).  That is a very good technique, but in this particular case not exactly what I was hoping to do.

    Thanks again for your help!

    Hi Transistor1,

    Thank you for the way you summarize things. It is better than I could produce. But it goes a few steps further.

    The same systematics is also used for conditional authorization on field level, visibility, input checking, after update actions, etc. (in fact it is not the field itself, but the control representing the field), all based on the use of the same few forms.

    Nice thing in this approach is that I can immediately see in my 70+ applications where I use what definition for what purpose. It is far more transparent than the use of QueryDefs.

    Imb.

    Wednesday, April 1, 2015 6:17 PM
  • Hi Imb-hb,

    That sounds like a really interesting architecture. It sounds like you have pre-defined logic behind each control on your form that you can re-use, making the maintenance and design your apps much more simplified. Very ingenious!

    My particular app is an "ActiveX object" (although Microsoft doesn't use that terminology anymore-- I think they say "COM-visible user control" now) hosted on an Access form, which uses Access COM objects via interop.

    Wednesday, April 1, 2015 6:44 PM
  • That sounds like a really interesting architecture. It sounds like you have pre-defined logic behind each control on your form that you can re-use, making the maintenance and design your apps much more simplified. Very ingenious!

    Hi Transistor1,

    Yes, I think you can share the systematics under the RAD-tools.

    Defining a new application is not more than naming the application name, naming the tables, and the fields in the tables. Each field has a field type that goes a little further than Access does. This is enough to make a working application with all possible functionality, but until now without data.

    This works, because all code is heavily generalized, and stored in a referenced library database, so in use by all applications. About 95% of all functionality is stored in the library database, with maximum re-usability.

    It is also a very dynamic tool for maintenance, since all changes to the data definition in the BE are automatically synchronized upon a new installation in production.

    It takes a little more work to build in the subtle differences of the logic that is application dependent, but most can be done by tuning the meta data tables.

    You can understand that the level of cosmetics is almost zero.

    Imb.

    Wednesday, April 1, 2015 7:59 PM