468,249 Members | 1,485 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,249 developers. It's quick & easy.

CreateProperty

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

Nov 12 '05 #1
4 14237
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

Nov 12 '05 #2
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


Nov 12 '05 #3
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.QueryDefs("qry9").Properties("Descriptio n") = "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" <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



Nov 12 '05 #4
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



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Farek | last post: by
1 post views Thread by mahnovetsky | last post: by
3 posts views Thread by Adonis Walmsley-McCarthy | last post: by
6 posts views Thread by Richard | last post: by
3 posts views Thread by chris | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.