Using Dynamic SQL versus Parameter Query

[Banana]

Inspired by this thread, which I didn't really want to hijack, I figured I'd put the question to everyone-

What are your personal preferences for creating queries that may have variable data in it, by writing out a SQL in VBA and executing it on the fly, or accessing a parameter query and opening a recordset with that query, and why?

I generally find myself preferring parameter queries only because I don't want to worry too much about SQL syntax when I'm writing out VBA codes, and because I believe that when a query is saved, either as a object in database container or as a data source for any forms or controls, Jet creates an execution plan, even for parameter queries with unknown variables until runtime, whereas writing SQL in VBA would offer Jet no chance to optimize at all, but I could be wrong about that.

 

[georgedwilkinson]

Thanks for bringing this up. It's been bugging me for 2 years.

You know I'm interested in this topic but don't have an answer. Anyone else?

How 'bout you MVPs?

 

[LPurvis]

Hello

(In the following diatribe I'll largely refer to saved query definitions as just "queries").

There are pros and cons of course - but to my mind it largely comes down to maintainability of an application.

Some developers prefer SQL in VBA - and indeed will use little else if they can avoid it.
It offers trivial concatenation of (what would otherwise be) parameter values. It's inherently tied to the task at hand (often a hinderance IMO). Its formatting is fixed! (Now that *is* good ;-)


The problem I have with it is one of transparency.
When you look at an application - you're drawn to table structure first (to understand the database). Queries (to see how they relate) and then forms/reports and VBA to see how it's manipulated and represented.

Queries of some sort are almost are fairly inevitable in an application.
There may be forms and reports which are based upon them. List controls - as well as code objects.
We normalise our data out into tables. Queries let us get back to something meaningful.

Removing the logic of the saved query definitions and placing that *all* into VBA makes familiarisation with the application that bit slower.
More than that though - a single query definition can be used by many objects.
Several forms, reports, lists as well as recordsets.
If something changes in the db schema you change that core query and you're a long way towards updating all your data objects.
You might base other queries on these core queries.
You might use them directly in a variety of locations - using only the columns you need at that time.
But application maintainability and transpacency is simplified.

To an extent the same applies to using SQL statements in forms and listcontrols.
These are saved anyway as hidden query definitions, compiled just the same, execution plans saved just the same. But are hidden, hence unable to be trivially shared among other objects.

A SQL statement created on the fly isn't, of course, saved anywhere - but it does still generate a temporary querydef. (Jet needs to do this internally to execute the request).
So although there isn't the stored execution plan - Jet still optimises the data request. It's the engines responsibility to do so. There's a bit difference between executing a plan for optimising a data request and having saved one. Generally speaking - the calculation time to generate a plan is a tiny fraction of the time required for the actual data request itself.

It can be argued that since there is no saved plan that the newly generated plan can be more appropriate based on the current table statistics at the time rather than when the plan was created.
(Jet has a variety of joining stategies which it will use for one case or another - often depending upon indexes present but also the amount of data in the tables at the time).
(Of course, as we all know, there are ways to reset the statistics and stored plans - compacting being the most obvious).


There are, naturally, times when a VBA constructed statement is advantageous.
Search forms are a time when I prefer to do so.
We can create parameter queries - and even, by hook or by crook, make those parameters optional.
But it's much easier to do so when generating VBA on the fly *and* to make that query optimisable using available indexes.
Once you start fudging query definitions to include
WHERE (FieldName = [ParamValue] Or [ParamValue] Is Null)
you go losing index efficiency.
Whereas the old
If Not Isnull(Me.txtFieldName) Then
strSQL = strSQL & " AND FieldName = " & Me.txtFieldName
End If
allows you to sidestep that. The hit of the non-saved execution plan can go from a slight disadvantage to a useful thing to have.

This can lead on to the formatting issue I mentioned earlier.
When Access parses the SQL entered as a query it can reformat it. Losing our carefully laid out indenting and changes the syntax of subqueries back to the old pre Jet 4 style. :-s
Storing SQL in local tables is a good way to avoid this.
It can be thought of as a hybrid betweent the two methods suggested in this thread.
You keep your formatting. You can implement parameters or placeholders for substitution in code. You can even split the statement out into clauses (Select, From, Where, GroupBy, Having, Order). This can be very useful.
The primary use of such table SQL data is, of course, going to be from code - but it's also a reasonably central repository for doing so.

Ummm... in summary?
Horses for courses.
As with so many things in development - I think all ways of using queries have their place.
To use either one exclusively would be just limiting IMHO.

Cheers!

 

[twoplustwo]

Hmmm I use something like the below fairly often but I have no idea if it is correct or not

Private Sub cmbDFlog_Click() 'Filter list by DF team member
 
Dim sSQL As String 'query string
Dim sTeamMember As String 'df member
 
sTeamMember = Me.cmbDFlog.Column(1)
'Statement to be inserted into the rowsource
 
sSQL = "SELECT tblDfOwner.DF_Name,"
sSQL = sSQL & " tblCustomers.CustomerID,"
sSQL = sSQL & " tblCustomers.CompanyName,"
sSQL = sSQL & " tblCustomers.SiteName,"
sSQL = sSQL & " tblLinesOfBusiness.LineOfBusinessTitle,"
sSQL = sSQL & " tblCustomers.DataSetID,"
sSQL = sSQL & " tblRelationshipManager.RelationshipManagerName,"
sSQL = sSQL & " tblCustomers.ForecastOK"
sSQL = sSQL & " FROM tblDfOwner"
sSQL = sSQL & " INNER JOIN ((tblCustomers"
sSQL = sSQL & " LEFT JOIN  tblLinesOfBusiness"
sSQL = sSQL & " ON tblCustomers.LOBID = tblLinesOfBusiness.LineOfBusinessID)"
sSQL = sSQL & " LEFT JOIN tblRelationshipManager"
sSQL = sSQL & " ON tblCustomers.RMID = tblRelationshipManager.RelationshipManagerID)"
sSQL = sSQL & " ON tblDfOwner.DF_ID = tblCustomers.DF_ID"
 
If sTeamMember <> "ALL" Then 'If ALL ignore WHERE statement
    sSQL = sSQL & " WHERE tblDfOwner.DF_Name = '" & [b]sTeamMember[/b] & "'"
End If
 
sSQL = sSQL & " ORDER BY tblCustomers.CompanyName;"
 
Me.lstCompanies.RowSource = sSQL
Me.lstCompanies.Requery
Me.lstComCtc.Requery
End Sub

 

[Banana]

LPurvis,

Thanks for commenting. I wasn't sure about optimization for SQL-on-the-fly, but is good to know that Jet will do it regardless, so our consideration is more of how big of a performance hit will we see when Jet has to optimize every time it is run against when it has a saved execution plan, and whether it is worthwhile (e.g. not so much for a big table that has millions of rows that won't likely change versus could be better for small table with very dynamic data).

