Changing a query properties in code

[John Acocella]

I have saved a defined query in Access. What I want to do
is change a property value of this query at runtime using
VBA code and then execute it in code. The name of the
property I want to change is "Source Database". I was
thinking about using ADO or DAO to do this, but I can't
find a way to change a defined query's property value in
VBA. Is it possible to change a defined query's property
value in VBA? If so, how? Thanks.

 

[Elwin]

something like this should work...

Sub SetQryProperty(strQryName as String, _
strPropertyName as String, _
varNewSetting as Variant)
Dim qdf as DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strQryName Then
qdf.Properties(strPropertyName) = varNewSetting
Exit For
End If
Next qdf
Set qdf = Nothing
End Sub

You'd paste this sub procedure into a module and call it
in code like this...

SetQryProperty "qryName", "prpName", "prpSetting"


Don't forget to reference the DAO 3.6 Object Library.
Good luck.

 

[john acocella]

I tried it and it doesn't appear to work. The qdf.name
property is returning gibberish ex
~sq_cfrm_Stars~sq_cCombo17. I don't have any queries with
this name.

 

[john acocella]

Actually it did find the query, but when I pass
the "Source Database" property, I get an error
message "Property does not exist"

 

[Douglas J. Steele]

That's the query associated with Combo17 on frm_Stars. If you don't use an
already-existing query as the Rowsource, Access creates one, but hides it
from normal view.

 

[Douglas J. Steele]

Try SourceDatabase, without a space.

 

[Tim Ferguson]

Actually it did find the query, but when I pass
the "Source Database" property, I get an error
message "Property does not exist"
....

You need to read the help files on the DAO Properties collection. Not all
properties exist until you have something to put in them, and attempts to
read or write non-existent properties raise a trappable error. You can
intercept this error and create the property using the appropriate method
call. It's well documented with examples.

In any case, surely the source of data is part of the SQL text of the query
itself. Dont you have it in an IN clause?

HTH


Tim F

 

[john acocella]

I tried "SourceDatabase" and even tried other properties
such as "Description". I don't think this solution
works. Also when I enumerate through the properties
collection, it does not appear to be the query's
properties at all. For example qdf.Properties(1) shows up
as 3/23/2004 11:58:13 AM which isn't even a property that
shows up in the properties window of the query.

 

[Elwin]

Good news and bad news. I did a little research for you.
Turns out that "Source Database" is a setting on the
property sheet of the query that you can set, however it's
not a true 'property' of the QueryDef object.

Setting the property in the query design view simply
modifies the query's SQL by adding to the query's FROM
clause

eg) with property set;
FROM tblMyTable IN 'C:\MyDatabase.mdb'

without property set;
FROM tblMyTable

You could use the sub procedure I gave you earlier to
modify the query's SQL property.

strDbPath = "C:\MyDatabase.mdb"
strSQL = "SELECT tblMyTable.* " _
& "FROM tblMyTable IN '" & strDbPath & "'"
SetQryProperty "qryName", "SQL", strSQL

good luck.

 

[john acocella]

Thanks...I knew that setting this property appended the
SQL. I guess like you said you can't access defined query
properties via Querydef. If my memory serves me you can
change properites of a form via code ie "Record Source".
Don't understand why I can't change a query property.

If I write code to modify the SQL in code as you suggest,
then I might as well just copy and paste the SQL from the
Access window to VBA and just insert a variable in place
of the file location and get rid of the defined query
alltogether since I would be over writing the SQL using
your approach.

 

[Elwin]

You're correct in that you would be over writing the SQL,
but modifying the SQL property allows you to perpetually
reuse the same QueryDef rather than constantly deleteing
and re-creating it on the fly (which also works fine).
Seems there's never any ONE correct way. I'm glad you've
got it working.

 

[Doug Bell]

You can also create the SQl as a string and parse the DB source into the
string and then just execute the string against a database object:
Set db=current db()
stSQL = "SELECT tblUSR.ID " & _
"FROM [" & stSrcTbl & "].tblUsrTransactions AS tblUSR " & _
"LEFT JOIN tblDatTransactions ON " & _
"tblUSR.ID = tblDatTransactions.ID " & _
"WHERE (((tblDatTransactions.ID) Is Null));"

stSrcTbl ="C:\DBName.mdb"

db.Execute stSQL
set db=nothing

 

[TC]

(snip)

Thanks...I knew that setting this property appended the
SQL. I guess like you said you can't access defined query
properties via Querydef. If my memory serves me you can
change properites of a form via code ie "Record Source".
Don't understand why I can't change a query property.

You >can< change querydef properties. However, despite what the query grid
may say, "source database" is >not< a property of any querydef object. Look
up querydef object in online help, click the properties button, & you will
not see an entry for "source database".

Conversely, "recordsource" >is< a property of a form object. You >will< see
that property listed in online help, for the form object.

HTH,
TC