473,545 Members | 2,469 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update query Inner Join question

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.Entit y_ID =
seach3220.Entit y_ID SET tblEntity.Cat_I D = 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.
Nov 13 '05 #1
10 10511
I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entit y_ID =
search3220.Enti ty_ID SET tblEntity.Cat_I D = QryPrm("frmTool s", "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?
Nov 13 '05 #2

"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:O0******** *********@newss vr14.news.prodi gy.com...
I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entit y_ID = search3220.Enti ty_ID SET tblEntity.Cat_I D = QryPrm("frmTool s", "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".
Nov 13 '05 #3
> 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 strSomeSqlStrin g

and

db.Execute "qryQueryNa me"

?

Thanks for the help!
Nov 13 '05 #4

"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:S0******** *********@newss vr14.news.prodi gy.com...
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_CreateQuer y()

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 = "ShowTblInvento ry"
qd.SQL = SQLString

qds.Append qd

Set qd = Nothing
db.Close
Set db = Nothing
End Sub
Nov 13 '05 #5
> 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"?
Nov 13 '05 #6

"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:oj******** *********@newss vr14.news.prodi gy.com...
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.

Nov 13 '05 #7
deko wrote:
I figured it out:

UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entit y_ID =
search3220.Enti ty_ID SET tblEntity.Cat_I D = QryPrm("frmTool s", "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.

Nov 13 '05 #8
> 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.OpenRecor dset

Does this mean I can do this:

Set rst = qItem.OpenRecor dSet?

Thanks for the help!
Nov 13 '05 #9
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 = "_qryQueryN ame" 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.OpenRecor dset

I suppose I can always use:

db.Execute "_qryQueryN ame"
Set rst = db.OpenRecordse t("_qryQueryNam e")

Thanks again for the help!
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2507
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an Update statement. I have a sample code to reproduce my problem. To simplify the scenario I am trying to use Order related tables to explain a...
9
4337
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user...
4
2723
by: Andy Proctor | last post by:
I hope there is an answer out there.... I have a simple database structured like this (non relevant tables and fields omitted) Members table memberID memberFname memberLname memberNokID
1
1742
by: septen | last post by:
Hi, I have a select query of 12 tables. The SQL code is as follows: SELECT OptChannelRangeAve.ChannelRangeMin, OptChannelRangeAve.ChannelRangeMax, OptChannelRangeAve.SampleAveraging, OptChannelADcard.ChannelRangeAveID, OptChannelChannel.ADcardID, OptChannelNameChannel.ChannelEveryID, OptChannelNameChannel.ChannelID,...
4
11320
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET AddressDescription of Entity 456 = AddressDescription of Entity_ID 123 Address1 of Entity 456 = Address1 of Entity_ID 123 City of Entity 456 =...
1
1489
by: G Gerard | last post by:
Hello I am trying to update a table (TableB) using records from a second table (TableA)
2
1382
by: Antonio | last post by:
Can someone tell me why the following procedure updates ALL the records in the database with the field being updated for one record? private void updateRow(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings); SqlCommand updCommand = new...
8
2506
by: Richard | last post by:
Hello! I have this piece of SQL code: UPDATE a SET Field1 = c.Field1 FROM a INNER JOIN b ON a.GUID1 = b.GUID1 INNER JOIN c ON b.GUID2 = c.GUID2 WHERE c.Type = 1
5
10253
by: Ferasse | last post by:
Hi, I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get... Right now, I'm working on a database that stores contractual information. One of the form that I created is based on a query that links several tables. When I try to insert a record in this query-based form, I can only update the fields that...
3
3939
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID = ?
0
7490
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7425
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7682
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7780
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6009
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5069
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3479
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1911
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1037
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.