Your blurb about having a table gives me a idea- I wonder if it would be worthwhile to create a collection of functions and perhaps a Enum so when writing out the VBA code, we can use Enum type to list all available queries and get their SQL.

As for your point about VBA simplifying otherwise inefficent and complex parameter querys (e.g. Null values), wouldn't this still work:

If Not IsNull(Me.MyTextBox) Then
    qdf.MyParam = Me.MyTextBox
    Me.Recordset = MyDb.OpenRecordSet(qdf)
Else
   Me.Recordset = Nothing
End If

Finally, mind elaborate a bit about formatting problem? I'm not quite sure if we're talking about its tendency to feed a newline for each clause group or its excessive usage of ()s or something else?



Twoplustwo-

Keep in mind that we're discussing preferences- if you find it workable for your needs, then don't worry about it. As LPurvis quite astutely puts it:

To use either one exclusively would be just limiting IMHO.

 

[tehNellie]

Fortunately as the evil DBA I get to prevent developers from having any direct access to the tables.

Why? Bitter experience, we had someone think it was cool and all to put all the SQL in his application. Problem is he wrote bad SQL and implemented no real rules on the database because hey, it was all done in the application right?

Problem no is that poor design is starting to creak and I can't do anything other than firefight the existing DB because all of the database logic sits in the application which will have to be rewritten to take into account any changes done to the Database.

