Update query Inner Join question

[deko]

I've tried each of the below, but no luck.

UPDATE tblEntity As tbl INNER JOIN search3220 As qry ON tbl.Entity_ID =
qry.Entity_ID SET tbl.Cat_ID = 289;

UPDATE tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
seach3220.Entity_ID SET tblEntity.Cat_ID = 289;

The only issue I can imagine aside from syntax is that Entity_ID is not
unique in the query. tblEntity will only have one Entity_ID of 1700, for
example, while search3220 may have several Entity_IDs of 1700. Is this the
problem? Or is it a syntax issue?

Thanks in advance.

[deko]

I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm("frmTools", "frmCatAdm",
"cbxNewCat");

Jet wants the DISTINCTROW statement for some reason...

The only issue now is that query search3220 is created dynamically on the
fly - it could be search2110 or whatever - I don't know what it is until
runtime. I could build a string with code and use something like
"db.Execute strSql" - but I need the performance of a compiled query.

Is there anyway to search the qureyDefs collection and insert the query name
into a compiled query, something similar to what I'm doing with the QryPrm
function?

[Chris2]

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:O0*****************@newssvr14.news.prodigy.co m...

I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID = search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm("frmTools", "frmCatAdm", "cbxNewCat");

Jet wants the DISTINCTROW statement for some reason...

The only issue now is that query search3220 is created dynamically on the
fly - it could be search2110 or whatever - I don't know what it is until
runtime. I could build a string with code and use something like
"db.Execute strSql" - but I need the performance of a compiled query.

Is there anyway to search the qureyDefs collection and insert the query name into a compiled query, something similar to what I'm doing with the QryPrm
function?

Here is a barebones example of how to iterate through the QueryDef
collection.

This displays all Query names and SQL in the VBA IDE Immediate Window.
Public Sub ShowQueryDefs()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = CurrentDb()
Set qds = db.QueryDefs

For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd

Set qd = Nothing
db.Close
Set db = Nothing

End Sub
To do what you need to do, modify the above to check each SQL string for
whatever it is you're looking for, then dynamically assemble a new SQL
string containing whatever it is you need, and then use "qd.sql =
SQLString".

[deko]

> Here is a barebones example of how to iterate through the QueryDef

collection.

This displays all Query names and SQL in the VBA IDE Immediate Window.
Public Sub ShowQueryDefs()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = CurrentDb()
Set qds = db.QueryDefs

For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd

Set qd = Nothing
db.Close
Set db = Nothing

End Sub
To do what you need to do, modify the above to check each SQL string for
whatever it is you're looking for, then dynamically assemble a new SQL
string containing whatever it is you need, and then use "qd.sql =
SQLString".

So, basically, I just create a QueryDef object using an SQL string - which
contains the name of the dynamically-created query. This sounds reasonable.
But I'm wondering if the overhead of doing this is worth whatever
performance gains a compiled query will provide.

Is there much of a performance difference between:

db.Execute strSomeSqlString

and

db.Execute "qryQueryName"

?

Thanks for the help!

[Chris2]

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:S0*****************@newssvr14.news.prodigy.co m...

Here is a barebones example of how to iterate through the QueryDef
collection.

This displays all Query names and SQL in the VBA IDE Immediate Window.
Public Sub ShowQueryDefs()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = CurrentDb()
Set qds = db.QueryDefs

For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd

Set qd = Nothing
db.Close
Set db = Nothing

End Sub
To do what you need to do, modify the above to check each SQL string for
whatever it is you're looking for, then dynamically assemble a new SQL
string containing whatever it is you need, and then use "qd.sql =
SQLString".

So, basically, I just create a QueryDef object using an SQL string - which
contains the name of the dynamically-created query. This sounds

reasonable.

No. The SQLString contains the SQL.

The SQL property of the QueryDef object contains SQL.

The Name property of the QueryDef object contains the Query's name.
If you want to create entirely new QueryDefs, use:

Public Sub Exam_CreateQuery()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef
Dim SQLString As String

