Jeremy,
re:-
CurrentDb.QueryDefs("qry9").Properties("Descriptio n") = "blah"
I cannot reproduce the behaviour that you see in Access2002.
The following function certainly works (and should work in all versions)
' ************************************
' Code Start
' ************************************
Option Explicit
Public Enum ERR_RETS
erObjTypeNotSupported = 1100 + vbObjectError
End Enum
' ************************************
'
Function SetProperty( _
ObjType As AcObjectType, _
ObjName As String, _
PropName As String, _
PropValue, _
PropType As DAO.DataTypeEnum, _
Optional DDL As Boolean = False _
)
Dim loObj As Object
Dim loDB As DAO.Database
Dim loProp As DAO.Property
On Error GoTo SetProperty_Err
Const ERR_MSG_OBJ_TYPE_NOT_SUPPORTED = "The object type is not supported"
Set loDB = CurrentDb
Select Case ObjType
Case acReport
Set loObj = loDB.Containers("Reports").Documents(ObjName)
Case acQuery
Set loObj = loDB.QueryDefs(ObjName)
Case acModule
Set loObj = loDB.Containers("Modules").Documents(ObjName)
Case acMacro
Set loObj = loDB.Containers("Scripts").Documents(ObjName)
Case acForm
Set loObj = loDB.Containers("Forms").Documents(ObjName)
Case acTable
Set loObj = loDB.TableDefs(ObjName)
Case Else
Err.Raise erObjTypeNotSupported, , ERR_MSG_OBJ_TYPE_NOT_SUPPORTED
End Select
Set loProp = loObj.Properties(PropName)
loProp.Value = PropValue
SetProperty_End:
On Error Resume Next
Set loProp = Nothing
Set loObj = Nothing
Set loDB = Nothing
Exit Function
SetProperty_Err:
Select Case Err
Case 3270 ' Prop not found
Set loProp = loObj.CreateProperty(PropName, PropType, PropValue, DDL)
loObj.Properties.Append loProp
Case Else
With Err
MsgBox .Number & ": " & .Description
End With
End Select
Resume SetProperty_End
End Function
' ************************************
' Code End
' ************************************
Sample calls
To not set the DDL option
------------------------------
Call SetProperty(acQuery, "Query1", "Description", "Aha", dbText, False)
Or
Call SetProperty(acQuery, "Query1", "Description", "Aha", dbText)
To set the DDL option
--------------------------
Call SetProperty(acQuery, "Query1", "Description", "Aha", dbText, True)
Terry
"Jeremy Wallace" <absolutejunk@AlphaBetCityDataworks.com> wrote in message
news:n5ydnQUH-4DkeS-iXTWc-g@speakeasy.net...[color=blue]
> Terry,
>
> Duh. I did have the dao reference, and had put the DAO.s in before the
> querydef and database, but somehow not the property. So I've fixed that.
> Thanks.
>
> But now I only get one line further. On the last line:
>
> Call MyQuery.Properties.Append(myProperty)
>
> I get error 3367, telling me that it can't be appended because "An object
> with that name already exists in the collection." Seems odd. The queries I
> test this on are all brand new, with nothing ever having been in the
> "Description" property.
>
> OOOOOooooooh.
>
> Ouch. And sorry for wasting your time.
>
> Seems all the posts I read about this property not existing until there's
> something in it do not apply to Access 2002.
>
> All I needed to do was this:
>
> CurrentDb.QueryDefs("qry9").Properties("Descriptio n") = "blah"
>
> (or any one of a zillion other ways to reference that property, which is
> already there) Oh that hurts. But at least the code will be easy to write.
>
> Thanks again.
>
> Jeremy
> --
> Jeremy Wallace
> AlphaBet City Dataworks
>
http://www.ABCDataworks.com
>
>
> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
> news:botobd$uvg$1@newsreaderg1.core.theplanet.net. ..[color=green]
> > Jeremy,
> > 1 Make sure you have a reference to DAO
> > 2 Change your declarations as follows
> >
> > Dim MyQuery As DAO.QueryDef
> > Dim myDB As DAO.Database
> > Dim myProperty As DAO.Property
> > Dim VarValue As Variant
> >
> > Terry
> >
> > "Jeremy Wallace" <absolutejunk@AlphaBetCityDataworks.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:YPKcnZC89ranwi-iXTWc-g@speakeasy.net...[color=darkred]
> > > OK, so I've been playing with this for too long, not getting it to[/color][/color][/color]
work.[color=blue][color=green][color=darkred]
> > > Seems pretty simple, and I'm sure I'm just klutzing some syntax,[/color][/color][/color]
but...[color=blue][color=green][color=darkred]
> > >
> > > I need to update the description property of queries via code. I know[/color][/color]
> that[color=green][color=darkred]
> > > the property doesn't exist unless it's been set, because it's an[/color][/color][/color]
Access[color=blue][color=green][color=darkred]
> > > property tagged onto a Jet object. I've searched a bunch on google and[/color]
> > read[color=darkred]
> > > the help topic about four times (which is not an assertion that the[/color]
> > answer's[color=darkred]
> > > not in there). I've checked references and not noticed anything that[/color][/color]
> would[color=green][color=darkred]
> > > hold me back, but maybe I'm missing something there.
> > >
> > >
> > > I cribbed some test code that works just fine in 97 but won't work in[/color]
> > 2002,[color=darkred]
> > > which is where I need it. This works for a query that has never had a
> > > description. I haven't run it on one that does, because I'm just[/color][/color][/color]
trying[color=blue]
> to[color=green][color=darkred]
> > > get this down first. When I run this:
> > >
> > > Sub setTheDesc()
> > > Dim MyQuery As QueryDef
> > > Dim myDB As Database
> > > Dim myProperty As Property
> > > Dim VarValue As Variant
> > >
> > > VarValue = "works here"
> > > Set myDB = CurrentDb
> > > Set MyQuery = myDB.QueryDefs("qry3")
> > > Set myProperty = MyQuery.CreateProperty("Description", dbText,[/color][/color][/color]
VarValue)[color=blue][color=green][color=darkred]
> > > Call MyQuery.Properties.Append(myProperty)
> > >
> > > End Sub
> > >
> > > I get a type mismatch error on the "Set myProperty..." line. The same[/color]
> > exact[color=darkred]
> > > code works fine in 97. What do I need to do to get this to work in[/color][/color][/color]
2002?[color=blue][color=green][color=darkred]
> > >
> > > Thanks for any help.
> > >
> > > Jeremy
> > > --
> > > Jeremy Wallace
> > > AlphaBet City Dataworks
> > >
http://www.ABCDataworks.com
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