I can't rewrite the queries causing the problems for the same reason. I can see them hitting the database through the trace tool, but I've no idea where in the application they are being called from.

At the point you're calling queries/storing procedures, your application is separated from the database. All you need to do is pass those parameters to that Query/procedure and you'll get those results back again, I'm now free to tune the underlying table structure and queries without you ever needing to touch the application.

It's probably less of an issue in Access, but a well written stored procedure will execute faster than well written dynamic SQL, put less traffic through the network and be more secure than passing dynamic SQL.


Dynamic SQL makes the baby DBA Jesus cry.

 

[Banana]

Nellie,

Do you happen to be DBA of a RDBMS other than Access?

The only reason I ask is because when we deal with any other RDMBS, I know that MySQL people will encourage the use of prepared statements, while George already has stated that Oracle strongly encourages Bind Variables, and Bound Parameters exists in MS SQL, which all are essentially the same thing as Access's Parameter Query, but is that because they had n-tiered application in mind?

Access isn't really a n-tier application, and when it's a simple tracking database in use by a department, it doesn't matter that much when it's embedded in VBA rather than using Parameter Query, and besides, it's probably easier for the limited users. But once company adopts, all headaches come out of that design which wasn't simply intended to be scaled. Is that why we're seeing such differences in behaviors of developers for Access and developers for any other n-tier applications?

(BTW, for anyone interested in more information, I think FMS has a great paper on this).

 

[georgedwilkinson]

Additionally, many smaller companies have a dual-role SQL DBA/Access Developer (like in my case). We come in and inherit badly written stuff and try to apply our best practices to make the situation more bearable.

In the case of my main database, I have hundreds, maybe even thousands of SQL calls straight to the SQL Server database...no stored procedures. Since the SQL on many of these queries is dynamic, I believe that using Bound Parameters to "fix" some of this stuff will increase the performance of my SQL database and improve the maintainability of my Access front end until I can rewrite all of it using stored procedures.

Unfortunately, it is unclear how to achieve this. Banana had suggested that Parameter Queries might be a way to implement this in Access and I've been secretly plotting on how I might use that information to improve my SQL database parse/plan/execute performance. Alas, I have been sidetracked by multiple project deliverables and haven't tried it yet to see how it performs.

 

[LPurvis]

