Results 1 to 13 of 13
  1. #1
    Monty51 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    48

    Cycling Through DB Properties

    I hope this is a good place to ask this.

    I've posted before about how I'm converting an Access 97 db to Access 2013. It's progressing, but with fits and starts. Today's question centers around the following statement:

    Set prpUserDefined = db.Properties("glbOpsPersonnel")

    It's part of this function (some lines eliminated for clarity):

    Public Function glbOpsPersonnel(Optional SetglbOpsPersonnel As Variant) As String

    Dim db As Database
    Dim prpUserDefined As Property
    Set db = CurrentDb

    Set prpUserDefined = db.Properties("glbOpsPersonnel") ' User-defined property.

    If IsMissing(SetglbOpsPersonnel) Then ' If no value sent the return value of property
    glbOpsPersonnel = prpUserDefined.Value
    Else ' Else set property to new value
    prpUserDefined.Value = SetglbOpsPersonnel
    db.Properties.Refresh
    glbOpsPersonnel = prpUserDefined.Value


    End If

    db.Close
    Set db = Nothing

    End Function

    The line in question leads me to believe that there is a db property named "glbOpsPersonnel" and it has a value (or maybe it doesn't) that is set somewhere (or isn't). I've not been able to find where this property is set. I've been looking for something along the lines of "dim glbOpsPersonnel as Property", etc., but there's nothing like that anywhere in the DB code.

    Failing that, I searched through the registry for the value (since it was installed as a MSI package, there are some rudimentary db.properties set) but it's not there either.

    This all works perfectly well in the Access 97 version, but porting over to Access 2016 I get an error along the lines of "Value not found" when execution encounters the line in question. I want to start by listing all of the DB's properties and see if that's in there. Can anyone suggest a way to cycle through them in a VBA immediate window? Sorry for the formatting. I can't seem to retain the indentations.

    Thanks for looking.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Use code tags when posting code and indentation will hold (# on posting toolbar)
    Maybe something like this to cycle:

    Code:
    Sub PropNameAndValue()
    Dim prop As DAO.Property
    
    On Error Resume Next
    For Each prop In CurrentDb.Properties
      Debug.Print prop.Name & "  " & prop.Value
    Next
    
    End Sub
    I only took a quick glance at your code but it seems that the property name is the same as the function name. Not something I would do if that's the case.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Monty51 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    48
    It's not something I would have done either. I'm taking over maintenance of this app after the original programmer passed away without leaving documentation (this was some time ago). I'm having to figure out some of this as I go without the benefit of the author's thinking, and I'm not quite sure what this snippet does, save for return a value from or set a value to one of the DB's properties. I find it curious that it works in Access 97 and 2003, but not in Access 2016. I was hoping someone might recognize why that might be.

    Also, that routine you posted, can that be invoked from VBA's immediate window, with say, just the name of the routine?

    Thanks again.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    can that be invoked from VBA's immediate window, with say, just the name of the routine?
    If you put the code in a standard module, place your cursor within the procedure, and hit F5 it will run
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by orange View Post
    Monty51

    Have you found any reference to CreateProperty or similar?

    Just did a search and found this post by you.
    https://www.accessforums.net/showthr...createProperty
    Good spot @orange
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Monty51 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    48
    I'd forgotten about the other post, probably because I'm going in several directions at once between learning the ins and outs of different versions of Access and trying to figure out what the original programmer was trying to accomplish. I believe I resolved that issue but I can't recall how. I just know that I no longer get that error.

    I'm on to something different now!

    I'm going to focus on what that code is intended to do, which probably means breaking out the Access 97 bible. I see there's a way to create custom DB properties on the info page of the DB. I may just create the property there since every other reference I can find to glbOpsPersonnel is about comparing it or setting it (the assumption being it's already created). I'm a little lost as to why there is a function named glbOpsPersonnel and a db property of the same name. Do I understand that code correctly? The line "Set prpUserDefined = db.Properties("glbOpsPersonnel")" suggests, to me, that the db has (or should have) a property named glbOpsPersonnel, and this function is named glbOpsPersonnel (I wouldn't have named them the same, but I don't know what the author was thinking).

    Thanks for looking.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the db has (or should have) a property named glbOpsPersonnel
    It would appear to be so, but it is quite likely a custom property. Uploading a zipped copy of your db would be an option?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron - a copy of the database(zipped) would be useful.
    My guess is that there is/was code somewhere within the application to:
    -create the custom property, and
    -value that property

    That's why I asked if there was a CreateProperty statement anywhere.

    It seems that may have been a way/method to control the application or protect parts of the database from some users/roles.

  10. #10
    Monty51 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    48
    Quote Originally Posted by Micron View Post
    It would appear to be so, but it is quite likely a custom property. Uploading a zipped copy of your db would be an option?
    My customer's not comfortable with the notion of putting the whole DB out there, sorry.

    I've been looking for a property name glbOpsPersonnel. There is a function written by the author called CreateProperty that has a line in it: "Call CreateProperty("glbOpsPersonnel", 10, "9")", but it's commented out (along with several others). I tried uncommenting it and still get the Property Not Found error. I noticed the statement was part of a sub called cmdSetProp_Click, but a search of the entire project's VB code didn't reveal a call to it.

    I tried setting a DB property from the Info page (View and Edit Database Properties) but that didn't work.

    I cycled through CurrentDB.Properties using some code another viewer was kind enough to post for me, and I didn't see it there, even after manually setting the property.

    I'm at a loss. It works in Access 97 but not Access 2016.

    Thanks for looking.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    There is a function supplied by moke123 that allows you to amend sensitive data, so you can upload it.
    However it sounds like we do not need the data?, just the DB?

    If not, not a lot more we can do here? Earlier versions of Access were apparently a little more tolerant of mistakes.
    Regardless, just set the property, even if you do not use it now (clearly the code does). That CreateProperty("glbOpsPersonnel", 10, "9")" should indiacte what it needs to be set as, if that matters at all now?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is link to CreateProperty. Do you or anyone with any familiarity with the database/application have any documentation/experience with running or adjusting it?
    Are you having a problem with its operation? If so, please describe.
    Is this just a "hanger-on" issue that you'd like to resolve, but is not a show stopper?

    Do you have operational databases running in 97 and 2016?
    Can you check the 97 version to see if the Property exists?

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I tried setting a DB property from the Info page (View and Edit Database Properties) but that didn't work.
    We need more explaiation on what you tried. I'm not aware of any info page other than those which are available from the backstage options dialog.

    This all works perfectly well in the Access 97 version, but porting over to Access 2016 I get an error along the lines of "Value not found" when execution encounters the line in question.
    if you run this code in access 97 version you should see the property listed

    Code:
    Sub PropNameAndValue()
    
    Dim prop As DAO.Property
    
    On Error Resume Next
    For Each prop In CurrentDb.Properties
      Debug.Print prop.Name & "  " & prop.Value
    Next
    
    End Sub
    If you run it in the access 2016 version I suspect it doesn't show up.

    Can you confirm this?

    edit: oops Orange beat me to it.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-30-2021, 03:13 PM
  2. Cycling through a collection of controls
    By Paul H in forum Forms
    Replies: 4
    Last Post: 08-18-2015, 11:26 AM
  3. Replies: 6
    Last Post: 12-03-2013, 02:59 AM
  4. Cycling through values in a list box
    By kmw in forum Programming
    Replies: 4
    Last Post: 08-27-2012, 10:58 AM
  5. Cycling through subform Records
    By paddon in forum Programming
    Replies: 3
    Last Post: 05-10-2011, 10:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums