none
Difficulty Deleting a Custom Property RRS feed

  • Question

  • Good day, and thanks in advance for your help:

    I am writing a program that defines Part Numbers for the Major Objects in an Access 2010 database. Why I want to do that is not the question.  When I assign a part number I want to differentiate between Table and Query part numbers so I chose creating the Custom Property ("PartNumber") using QueryDef and TableDef instead of Containers("Tables").  When doing the testing on the routine that assigns a dummy part number I found some errors in the part number assigned, and decided that the best approach to resolving the source of the error was to delete the PartNumber Property and troubleshoot the routine that generates the part number.  This is where a problem was discovered.  I used a simple routine to delete the PartNumber Property from all the queries in a test Database.  The Test database is external to the database that contains the  Delete routine: 
    Sample of Delete Routine
    Sub Test_Delete_Query_Part_Number()
    Dim d As Database, dbs As Database, rsa As Recordset
    Set d = CurrentDb
    Set dbs = OpenDatabase("F:\Manage Picture Database\Manage Pictures.accdb")
    For i = 0 To dbs.QueryDefs.count - 1
        If Left(dbs.QueryDefs(i).name, 1) <> "~" Then
        dbs.QueryDefs(i).Properties.Delete "PartNumber"
        End If
    Next i
    dbs.Close
    Set dbs = Nothing
    Set d = Nothing
    End Sub
    Seems pretty straight forward.  However, when I went through and checked, the routine only deleted the PartNumber Property on some QueryDefs.  I tried going to the actual test database and using a similar algorithm, but I was not able to delete some of the queries even while working in the test database.  I then wanted to see if I could delete the PartNumber Property by using the Containers("Tables") approach, but I discovered that when I looked at a specific query in QueryDefs(queryname).Properties("PartNumber") exists, but in Containers("Tables").Documents(queryname).Propertied("PartNumber") does not exist.

    I did find some queries that indeed had the PartNumber Property Deleted, but I could not understand why some didn't and others did.  Perhaps there is a simple answer to this, but I couldn't discover it.  My work around was to go through all of the QueryDefs and set the value of the PartNumber Property to the correct dummy value using the parallel to the  example above.

    Sample of Set Value Routine
    Sub Test_Set_QueryDef_Part_Number()Dim d As Database, dbs As Database, rsa As Recordset
    Set d = CurrentDb
    Set dbs = OpenDatabase("F:\Manage Picture Database\Manage Pictures.accdb")
    For i = 0 To dbs.QueryDefs.count - 1
       If Left(dbs.QueryDefs(i).name, 1) <> "~" Then
            dbs.QueryDefs(i).Properties("PartNumber").value ="20-1xxxx-yy"
       End If
    Next I
    dbs.Close
    Set dbs = Nothing
    Set d = Nothing
    End Sub

    That Simply got all of the QueryDefs with a correct starting part number, but did not resolve why there were problems with creating correct values.  However, in the future it would be nice to be able to delete a Custom Property if desired, and understand why if I can set the Property for a QueryDef, it also doesn't propagate into the Container / Document properties for the same Query.   

    Monday, August 19, 2019 10:42 PM

All replies

  • Hi,

    Did you include the error trapping whenever a Query that doesn’t have the “Custom Property”?

    It will skip the For Loop and will not delete the rest of the Queries.

    You need to have a condition to loop through the Query properties by searching your custom property inside your For Loop count.

    With an inside for-loop, you can avoid the error.

    Wednesday, August 21, 2019 3:41 AM
  • Probably you can just ignore the error if the property doesn't exist.  But there are other issues.  Your code declares and sets both "d" and "dbs", and then doesn't use "d" at all.  Are you doing this in the current database (where the code is running), or are you running the code in one database, to operate on the querydefs in a different database?  For the moment, I'll assume you are running it against the current database, in which case the code can look like this:

    Sub Test_Delete_Query_Part_Number()
    
        Dim dbs As DAO.Database
        Dim i As Long
    
        ' If deleting the property in the current database, say this:
        Set dbs = CurrentDb
        ' If deleting the property in another database, say this:
        'Set dbs = OpenDatabase("F:\Manage Picture Database\Manage Pictures.accdb")
        
        On Error Resume Next    ' skip error if property doesn't exist
        
        For i = 0 To dbs.QueryDefs.Count - 1
            With dbs.QueryDefs(i)
                If Left(.Name, 1) <> "~" Then
                    .Properties.Delete "PartNumber"
                End If
            End With
        Next i
        
        ' If deleting the property in another database (not the current one), say this:
        'dbs.Close
        
        Set dbs = Nothing
    
    End Sub
    

    If that doesn't work, let me know.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 21, 2019 7:49 PM
  • Probably a quick and dirty solution might be to add 

    On Error Resume Next

    And just log before  and after any day manipulation (delete/set)...so you will know which queries processed OK...and which failed..so you would check on the failing ones to find the problem

    Saturday, September 7, 2019 11:37 AM