Set db = CurrentDb()
Set qds = db.QueryDefs
Set qd = New QueryDef

SQLString = "SELECT * FROM tblInventory;"

qd.Name = "ShowTblInventory"
qd.SQL = SQLString

qds.Append qd

Set qd = Nothing
db.Close
Set db = Nothing
End Sub

[deko]

> No. The SQLString contains the SQL.

Yes, but the query name is part of the SQL.

The SQL property of the QueryDef object contains SQL.

So I can change the SQL property of an existing QueryDef object? I didn't
know I could that. I'd think this would be more efficient than creating a
new QueryDef, and also more efficient than assembling a string with code and
running it with "db.Execute strSqlstring" In fact, why not just have one
QueryDef object and simply change the SQL property whenever needed?

For example, there could be a thousand situations where I need to create an
SQL string based on user selection or some control setting, and I just
update the SQL property of a single QueryDef object? And this is faster
than "db.Execute strSqlstring"?

[Chris2]

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:oj*****************@newssvr14.news.prodigy.co m...

No. The SQLString contains the SQL.
Yes, but the query name is part of the SQL.

Oh. I understand now. You meant the QueryDef name being added to the SQL
itself. Sorry.

The SQL property of the QueryDef object contains SQL.
So I can change the SQL property of an existing QueryDef object? I didn't

Yes.
know I could that. I'd think this would be more efficient than creating a
new QueryDef, and also more efficient than assembling a string with code and running it with "db.Execute strSqlstring" In fact, why not just have one
QueryDef object and simply change the SQL property whenever needed?
I believe that when the SQL property changes, Access must recompile it.
There is no way I know of (which doesn't mean there isn't one) to avoid
recompilation/query-optimization when the SQL of a QueryDef changes.

For example, there could be a thousand situations where I need to create an SQL string based on user selection or some control setting, and I just
update the SQL property of a single QueryDef object?
Yes.
And this is faster than "db.Execute strSqlstring"?
It's on a case by case basis. Access's tools for assessing query execution
costs aren't the best, IMO. One query you run will be quick regardless of
how it's initiated inside Access, another will be slow, also regardless of
how its initiated.

The cost of compilation of a single QueryDef is negligible, but if you put
that inside a loop and recompile over and over, heck, I don't know, I've
never tried anything like that. I'd go out of my way to avoid it. Changing
them that many times would imply executing them that many times, and the
cost would be huge. If I were forced to do it, it would be for queries that
returned single rows off unique indexed columns (preferably the Primary Key)
without any aggregation.

[Salad]

deko wrote:

I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm("frmTools", "frmCatAdm",
"cbxNewCat");

Jet wants the DISTINCTROW statement for some reason...

The only issue now is that query search3220 is created dynamically on the
fly - it could be search2110 or whatever - I don't know what it is until
runtime. I could build a string with code and use something like
"db.Execute strSql" - but I need the performance of a compiled query.
What performance hit would you take? .1 second? .5 seconds?

The only performance hit I see is calling QryPrm(). Not sure what it
does internally but if your query is slow, that's your bottleneck.
Is there anyway to search the qureyDefs collection and insert the query name
into a compiled query, something similar to what I'm doing with the QryPrm
function?
Chris supplied the answer there. You store the SQL to a query.

[deko]

> The cost of compilation of a single QueryDef is negligible, but if you put

that inside a loop and recompile over and over, heck, I don't know, I've
never tried anything like that. I'd go out of my way to avoid it. Changing them that many times would imply executing them that many times, and the
cost would be huge. If I were forced to do it, it would be for queries that returned single rows off unique indexed columns (preferably the Primary Key) without any aggregation.

I don't have any loops in mind, but I do have several modules that use code
to assemble SQL strings - complex Update and Select queries with multiple
joins and parameters from form controls - which seem to run pretty slowly
when executed with "db.Execute strSqlString".

Based on your suggestion, I'm considering this:

