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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|