CreateProperty

[Jeremy Wallace]

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.CreateP roperty("Descri ption", dbText, VarValue)
Call MyQuery.Propert ies.Append(myPr operty)

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

[Terry Kreft]

Re: CreateProperty

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@A lphaBetCityData works.com> wrote in message
news:YPKcnZC89r anwi-iXTWc-g@speakeasy.net ...[color=blue]
> 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[/color]
read[color=blue]
> the help topic about four times (which is not an assertion that the[/color]
answer's[color=blue]
> 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[/color]
2002,[color=blue]
> 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.CreateP roperty("Descri ption", dbText, VarValue)
> Call MyQuery.Propert ies.Append(myPr operty)
>
> End Sub
>
> I get a type mismatch error on the "Set myProperty..." line. The same[/color]
exact[color=blue]
> 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
>
>
>[/color]

[Jeremy Wallace]

Re: CreateProperty

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.Propert ies.Append(myPr operty)

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
"Descriptio n" 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.Query Defs("qry9").Pr operties("Descr iption") = "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@mp s.co.uk> wrote in message
news:botobd$uvg $1@newsreaderg1 .core.theplanet .net...[color=blue]
> 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@A lphaBetCityData works.com> wrote in message
> news:YPKcnZC89r anwi-iXTWc-g@speakeasy.net ...[color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > 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[/color]
> read[color=green]
> > the help topic about four times (which is not an assertion that the[/color]
> answer's[color=green]
> > not in there). I've checked references and not noticed anything that[/color][/color]
would[color=blue][color=green]
> > 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=green]
> > 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[/color][/color]
to[color=blue][color=green]
> > 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.CreateP roperty("Descri ption", dbText, VarValue)
> > Call MyQuery.Propert ies.Append(myPr operty)
> >
> > End Sub
> >
> > I get a type mismatch error on the "Set myProperty..." line. The same[/color]
> exact[color=green]
> > 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
> >
> >
> >[/color]
>
>[/color]

[Jeremy Wallace]

Re: CreateProperty

Huh. Can't see my previous post, but I did just post something saying that I
didn't have to do any of this, all I needed to do was:
CurrentDb.Query Defs("qry9").Pr operties("Descr iption") = "blah"

Well it turns out that works in an Access 2002 database, but not in an
Access 2000 database opened with Access 2002. I don't have 2k, so I can't
test that. In the Access 2000 database, it did, of course, work to just
disambiguate the declarations.

Thanks, once again.

Jeremy

--
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message
news:botobd$uvg $1@newsreaderg1 .core.theplanet .net...[color=blue]
> 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@A lphaBetCityData works.com> wrote in message
> news:YPKcnZC89r anwi-iXTWc-g@speakeasy.net ...[color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > 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[/color]
> read[color=green]
> > the help topic about four times (which is not an assertion that the[/color]
> answer's[color=green]
> > not in there). I've checked references and not noticed anything that[/color][/color]
would[color=blue][color=green]
> > 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=green]
> > 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[/color][/color]
to[color=blue][color=green]
> > 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.CreateP roperty("Descri ption", dbText, VarValue)
> > Call MyQuery.Propert ies.Append(myPr operty)
> >
> > End Sub
> >
> > I get a type mismatch error on the "Set myProperty..." line. The same[/color]
> exact[color=green]
> > 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
> >
> >
> >[/color]
>
>[/color]

[Terry Kreft]

Re: CreateProperty

Jeremy,
re:-
CurrentDb.Query Defs("qry9").Pr operties("Descr iption") = "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
erObjTypeNotSup ported = 1100 + vbObjectError
End Enum
' *************** *************** ******
'

Function SetProperty( _
ObjType As AcObjectType, _
ObjName As String, _
PropName As String, _
PropValue, _
PropType As DAO.DataTypeEnu m, _
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_TYP E_NOT_SUPPORTED = "The object type is not supported"

Set loDB = CurrentDb

Select Case ObjType
Case acReport
Set loObj = loDB.Containers ("Reports").Doc uments(ObjName)
Case acQuery
Set loObj = loDB.QueryDefs( ObjName)
Case acModule
Set loObj = loDB.Containers ("Modules").Doc uments(ObjName)
Case acMacro
Set loObj = loDB.Containers ("Scripts").Doc uments(ObjName)
Case acForm
Set loObj = loDB.Containers ("Forms").Docum ents(ObjName)
Case acTable
Set loObj = loDB.TableDefs( ObjName)
Case Else
Err.Raise erObjTypeNotSup ported, , ERR_MSG_OBJ_TYP E_NOT_SUPPORTED
End Select

Set loProp = loObj.Propertie s(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.CreatePro perty(PropName, PropType, PropValue, DDL)
loObj.Propertie s.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(acQ uery, "Query1", "Descriptio n", "Aha", dbText, False)
Or
Call SetProperty(acQ uery, "Query1", "Descriptio n", "Aha", dbText)

To set the DDL option
--------------------------
Call SetProperty(acQ uery, "Query1", "Descriptio n", "Aha", dbText, True)


Terry


"Jeremy Wallace" <absolutejunk@A lphaBetCityData works.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.Propert ies.Append(myPr operty)
>
> 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
> "Descriptio n" 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.Query Defs("qry9").Pr operties("Descr iption") = "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@mp s.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@A lphaBetCityData works.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:YPKcnZC89r anwi-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.CreateP roperty("Descri ption", dbText,[/color][/color][/color]
VarValue)[color=blue][color=green][color=darkred]
> > > Call MyQuery.Propert ies.Append(myPr operty)
> > >
> > > 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]