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" <ab**********@AlphaBetCityDataworks.com> wrote in message
news:n5********************@speakeasy.net...
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" <te*********@mps.co.uk> wrote in message
news:bo**********@newsreaderg1.core.theplanet.net. .. 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" <ab**********@AlphaBetCityDataworks.com> wrote in
message news:YP********************@speakeasy.net... OK, so I've been playing with this for too long, not getting it to
work. Seems pretty simple, and I'm sure I'm just klutzing some syntax,
but...
I need to update the description property of queries via code. I know
that the property doesn't exist unless it's been set, because it's an
Access property tagged onto a Jet object. I've searched a bunch on google and
read the help topic about four times (which is not an assertion that the
answer's not in there). I've checked references and not noticed anything that would 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
2002, 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
trying to 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,
VarValue) Call MyQuery.Properties.Append(myProperty)
End Sub
I get a type mismatch error on the "Set myProperty..." line. The same
exact code works fine in 97. What do I need to do to get this to work in
2002?
Thanks for any help.
Jeremy
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