Dim db as DAO.Database
Dim qItem As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs
For Each qItem In qdf
If qItem.Name = strQry Then Set qItem.SQL = strSql
qItem.Execute
Next qItem

This would allow me to dynamically create both the query name and the Sql
string on the fly with code, and still get the performance of a compiled
query. Does this sound correct?

But I have over 300 queries - it seems inefficient to have to loop through
all of them to get the one I want if I know the name of the query. Can the
above loop be improved if I know the name of the qItem I'm looking for?

Also - Intellisense shows an OpenRecordset method for qItem:

qItem.OpenRecordset

Does this mean I can do this:

Set rst = qItem.OpenRecordSet?

Thanks for the help!

[deko]

This seems to be working:

Dim db As DAO.Database
Dim qItem As DAO.QueryDef
Dim qdf As DAO.QueryDefs

Set db = CurrentDb
Set qdf = db.QueryDefs

For Each qItem In qdf
Debug.Print qItem.Name
If qItem.Name = "_qryQueryName" Then
qItem.SQL = strSql
Debug.Print "Setting SQL property for " & qItem.Name
Exit For
End If
Next

Prefixing the query name with "_" puts it first in the list of qItems, so
the loop only iterates once, instead of 300+ times.

I'm still having trouble with:

qItem.Execute
qItem.OpenRecordset

I suppose I can always use:

db.Execute "_qryQueryName"
Set rst = db.OpenRecordset("_qryQueryName")

Thanks again for the help!

[Chris2]

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:dl******************@newssvr21.news.prodigy.c om...

I don't have any loops in mind, but I do have several modules that use code to assemble SQL strings - complex Update and Select queries with multiple
joins and parameters from form controls - which seem to run pretty slowly
when executed with "db.Execute strSqlString".
Each table added to a query slows it down. As the complexity goes up, the
speed goes down. I'm sure you already know that part, what I'm trying to
say is I don't think you'll find using db.execute "SELECT . . ." and
db.execute existing_QueryDef will have nearly as big an impact as the actual
complexity of the query itself.

Unfortunately, as nice a critter as Access is, it isn't the smartest db
manager on the block, and it will likely never make the best optimization
decisions.


Based on your suggestion, I'm considering this:

Dim db as DAO.Database
Dim qItem As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs
For Each qItem In qdf
If qItem.Name = strQry Then Set qItem.SQL = strSql
qItem.Execute
Next qItem

This would allow me to dynamically create both the query name and the Sql
string on the fly with code, and still get the performance of a compiled
query. Does this sound correct?

But I have over 300 queries - it seems inefficient to have to loop through
all of them to get the one I want if I know the name of the query. Can the above loop be improved if I know the name of the qItem I'm looking for?

Also - Intellisense shows an OpenRecordset method for qItem:

qItem.OpenRecordset

Does this mean I can do this:

Set rst = qItem.OpenRecordSet?
Yes. rst needs to be created first, "dim rst as DAO.Recordset" (and so
on). What you get is a "recordset" that can be iterated through. Sort of a
virtual datasheet (*sort of*). You can use the recordset methods, .edit,
..add, .update, etc. on it. Recordsets are a *big* discussion. (There are
also ADO and RDO versions, as well, which is why you *always* prefix with
the object model you are drawing on, otherwise, problems will occur.)

Since you have Access installed (hopefully 2000+), you also probably have
the following files, which I find invaluable.

DAO360.CHM <--Recordsets are defined in here (along with the rest of
Direct Access Objects 3.6).
JETSQL40.CHM <--SQL for Access is in here.
VBLR6.CHM <--VB 6.0 is in here.

I really can't live without these. :) They are my #1 Resource (followed
by Google's archive of these newsgroups, www.mvps.org/access/, and several
of the better books). Look through them, they will do you good. The search
function in them for A2000 is hopelessly useless, use the index instead.

DNJET.CHM (Microsoft Jet Database Engine Programmer's Guide), is also very
interesting, but unfortunately, it's for JET 3.5. It still has some useful
info in it.
Thanks for the help!

You are welcome. :)