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. 10 10529
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?
"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".
> 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!
"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
> 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"?
"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.
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.
> 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!
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 little better the tables i have to work with.
|
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 for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
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
|
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,
OptChannelNameChannel.NameID, OptChannelNameChannel.SignAnalID,
|
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 = City of Entity_ID 123
| |
by: G Gerard |
last post by:
Hello
I am trying to update a table (TableB) using records
from a second table (TableA)
|
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 SqlCommand();
|
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
|
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 are used to build relations with other tables.
|
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 = ?
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
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
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |