A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Changing Query Defs in VBA



 
 
Thread Tools Display Modes
  #21  
Old January 29th, 2007, 02:43 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Changing Query Defs in VBA

"'69 Camaro" AM
wrote in :

I've encountered occasions (rarely, mind you) where the last chunk
of record changes hadn't been written to disk yet and the next
query errored because it couldn't find the data that I was
expecting to be there already. That's the problem with a desktop
database engine as opposed to a client/server database engine.
The client/server does these operations in memory and has a
snapshot of the data that includes the results of the previous
data manipulations and uses that snapshot for subsequent
operations which read from the snapshot, not from the disk, while
the database engine writes to disk the earlier transactions. (I'm
not sure I'm explaining this very clearly. Sorry.) The desktop
database engine doesn't have a snapshot to read from and always
reads what's already been written to disk. The lack of ability to
do these "will be done" operations in memory is one of the reasons
one sees lightning speed in client/server database engines and Jet
seems to take its sweet time.


I've never seen such a thing.

Jet *does* use memory, in the form of temp files on disk. And Jet
keeps track of which are the currently valid data pages, so it knows
that a data page in the MDB file has been superseded by a data page
in the temp file. Surely this is kept track of in an in-memory list,
which all query processing will access in order to get the latest
data. And it shouldn't matter if it's actually been written to disk
or is just in the disk cache queue to be written, since the disk
cache should also be keeping its own list of what is to be read from
disk and what sectors from the cache (because they haven't yet been
flushed to disk).

I would suggest that the problem you're described is caused by a
number of possible problems:

1. using a different connection/database variable for the second
query than for the first.

2. running them in separate transactions without commiting the first
before running the second.

These seem to me like the kinds of problems that could lead to a
query not seeing the updates made in the immediately previous query.
I guess the one thing that *may* happen is something about the disk
writes that DoEvents allows, but I think that's very unlikely, as
disk I/O should be running at a level much higher than Access
itself, so ought to happen anyway. Perhaps it frees up something in
Access itself to communicate with the OS in a way that makes the
write happen.

In all my years of programming in Access, I've never encountered
such a problem, and I've written lots of code where queries are
executed successively and later ones depend on writes that happened
in the earlier ones. I'd be interested to know what conditions lead
to this problem, since I've never run onto it. Perhaps ADO is the
culprit (I don't use ADO)?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #22  
Old January 29th, 2007, 03:17 PM posted to microsoft.public.access
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Changing Query Defs in VBA



On Jan 29, 1:43 pm, "David W. Fenton"
wrote:
I've encountered occasions (rarely, mind you) where the last chunk
of record changes hadn't been written to disk yet and the next
query errored because it couldn't find the data that I was
expecting to be there already.


In all my years of programming in Access, I've never encountered
such a problem, and I've written lots of code where queries are
executed successively and later ones depend on writes that happened
in the earlier ones. I'd be interested to know what conditions lead
to this problem, since I've never run onto it. PerhapsADOis the
culprit (I don't useADO)?


Perhaps it's the vegetarians (I eat meat) g? Nice try but I've run a
fair few queries through ADO in my time and haven't experienced such
behaviour either. While it is not completely beyond my imagination, I
too would attribute this to something more straightforward but
overlooked at the time; Gunny is a reliable witness but we all make
mistakes. Of course, if someone can post some code/steps to
reproduce...

Jamie.

--


  #23  
Old January 29th, 2007, 04:33 PM posted to microsoft.public.access
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Changing Query Defs in VBA



On Jan 27, 1:07 pm, "David W. Fenton"
wrote:
There is only one context in which I've ever written to a QueryDef
in order to change it, and that was for a graph that was presenting
TOP N results, where N was choosable by the user. Since I couldn't
find any way to alter the graph's SQL properties, and because
there's no way to use a variable for N in TOP N queries, I had to
use a saved query and change its SQL.

I have never encountered any other cases where altering a saved
QueryDef was required.


One cannot parameterize the N in TOP N (just one of the many drawbacks
of this proprietary syntax) but you can use a standard SQL construct
e.g. using Northwind, to parameterize the 10 in this:

SELECT TOP 10 OrderDate, OrderID
FROM Orders
ORDER BY OrderDate DESC;

you could use this:

CREATE PROCEDURE TestProc (
arg_N INTEGER
)
AS
SELECT T1.OrderDate, T1.OrderID
FROM Orders AS T1
WHERE arg_N = (
SELECT COUNT(*)
FROM Orders AS T2
WHERE T1.OrderDate T2.OrderDate);

The problem with the above procedure is that Jet is extremely poor at
optimizing correlated subqueries; with much more than about 1000 rows
in the Orders table the performance of the above proc will start to
degrade significantly.

But what are we trying to achieve here? If Larry Linson Microsoft
Access MVP truly does use one query for each task then he would
presumably have N queries, one for each value of TOP N he
anticipates; that would give great performance but at a cost e.g. a
bloated schema that is harder to use than it need be.

The fact you quote the above TOP N as an exceptional case suggests
that like me, but unlike Larry it would seem, you regard a
parameterized query (a.k.a. a procedure) as the norm. I'd go further
and say that dynamic SQL (the term usually applied in the wider SQL
community and is pejorative - google it) should be avoided.

Jamie.

--


  #24  
Old January 30th, 2007, 03:40 AM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default Changing Query Defs in VBA

"Jamie Collins" wrote

But what are we trying to achieve here? If Larry
Linson Microsoft Access MVP truly does use one
query for each task then he would presumably have
N queries, one for each value of TOP N he antici-
pates; that would give great performance but at a
cost e.g. a bloated schema that is harder to use than
it need be.


The database applications I have done do not include many TOP N Queries, and
I don't recall a single case where the requirements called for multiple
Queries or multiple searches varying only in "N". The idea of saving
multiple queries varying only in "N" seems outlandish to me. On the other
hand, I didn't begin using SQL until sometime in the 1980s, so maybe I have
missed some important points.

Unless you are querying very large numbers of records, the performance
advantage of saved Queries versus dynamic SQL is not detectable by the user
at the keyboard in the Access environment.

The fact you quote the above TOP N as an exceptional
case suggests that like me, but unlike Larry it would seem,
you regard a parameterized query (a.k.a. a procedure) as
the norm. I'd go further and say that dynamic SQL (the
term usually applied in the wider SQL community and
is pejorative - google it) should be avoided.


Interestingly, the very first article retrieved by googling "dynamic SQL"
included comments about both the benefits and drawbacks of dynamic SQL, but
the lead-in to the drawbacks discussion qualified that with "not carefully
handled". Perhaps some in the "wider SQL community" exercise little or no
care in "handling" dynamic SQL -- that would be a good reason to regard it
as dangerous and a pejorative, or to exercise more care in hiring.

There may be other good reasons for avoiding dynamic SQL in the "wider SQL
community" but it has not been a stumbling block in my Access work, whether
individual databases, multiuser environment with MDB-JET, or Access clients
to server databases.

Generalizations from the "wider SQL community" are not necessarily
universally applicable, nor necessarily applicable in the Access world. In
my observation, those who speak of "the wider SQL community" are primarily
speaking about server databases, not file-server databases, and often aren't
aware of the differences.

Larry Linson
Microsoft Access MVP



  #25  
Old January 30th, 2007, 03:04 PM posted to microsoft.public.access
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Changing Query Defs in VBA



On 30 Jan, 02:40, "Larry Linson" wrote:

There may be other good reasons for avoiding dynamic SQL in the "wider SQL
community" but it has not been a stumbling block in my Access work, whether
individual databases, multiuser environment with MDB-JET, or Access clients
to server databases.


You seem to be having trouble imagining good reasons for avoiding
dynamic SQL, so try this. Say you have one Jet mdb and N number of
'front end' applications (Access, VB6, .NET, whatever) using the
database as a shared application store and each application has its
own 'local' version of some SQL code (i.e. it is held as text compiled
into the application etc) which each return the exact same resultset.
Consider the scenario where the table structure changes, requiring the
SQL code to be changed in order to return the same resultset. In this
scenario you must find the code in N number of applications and alter
it using 1 to N 4GL languages. Now consider a single application (e.g.
Access) that holds the same SQL multiple times within its VBA code;
you have the same maintenance issue i.e. finding and amending the
essentially the same code multiple times (and what if you miss
one...?) Both sceanarios can be ameliorated by storing the SQL code in
a database object as a VIEW (or perhaps a PROCEDURE, although I
recognise that that executing procedures while optionally passing
strongly-typed parameter values is not popular with Access 'purists'
because it doesn't fit well with the "always connected bound controls"
approach, and I commonly see names of front end Forms/controls hard-
coded into backend database PROCEDURES, which strikes me as lacking
the necessary level of code separation between front- and backends).
This way, the SQL code can be considered as part of the schemaas
indeed it should, IMO.

In
my observation, those who speak of "the wider SQL community" are primarily
speaking about server databases, not file-server databases, and often aren't
aware of the differences.


Likewise, IMO those who fixate on the physical implementation ("file
based", "fields and records", etc) are missing the point of a SQL
product (hint: the 'S' used to be for 'Standard').

Generalizations from the "wider SQL community" are not necessarily
universally applicable, nor necessarily applicable in the Access world.


No one said that ideas universally applicable or applicable in the
Access world but please do open your mind to the fact that some SQL
concepts and practises may have benefit in the Access world some of
the time.

Unless you are querying very large numbers of records, the performance
advantage of saved Queries versus dynamic SQL is not detectable by the user
at the keyboard in the Access environment.


First, more users consume the results of SQL than write SQL
themselves. Second, try thinking in terms of code maintence, code
reuse, coupling and cohesion, etc; Access development is not totally
unrelated to the general principles of good software engineering.
Perhaps, Larry, you always work alone and have no need to write code
that can be maintained by another coder, or write 'code for coders'
e.g. coding business object model using class modules for other coders
to use in their own code, etc (I think I know what might be coming
next: "In my experience people who work in teams/choose an OOP
approach in VBA are [aim low here]." g).

Jamie.

--


  #26  
Old January 30th, 2007, 08:43 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Changing Query Defs in VBA

"Larry Linson" wrote in
:

Interestingly, the very first article retrieved by googling
"dynamic SQL" included comments about both the benefits and
drawbacks of dynamic SQL, but the lead-in to the drawbacks
discussion qualified that with "not carefully handled". Perhaps
some in the "wider SQL community" exercise little or no care in
"handling" dynamic SQL -- that would be a good reason to regard it
as dangerous and a pejorative, or to exercise more care in hiring.


And many databases, such as SQL Server, will cache the optimization
of an ad hoc query so that if the same SQL statement is sent a gain,
it will use the temp optimization rather than recomputing it. With
MySQL, I use the ADODB add-in that caches recordsets and thus speeds
up performance a great deal (instead of writing frequent queries to
text files, I just let ADO handle caching it, which works very well,
because the data often doesn't change).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #27  
Old January 31st, 2007, 05:13 AM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default Changing Query Defs in VBA

"Jamie Collins" wrote

First, more users consume the results of SQL than write SQL
themselves. Second, try thinking in terms of code maintence, code
reuse, coupling and cohesion, etc; Access development is not totally
unrelated to the general principles of good software engineering.


The word "access" in the name of this newsgroup is there for a reason... the
subject is Microsoft Access, not OOP programming, not other client or
front-end apps. I've been a computer programmer since before someone
invented the somewhat pretentious title of "software engineer". Access can
be a nice client for many server databases, so Access as a client to server
databases are generally on-topic.

Perhaps, Larry, you always work alone and have no need to write code
that can be maintained by another coder, or write 'code for coders'
e.g. coding business object model using class modules for other coders
to use in their own code, etc (I think I know what might be coming
next: "In my experience people who work in teams/choose an OOP
approach in VBA are [aim low here]." g).


You are probably correct that my view is not the same as yours... I've only
been working in teams, sometimes very large, sometimes small, since the late
1950s. Prior to that time, all my work had been "alone", and, it is true,
that in the following years, some of my work has been "alone". Not only
that, I wasn't involved in managing teams and establishing management and
development procedures and guidelines until sometime in the 1970s.

As you, no doubt, are aware, VBA supports only limited OOP. Neither Access
nor Jet are likely to be tools aimed at the enterprise architecture with
which you seem to be most familiar. OOP does have its place, and one place
for it is in code-intensive environments (which is often the environment for
enterprise applications). A place where it may not fit so well is in the RAD
environment, where a typical application can often be developed by
pointing-and-clicking one's way to a friendly UI and then sprinkling just
enough code behind the UI to make it work and work smoothly (which, often,
is surprisingly little code). But, even there, there are times that it is
useful. And, we often point out that even Access developers who never write
their own classes are Object Oriented Programmers, Consumer-side.

I do know that many, as you may also, fervently believe that "if it's not
enterprise, it's not development" but there are many more individual
applications, and modest-sized multiuser and client-server applications
than enterprise applications. In my later years in the corporate
environment, I had a colleague whose motto was, "if it isn't water-cooled,
it doesn't compute" -- a slightly earlier take on a similar view.

But, just so you won't be totally disappointed: In my experience, those
single-user and modest-group applications stand a significantly better
chance of being successful, on the average, than enterprise applications; I
suspect it is because far too many approach vast projects with half-vast
ideas, or, to put it another way, because the difficulty of managing a
project increases geometrically, not linearly, with its size.

Larry Linson
Microsoft Access MVP


  #28  
Old January 31st, 2007, 11:45 AM posted to microsoft.public.access
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Changing Query Defs in VBA

On 31 Jan, 04:13, "Larry Linson" wrote:
The word "access" in the name of this newsgroup is there for a reason... the
subject is Microsoft Access, not OOP programming, not other client or
front-end apps

Access can
be a nice client for many server databases, so Access as a client to server
databases are generally on-topic.


Yawn. This line of enquiry is a bit of a red herring but if we must
(again groan)...

There are no newsgroup with the word "jet" in the name and there are
no Microosft Jet MVPs. Using Jet as an application data store, where
the application (VB6, Access - yes! -, sub main, OOP, managed code,
unmanaged code, whatever) is the 'client' and the MDB is the
'server' (yes, I know Jet is file-based; the term 'server' is
notional, not physical), remains a common and valid approach. In other
words, Jet can be a nice 'server' database for many 'client'
applications (even though there are nicer 'desktop' versions of *real*
server databases also available for free). Therefore, Jet-no-Access is
also on-topic for these groups.

I think I know what might be coming
next: "In my experience people who work in teams/choose an OOP
approach in VBA are [aim low here]."


You are probably correct that my view is not the same as yours

the somewhat pretentious title of "software engineer"


You got me: I didn't anticipate you aiming low at that section of the
Access community! For me, someone can describe their role however they
like: software engineer, developer, programmer, coder, MVP, whatever
(remember that titles are more often given than chosen); what I think
is important is being 'professional', meaning one adopts a 'best
practice' approach, is open-minded to new (to them) ideas and
respectful of other people. It doesn't surprise me that you seek to be
judgemental and condescending about someone who is 'different' from
yourself, Larry, but coming from an MVP (where the 'P' is supposed to
stand for 'Proessional') I am disappointed. BTW I don't think you can
use your 'gloves-off Larry speaking for himself and not as an MVP'
disclaimer when you are posting in a newsgroup in which you earned
your MVP status.

there are many more individual
applications, and modest-sized multiuser and client-server applications
than enterprise applications


I don't know how you are classifying 'enterprise applications' but
I've often seen cases where the Jet data store of an individual/modest-
sized Access application has proven useful to users within the same
organisation, whether they are users of the Access application itself
or otherwise, the most common route being to query the database from
within Excel in order to conduct further 'business intelligence' (BI)
from there (don't get too het up about the 'pretentious' BI moniker;
just think about someone wanting to print a nice, colourful graph).
Crippling an otherwise useful VIEW or PROECDURE by embedding something
Access-only (VBA6 function such as Replace, Access function such as
NZ, UDF function such as Concatenate, etc), supporting only one
flavour of ANSI Query Mode (e.g. using the * wildcard character in a
Validation Rule), implenting data constraints in the Access front end,
etc is IMO short-sighted at best.

Jamie.

--



  #29  
Old January 31st, 2007, 11:56 AM posted to microsoft.public.access
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Changing Query Defs in VBA

On 30 Jan, 19:43, "David W. Fenton"
wrote:
many databases, such as SQL Server, will cache the optimization

of an ad hoc query so that if the same SQL statement is sent a gain,
it will use the temp optimization rather than recomputing it.


Sure, from that perspective there is no difference between a VIEW and
the equivalent ad hoc query. However, there are still advantages to
having the query stored in a . I used the example of code maintenance
(the Proliferation Avoidance Rule) but there are others, especially in
products such as SQL Server that support WITH CHECK OPTION and INSTEAD
OFtriggers. See:

A View Review
by Craig S. Mullins
http://www.dbazine.com/db2/db2-mfart...ins-viewreview

Another Use for Views
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko28

Jamie.

--


  #30  
Old January 31st, 2007, 03:03 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Changing Query Defs in VBA

"Jamie Collins" wrote in
ups.com:

On 30 Jan, 02:40, "Larry Linson" wrote:

There may be other good reasons for avoiding dynamic SQL in the
"wider SQL community" but it has not been a stumbling block in my
Access work, whether individual databases, multiuser environment
with MDB-JET, or Access clients to server databases.


You seem to be having trouble imagining good reasons for avoiding
dynamic SQL, so try this. Say you have one Jet mdb and N number of
'front end' applications (Access, VB6, .NET, whatever)


A ridiculous circumstance that hardly ever happens. For that
scenario, it would make *no* sense to put the data in a Jet MDB,
while it would make perfect sense to put the data in SQL Server,
using views and stored procedures in common for all the apps.

In any event, if you're doing that kind of development, it ought to
have an abstraction layer between the front end (in whatever format)
and the back end (in whatever db engine) precisely because that's
one of the best ways to handle multiple front ends to the same data
store.

But that's a very unrealistic *Access/Jet* scenario.

using the
database as a shared application store and each application has
its own 'local' version of some SQL code (i.e. it is held as text
compiled into the application etc) which each return the exact
same resultset. Consider the scenario where the table structure
changes, requiring the SQL code to be changed in order to return
the same resultset. In this scenario you must find the code in N
number of applications and alter it using 1 to N 4GL languages.
Now consider a single application (e.g. Access) that holds the
same SQL multiple times within its VBA code; you have the same
maintenance issue i.e. finding and amending the essentially the
same code multiple times (and what if you miss one...?)


I've never had many problems with this. First off, Speed Ferret does
a darned good job of fixing such issues, but mostly, I don't change
field names after an app has been built, simply because it causes
maintenance issues. So, for all the dynamic SQL I use, it's pretty
easy to avoid issues with this. And there are other ways to fix it,
such as replacing base source tables with stored queries of the same
name that alias the underlying data to be consistent with the code.
That's usually not something I'd do as a long-term solution, but
it's a great way of porting existing code quickly to new
requirements.

Both sceanarios can be ameliorated by storing the SQL code in
a database object as a VIEW (or perhaps a PROCEDURE, although I
recognise that that executing procedures while optionally passing
strongly-typed parameter values is not popular with Access
'purists' because it doesn't fit well with the "always connected
bound controls" approach, and I commonly see names of front end
Forms/controls hard- coded into backend database PROCEDURES, which
strikes me as lacking the necessary level of code separation
between front- and backends). This way, the SQL code can be
considered as part of the schemaas indeed it should, IMO.


You seem to be under the impression that Larry and I are advocating
no stored querydefs, whereas what I've been arguing (I'll let Larry
speak for himself) is that there's little need to edit QueryDefs in
code. That, of course, has exactly the same problems as dynamic SQL
for the scenarios you're talking about, so doesn't really address
*any* of the issues you've raised above.

In
my observation, those who speak of "the wider SQL community" are
primarily speaking about server databases, not file-server
databases, and often aren't aware of the differences.


Likewise, IMO those who fixate on the physical implementation
("file based", "fields and records", etc) are missing the point of
a SQL product (hint: the 'S' used to be for 'Standard').


The S has always stood for "Structured" so far as I'm aware.

Generalizations from the "wider SQL community" are not
necessarily universally applicable, nor necessarily applicable in
the Access world.


No one said that ideas universally applicable or applicable in the
Access world but please do open your mind to the fact that some
SQL concepts and practises may have benefit in the Access world
some of the time.


....And others may be completely irrelevant.

Unless you are querying very large numbers of records, the
performance advantage of saved Queries versus dynamic SQL is not
detectable by the user at the keyboard in the Access environment.


First, more users consume the results of SQL than write SQL
themselves. Second, try thinking in terms of code maintence, code
reuse, coupling and cohesion, etc; Access development is not
totally unrelated to the general principles of good software
engineering. Perhaps, Larry, you always work alone and have no
need to write code that can be maintained by another coder, or
write 'code for coders' e.g. coding business object model using
class modules for other coders to use in their own code, etc (I
think I know what might be coming next: "In my experience people
who work in teams/choose an OOP approach in VBA are [aim low
here]." g).


I think you're mistaking our position for one that rejects *all*
saved queries, whereas what we're rejecting is the insistence on
using stored QueryDefs for performance reasons (and thus, the
necessity to edit them in code). The performance reasons don't
justify editing the stored queries in code, in my opinion (and this
appears to me to be what Larry has clearly argued, as well).

This has little or nothing to do with the issues of maintenance,
because I (and, I suspect, Larry) use stored queries for some
purposes and then often use those stored queries as recordsources
for forms and reports, and apply WHERE clauses with the default
openform/report arguments, as well as occasionally altering the SQL
in the open/load events of the objects themselves.

For data manipulation that doesn't involve presentation in a UI, it
is often the case that I use pure dynamic SQL, however, that is not
uncommonly based on saved queries instead of directly on tables.

But I never need to edit those stored queries for the purposes of
changing criteria -- and that's the usual justification for editing
stored QueryDefs (for performance reasons).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.