Hi Banana
(There it is again .

The formation of a plan should be so quick that there would rarely (if ever) be a perceptable difference (other than by timed iterations in code).
If there were millions of rows - the real question is just "were there millions of rows when the saved plan was created"?
Essentially - I would look to make decisions of which method to use based upon other factors.

The enum is a possibility I suppose. Though it's not something I'd look to implement just for a design time convenience. Having to maintain the enumeration definition when adding new queries etc - and then maintain a conversion table of what each enum value related to as an actual query name...
Natually - when using a table to store SQL - it only makes sense when in conjunction with a function or class to return those statements in a meaningful and versatile fashion.

In the code you mention - your example is either passing a parameter and opening a recordset or not at all?
I'm envisioning the issues of this thread to deal with, potentially multiple criteria/parameters - and the optionality of each parameter.
So not opening the recordset due to one missing wouldn't then be an option.
Also - were you meaning something like
qdf.Parameters(0) = Me.MyTextBox
Me.Recordset = qdf.OpenRecordSet
But the same issue though - the fact of a parameter being optional or not. (And one of many).
(I'll no doubt come back to all this in the Server replies shortly

As for formatting...
Eurgh. Don't get me started. I've harped on about this for years.
I've expressed the opinion directly to MS more than once about my desire for better formatting in the SQL editor - and it respecting that formatting.
All too often the parser will reformat the SQL you've entered.
As I mentioned it can just be the indentation you've laboriously entered, carriage returns - but worst of all when it takes perfectly valid subquery statements and converts them to the Jet 3.X syntax.
e.g. from
..INNER JOIN (SELECT SomeField FROM tblA) As X ...
to
..INNER JOIN [SELECT SomeField FROM tblA]. As X ...
which Jet may then have the audacity to *fail* to parse if the query is relatively complex, involving other joins etc!
A proper pretty coloured formatting SQL editor in Access would be nice (and I have no doubt is coming one day - I'm not saying when I must make clear... )
But for now I'd take one that just left what I've entered alone!
(There aren't many things at all I don't like about Access... but that's one! ;-)

Onwards...

 

[LPurvis]

tehNellie

OK - we're moving on to a Server scenario. Then the game's changed.
(I do fully understand the DBA point of view in this).

I'll just restate my age old mantra here that I'm talking about a SQL Server here verses *Jet*. Not Access... *Jet*.
(OK... or the Access Database Engine - which is actually an odd name as it is very much the database engine for *Office* now... but there you go. Jet 5 by another name lol).

With a server database we have a different set of considerations.

First of all - we very likely have a DBA.
Someone who not only *can* maintain these data objects (queries by one name or another) but who's job it is to and indeed responsibility to keep them running sweet as a nut. To remove objects from the server removes a huge amount of the ability to do that (as Nellie says).

The server is very likely critical to numerous other applications.
Poor local application data requests can not only nail that application and other users of it - but nonrelated applications too.

Server objects have much more power than Jet's.
Queries are a pretty versatile beast - but they're a mishmash combination of Views and limited Stored Procedures functionality (i.e. accepting parameters).
But SPs and UDFs have real processing and programming logic - conditional execution and temporary objects. (I'll not include cursors - as some DBAs may feel queesy at the mere mention).
The saved execution plan of an SP is a nice thing again - but IMO pales by comparison to the other abilities.
Indeed the WITH Recompile option exists to force the option which we discussed earlier in the debate with queries Vs dynamic SQL
But I agree - using Dynamic SQL (even on the server itself in a SP) often means you've lost the battle not to.
An SP's parameters can be made fully optional (and I don't just mean by the providing of a default value - but by the local decision made in the SP's code to run SQL implementing that criteria or not).

Jet lacks all this. The reason why is also the biggest reason for the difference in mentality (IMO of course ;-)
Scale. Jet is a client side query engine. A file server which simply is designed for a different implementation. If poor requests are made – they're still processed by the local client PC as are good ones. The server upon which the central data MDB resides is very much less relevant.
Yes poor requests can harm concurrency of the database in a very similar way to a server db – but the requests are made the same be it dynamic or saved.

As you mention, preventing any direct table access is indeed a possibility (and a good way of exercising control).
The server security is more robust and able to offer this.
But the objects (Views, SPs, UDFs) themselves reside on the server. And that's where they're then administered. It's a different advantage to those that can be considered in the dynamic SQL Vs saved queries in Access debate. There are no server side query objects.

Of course opinion is often made worse by poor requests from an Access application which *does* have access to the tables – or at least linked tables to something.
Well formed SQL requests from a direct code connection to the server offer a better (and perhaps more fair) comparison (i.e. in addressing the " traffic through the network" argument – well formed requests are always key, and method of execution does come into play).
I agree – SP's still have the edge and always will. But we're also weighing in here the work of a poor developer in hammering the server.
And I'll not get into the whole linked tables Vs code connections debate (again).
As I've said elsewhere here – horses for courses… and so much depends on the professionalism of the application in use.

 

[pbaldy]

Great discussion Leigh. I'll simply say that my rule of thumb is to use saved queries/stored procedures most of the time, dynamic SQL in situations where the basic structure of the SQL itself can change (like a search form where the user has any number of optional choices).

Since I'm both DBA and developer, my arguments with myself are brief and I always win.

 

[Banana]

I whipped up a quick'n'dirty module to see if it would be useful for developers in getting SQL from saved queries without having to leave VBE; this is done automatically by VBA, so all you need is to run the sub to build and rebuild the list everytime you create or delete queries from the database.

Option Compare Database
Option Explicit

'Do not insert anything between first lines and before end of enum!!
Public Enum qdfs
   tmpQueryName = 12
End Enum

Public Function GetSQL(qdfName As qdfs) As String

GetSQL = CurrentDb.QueryDefs(qdfName).SQL

End Function

Private Sub CreateQueryList()

Dim My As module
Dim qdf As QueryDef
Dim sPar As String
Dim sSQL As String
Dim i As Integer

Set My = Application.Modules("modEnumQuery")

Do Until sPar = "End Enum"
    My.DeleteLines 6, 1
    sPar = My.Lines(6, 1)
    If InStr(sPar, "End Enum") Then
        sPar = "End Enum"
    End If
Loop

For i = 0 To CurrentDb.QueryDefs.Count - 1
    Set qdf = CurrentDb.QueryDefs(i)
    If Not Left(qdf.Name, 1) = "~" Then
        My.InsertLines 6, "   " & qdf.Name & " = " & i
    End If
Next i

Set qdf = Nothing
Set My = Nothing
End Sub

To use this, just type in the immediate window:

?GetSQL(MyQueryName)

BOOM! You get the intellisense listing the query and it automagically gives you the SQL, including the parameters if it's a parameter query.

Will it be useful? You decide!



Leigh-, I hadn't really considered the "optionality" of parameter query since as the de facto DBA©/Developer®/Evil Mastermind™, I get to design the parameter query so I know what I need and implement just that. I can see how it could be problematic for something more dynamic than just setting a criteria but as I've never quite had that problem, I wonder is that something that default values can't fix? Off the top of head, if I enter a criteria of "" for a string data type, it'd be same thing as having no criteria at all, thus is just as optional? Or will this hurt performance nonetheless?

Thanks for the brief explanation about formatting- it just happened that I was at another MSDN page discussing about Access and MS SQL server which showed some SQL syntax, and I now understood what you were talking about indenting. That must blow to see formatting butchered up like that.

One thing I couldn't definitely confirm- does Jet perceive Views as tables? If that's the case, wouldn't that also solve the problem of Access applications being developed outside of DBA's control by exposing only Views for world-edit?


pbaldy, suppose you're confused or undecided? What do you do then?

 

[pbaldy]

pbaldy, suppose you're confused or undecided? What do you do then?

I ask the network tech of course! If he doesn't know, the hardware guy, the applications guru or guy who maintains the servers. If they're all clueless, the coin flip (programmed in Access of course!).

 

[tehNellie]

Do you happen to be DBA of a RDBMS other than Access?

Correct, however to a degree I believe the principle remains the same whether you're developing for Access or SQL Server/Oracle/MySQL. There is less of an impact in Access normally because you're both the application developer AND the DBA and frequently the app and the database are on the same physical machine using the same overall application (Access), but I still believe that it's better practice to use as little Dynamic SQL in your application as possible.

Do I use Dynamic SQL in my Access stuff? Sure I do, I've even been known to chuck SELECT statements directly to SQL Server tables, especially when I'm initially developing the application/database however once I've finalised my design I try to move as much as possible into Queries. Why?

Pretty much exactly the same reason as I insist on it for SQL server. The application does not contain any/much "database logic" If I change tblMyData in my Access app I might break a query or two, I fix the queries I fix the application and looking at the query I can see exactly what information it's taking in and passing back out again. If I've got dynamic SQL I have to go look for where the application is breaking to find out why and what it is expecting to update or receive back at that point.

I might have the same SQL specified multiple times in my app (ok you can argue that's bad programming as well as bad DB practice) in different places, I want to change that query or the underlying tables I've got to change the SQL in all those places. But if you're going to develop a sub or a function specifically to call a repeating piece of SQL why not just dump it in a query and be done with it? It might still be prudent to wrap that query routine in a separate sub or function but your code is now even simpler.

When you've just got a bunch of tables, even if the constraints, relationships etc are well defined, but no queries it's very difficult, imo, to get a feel for what the database is doing and how. Again Access is a bit easier because you've got your tables and your application essentially in the same place and you've probably developed both sides.

And if you're in an environment where this is a possibility, at the point you outgrow Access and decide you want an all singing, all dancing application based on a RDBMS system with a web app, but based on your existing Access application you just need to pass the tables and queries to your DBA and your VBA code/Forms to your application designer. Sure they might still be the same person (you) but it's a hell of a lot easier to design when you can see all the database logic without having to trawl through pages and pages of code to figure out what that SQL is doing and why. From a DB point of view I don't care what your app is doing and from an app point of view I don't really want to have to trawl through lines of SQL to figure out what the code is doing.

One thing I couldn't definitely confirm- does Jet perceive Views as tables? If that's the case, wouldn't that also solve the problem of Access applications being developed outside of DBA's control by exposing only Views for world-edit?

Certainly from a SQL server point of view Access considers a view to be a table. Would it surprise you if I said that I don't like applications using views either? I am a bit more lax about letting people have some access to views, but generally speaking I'd much rather they use a Stored Procedure unless there's some good reason why they can't.

I'm not saying Never, ever use dynamic SQL but I do believe that for anything other than a throwaway application or something that you just use to do something helpful but will never be given to anyone else use Queries as much as possible.

 

[Banana]

tehNellie,

I totally so agree with you about keeping the logic compartmentalized. I kind of wish Access would implicitly encourage this, though I know that Microsoft weren't really thinking of OOP programmers as the intended audience but rather knowledgeable employees who want to be more productive in their work without investing half a million for a total RDBMS solution. Still, had they done a better job of "encouraging" good habits in use by OOP programmers, this would be less of an issue.

I have to admit I'm surprised that you don't like Views as much. I certainly do see the appeal of just passing off the SP, as it's just any other functions to the application, which is a very good thing™, but I don't think Jet can take in a SP and treat it as a query or even a table, where Views can at least be linked and manipulated within Access environment?

Maybe I'm just missing something, since I'm the one in absolute control of the whole setup so I don't normally think about needing to protect my MySQL from bad Access application because any bad Access application would be entirely my fault. (Insert a FOMP! here)

 

[LPurvis]

Hi Paul, good to see you here too.
(Indeed it's always nice to "meet" someone online who you've actually met! ;-)

Alas I have only the one official role - so conversations with myself have little excuse. :-s


Banana
As far as optional parameters - it can be something as simple as a search form. (A common reference in these discussions).
If you want to avoid dynamic SQL then you have to have optional parameters. We can't force a user to specify criteria for every field we want allow searching upon.
There are no implicit default values in Jet - and even if there were we should still have to apply some decision making (just as we would in an SP) to avoid unnecessary (and perhaps index ignoring) data requests.
For example consider a ZLS ("").
We couldn't apply that directly as the parameter value - or the db engine will literally look for fields which contain a ZLS.
It's even worse for Null (as we all know we'd never get *any* matches then...
WHERE FieldName = Null
will always return no matches regardless.
Some developers try to introduce fudges like implementing a Like "*" on a text field or requesting fields to default to their own value
i.e.
WHERE FieldName Like IIF([ParameterValue] = "", "*", [ParameterValue])
or
WHERE FieldName = IIF([ParameterValue] = "", FieldName, [ParameterValue])
Both of those are not equivalent to having passed no parameter though as they will never return any rows with Null in the specified column.
The only way to make it optional is as I mentioned earlier
WHERE (FieldName = [ParameterValue] OR [ParameterValue] Is Null)
or if you wanted to use ZLS
WHERE (FieldName = [ParameterValue] OR [ParameterValue] = "")
but you'd have to choose a way to pass a ZLS to a parameter then :-s.
And that latter OR criteria will generally prevent Jet from using any index which includes the FieldName column. So we're then harming database efficiency, performance and scalability - which is exactly what we're trying to improve by using paramater queries.

Many folks don't realise that even Filters in Access are handled by Jet into query requests for only the required data.
You hear the old mantra chanted that filters are poor and you must set recordsources. But, for example, passing the WhereCondition in the OpenForm method (although it actually just sets a Filter on the called form) sees Access pass that to Jet in the opening request - and Jet asks for the source of the form including the filter criteria as if it were part of the form's recordsource where condition.

Access (i.e. Jet via the ODBC provider) will even do this when accessing server data. The difference being that it can't be guaranteed - as not everything in Jet SQL is parsable by ODBC Jet will even break local query requests down if it can't provide an ODBC parsable statement into that which can be requested upon the server and that which must be processed by the client engine.
But some innefficient requests do get made and before you know it the reputation of linked tables is down the toilet. (Where it has been for some for years - how often have we heard the claim that linked tables always pull over all records and filter locally? :-s).
It's just not necessarily the case.

(It's perhaps worth pointing out here that the above functionality of Jet using linked tables does imply a greater advantage of saved query definitions Vs local dynamic SQL than in file server mode... As the execution plan involves ODBC decision making too and hence that tiny bit more overhead - but not that much... even saved queries are still parsed by ODBC before execution...).

All that said... do I prefer making ADO requests for the data directly against the server? Yepperooni! Big time! IMO ADO is utterly appropriate for server data access. (Be it executing SP's or just dynamic SQL ;-)
I'm just saying that alot of default functionality is written off due to small misunderstandings and bad implementations.
And I much prefer MDBs for server Access rather than ADPs because of the versatility that having Jet still there offers. It's a fantastic little db engine - if properly applied in scenarios for which it's intended.

And yes - AFAIK there's no differentiation between linked tables and views. Jet just needs to be "instructed" on the unique index of the view for it to be updatable - which isn't hard.

 

[tehNellie]

I totally so agree with you about keeping the logic compartmentalized. I kind of wish Access would implicitly encourage this,

I certainly think Access is pretty crap in this regard but perhaps this also stems from it being an awful lot better at performing outside of its basic scope as a single user, simple database tool.

Views have their place, I do use them a lot behind the scenes and have been known to let applications link to them for reporting purposes, for example, where a stored procedure might not be that useful for letting you quickly get a subset of that report you run now and again. I'm in the [un]fortunate position of being responsible for a DB that is basically "how not do everything in a RDBMS 101" so even where things do use views instead of going straight to the tables they tend to do it badly.

I even *shock* have an access front end written by myself that uses a View because trying to call an SP and present that data back to the user in a manner they could do something meaningful with in Access is a royal pain in the butt in some circumstances and functionality in that instance outweighed my desire not to have linked anything within the application.

I guess at the end of the day it's horses for courses. Using Views, or embedded SQL into a local Access table has its place. Using a couple of forms to manipulate data in a few tables then sure, it might make far more sense to just whack a bit of dynamic SQL in, save some time and keep it simple. Developing anything that might expand beyond its current scope and liberally using dynamic SQL is asking for pain and frustration further down the line imo.

Maybe I'm just missing something, since I'm the one in absolute control of the whole setup so I don't normally think about needing to protect my MySQL from bad Access application because any bad Access application would be entirely my fault.

That is certainly a key factor imo. When you're fulfilling both functions the implications of having your application dictate what you can do the database is perhaps less of an issue.

 

[Banana]

As far as optional parameters ....

Now I see that my initial conceptions about optionality was a bit misplaced and isn't really fixed by having If/Else or even Select Case constructs. The only way around this is to just do a passthrough query to RDBMS that supports optional parameters.

Many folks don't realise that even Filters in Access are handled by Jet into query requests for only the required data.
You hear the old mantra chanted that filters are poor and you must set recordsources. But, for example, passing the WhereCondition in the OpenForm method (although it actually just sets a Filter on the called form) sees Access pass that to Jet in the opening request - and Jet asks for the source of the form including the filter criteria as if it were part of the form's recordsource where condition.

Interestingly, I shun the filters and WhereCondition, though I'm aware that they don't pull everything over the wire, but rather because I feel that they obscure the logic- I'd much prefer to set the recordsource explicitly even if it's a bit more harder because I want to make sure I know what exactly has been passed to the MySQL, and it's usually in one place (e.g. I write my recordsource logic in a custom function and call that from various place within form modules). This also means I get to keep the logic in the same module- I don't want to go looking in the parent form's module for the logic of filtering/where-ing the recordsource.

Access (i.e. Jet via the ODBC provider) will even do this when accessing server data. The difference being that it can't be guaranteed - as not everything in Jet SQL is parsable by ODBC Jet will even break local query requests down if it can't provide an ODBC parsable statement into that which can be requested upon the server and that which must be processed by the client engine.
But some innefficient requests do get made and before you know it the reputation of linked tables is down the toilet. (Where it has been for some for years - how often have we heard the claim that linked tables always pull over all records and filter locally? :-s).
It's just not necessarily the case.

So true. I like to encourage other people who use ODBC to read the MS's whitepaper on Jet and ODBC connectivity, which I look at as the bible for understanding Jet's behavior and how we can make an effective use of Jet while exploiting the functionality provided with the linked tables and bound forms. Indeed, I've found that with a ODBC source, one could do more with Access than if Jet was the backend, and that's still the case even with Jet doing some of the work.

All that said... do I prefer making ADO requests for the data directly against the server? Yepperooni! Big time! IMO ADO is utterly appropriate for server data access. (Be it executing SP's or just dynamic SQL ;-)
I'm just saying that alot of default functionality is written off due to small misunderstandings and bad implementations.

You know, I'm kind of mixed on that- I know that ADO is far superior to DAO when we're handling ODBC data sources, but DAO is the ideal tool when we're using Jet. Furthermore, there's some functionality in DAO that I quite like. The idea I've toyed with, but never really seriously implemented is to use ADO exclusively for any dealings with MySQL, and when I have some records and I want to do some local-side processing, use DAO for that. Technically, this is quite feasible, but in terms of maintainability, do I really want to flip-flop between two different library? Eh, not sure yet!

I certainly think Access is pretty crap in this regard but perhaps this also stems from it being an awful lot better at performing outside of its basic scope as a single user, simple database tool.

Does anyone know when the ODBC functionality was introduced or if it was present from the beginning? I ask because if it was tacked on a later version (97?), this may be why we are seeing two discongruent methods- one being a simple file server for few people to use, another being a front-end client to a true RDBMS solution. To be fair, they're not really discongruent per se, but more so because of how Access exposes itself to the developers, whether they're a hard-core, bit-twiddling C programmer or a K-user who saw a code snippet somewhere on the internet.

I guess at the end of the day it's horses for courses.

Quite true!

That is certainly a key factor imo. When you're fulfilling both functions the implications of having your application dictate what you can do the database is perhaps less of an issue.

Yes, and this is also why IT hates Access- it's truly a double-edged sword.

I just wished that MS had focused more on polishing the functionalities for ODBC connectivity and modifying the Access's behavior to encourage good programming habits (For example, they could provide a hyperlink to saved query and QBE within VBE and provide a object to use as a fast query object).

 

[LPurvis]

I suppose the best approximation to parameter default values in Access would be using a UDF as the criteria instead of a standard parameter. Having the function returns some desired value (for example a Form control expression) - but if you didn't provide a value (e.g. the form wasn't open) then have the UDF return some "default" value instead.
This would still be prone to the drawbacks I mentioned of true optionality (with Null values in columns).

For me, ADO performs a really vital role for Access (at least until there's a replacement implemented for it) in that it affords the potential for a truly efficient application - without any linked tables and Jet request ambiguity but direct server requests . So we can have Forms bound directly to Stored Procedure's, UDFs and so on.
And other abilities like disconnected recordsets can have a wealth of uses.
Considering that ODBCDirect has been dropped from Access 2007 - it's gained even more importance (despite MS's re-leaning towards DAO in general).


ODBC has been supported in Access since the start. (Not that I worked with Access back then .
At the very latest then perhaps version 1.1 really made use of it.

One of Access' core strengths then remains so now - its sheer diversity of sources. True - much of this range is due to ODCB - and it's at the mercy of the ODBC driver providers to keep supplying up to date versions (cue many angry FoxPro developers...) But the ability to bind and bring together data so trivially is unrivalled.

The fact that it gets used poorly by low level users is annoying to professionals - but not necessarily against the ethos with which it was created.
Indeed but for those information workers nailing it with non-standard practices Access itself almost certainly wouldn't exist. (Not any more anyway).
So we have to, reluctantly, accept some annoyances - and the hammering it gets from IT staff due to the variety of examples out there.
Similarly to how we have to just accept when new features are implemented that don't exactly set developer's eyes alight.

 

[tehNellie]

The idea I've toyed with, but never really seriously implemented is to use ADO exclusively for any dealings with MySQL, and when I have some records and I want to do some local-side processing, use DAO for that. Technically, this is quite feasible, but in terms of maintainability, do I really want to flip-flop between two different library? Eh, not sure yet!

I do tend to do this but I ended up doing it from day one so it's kind of habit now.

One distinct advantage of it (and not using dynamic SQL) is that I can get a pretty good idea just from the variable declaration what the procedure is going to do. It's got a DAO recordset so it's going to be doing something with a local table and an ADO command object with a couple of parameters so it's talking to SQL server and passing/retrieving data.