Marcus recently reminded me of one of my Access maintenance headaches.
Do you write all your queries as queries and store them visible in the query window?
Or do you keep them in your VBA so they are easy to manipulate?
Or do you mix it up to give the maintainer a hard time?
Just wondering as I’ve been caught out a few times tracing the logic of the visible components in other peoples dbs, only to find they are quietly modified in code (from multiple places).
I often use Excel as the UI and build queries in cells, but I do try and consider the maintainer (just in case its me ;-)).
The last jet project I did I added all the SQL as querydefs, that made debugging much easier as I could open the .mdb and tinker with the queries via the Access UI.
What do you tend to do?
cheers
Simon
Smurf on Spreadsheets 
Tuesday, 22nd April, 2008 at 10:28 am |
it is much simpler to keep them in the mdb, but it is sometimes neater to create them on the fly in VBA. for example I sometimes like to provide a right click select and populate in a list with several columns using a generic form, where the data starts out in an access table. I either have to create a distinct query for each column, or start the sql as a text string in VBA and manipulate it to change the selected column in VBA.
Tuesday, 22nd April, 2008 at 12:21 pm |
I use both techniques:
The user can make their own queries, (starter query provided) and use their own field names, but the VBA checks and prompts for the required set of data fields (last-used fields are remembered etc). This provides 2-way isolation: the user can change the underlying data but must provide a query that renders it into the required shape, and the program can change what it wants but must provide a translation mechanism via the prompt.
Then the program will generate SQL/queries as required for the filtering/transforms/aggregations etc, driven by user-entered options and parameters.
Tuesday, 22nd April, 2008 at 12:27 pm |
I tend to get the sql out of access and bung it into VBA. I can count what i know about sql on one hand. I try not to use queries stored in the mdb.
Tuesday, 22nd April, 2008 at 1:37 pm |
Simon:
Named querydefs are one of the biggest strengths of Access. It’s exactly the same as using SPs and Views in SQL – and I don’t think anyone thinks their a bad technique to use ;-).
It is far easier to maintain your SQL in the UI provided by Access rather than in code. Also I very often use multi-leveled queries based on other queries that “could” be done in nested SQL but’s a whole lot easier to create and maintain in the Access UI.
Also it means that my code simply says “select * from qryXXXXX” – much cleaner and readable.
Simon – there’s no debate for me on this ;-)…
Dick
Tuesday, 22nd April, 2008 at 1:54 pm |
QueryDefs every time for me, especially through using param queries (though a potential gotcha is that, unlike SQLServer, you don’t get named params with Jet, so you have to set up your params in code in the same order that they appear in the query itself).
Dick, are you sure you need to “select * from qryXXXXX”? I just use “qryXXXXX” and it works fine.
Tuesday, 22nd April, 2008 at 2:50 pm |
“Dick, are you sure you need to “select * from qryXXXXX”? I just use “qryXXXXX” and it works fine.”
Whatever ;-) ……. Simon – your guys are picky ……..
“you don’t get named params with Jet, so you have to set up your params in code in the same order that they appear in the query itself).”
That’s a definite nuisance.
Dick
Biggus
Tuesday, 22nd April, 2008 at 4:06 pm |
There are some rare instances where I’ll embed SQL in code but the vast majority of time it’s QueryDefs and Stored Procedures. SP’s have the advantage that you can build more logic (and enforce business rule) directly in them. It’s far easier making one change on a server than having to distribute an updated front end to a broad audience. Simple example: I have one SP to truncate temporary tables which accepts the name of the table to truncate. The SP validates the table name, builds the SQL and executes it. Table names not recognised are ignored making it impossible to truncate a table that shouldn’t be truncated. It also means there is one SP to maintain rather than 6 places in VBA code.
“set up your params in code in the same order that they appear in the query itself”
Not at all. Store the parameter names and respective values in an array, then loop through the QueryDef’s parameter collection (in DAO or command.parameters in ADO). For each prm, loop the array and where the name matches, pass the parameter the corresponding value. Jet uses the name of the parameter (in square parenthesis) for the name – otherwise you can explicitly name the parameters in the QueryDef.
I wrote a class to handle connections to databases with methods to return a recordset to either an array or spreadsheet range (or simply execute it). Parameters are passed via another method (ParamAdd). The parameters can be assigned in any sequence. It works for both QueryDefs and Stored Procedures.
Alastair – that situation is easily solved with a SP where you could simply pass the table and column name in question and have the SP build the SQL and return a distinct list. Hmm – I wonder if it could be done with a QueryDef.
Regards –
Tuesday, 22nd April, 2008 at 4:52 pm |
Does anyone else encapsulate SQL in VBA in functions to which you need only pass any parameters? (in those rare cases where you embed SQL in code of course :P)
This keeps SQL out of the main body of code and allows re-use by different portions of a project.
Tuesday, 22nd April, 2008 at 5:30 pm |
I use 100% SQL-in-code. Although after reading these comments I’ll be trying the querydef method. There has to be some kind of performance hit using qeurydefs, doesn’t there? If I say qryMyQuery in code, something has to convert that to SQL, whereas if I provide straight SQL then no conversion is necessary. Maybe it’s so small as not to matter.
Tuesday, 22nd April, 2008 at 5:47 pm |
Dick – When you save an Access query (QueryDef object) the SQL statement is saved with the query. THat is, SQL is a property of the QueryDef object. What’s to convert?
Regards – Marcus
Tuesday, 22nd April, 2008 at 6:32 pm |
Dick (what a great name eh?):
“There has to be some kind of performance hit using qeurydefs, doesn’t there?”
Actually I have always thought the opposite is the case because a saved query is pre-compiled and Jet likes that.
This gain used to be easily seen in earlier versions of Access and on earlier slow machines and networks. Even though I haven’t tested it in recent versions (where’s Ken Getz when we need him ??), I have always thought it still had some value. If anything it’s neutral not a negative.
Biggus Dickus
Tuesday, 22nd April, 2008 at 8:29 pm |
“This gain used to be easily seen in earlier versions of Access and on earlier slow machines and networks.”
I was going to comment that “Select * from qryXXXX” is probably doing the query twice (well, doing the query and then querying the result of the query) and that the use of the ‘*’ also makes Jet do more work in finding out what fields it should be dealing with, but I think that, generally at least, such micro optimisation is becoming less relevant as hardware becomes faster and cheaper.
Tuesday, 22nd April, 2008 at 9:27 pm |
Rob
I assumed Dick meant ‘select * from qryXXX…’ where … means WHERE or HAVING or whatever. I build queries up just the same based on others, does anyone else?
Marcus yes I do that, I have SQL (and MDX) generator library code.
Performance wise I’d expect querydefs to have the edge, but I don’t know, havent tested it, and no idea whether it would be material. It would depend on how you access the db though, I imagine DAO is faster than ADO is faster than ADO.net?
Anyone got any decent performance links?
cheers
Simon
Tuesday, 22nd April, 2008 at 9:56 pm |
Yeah, I was going to sign my name Littlus Dickus, but that’s the kind of thing that follows a guy around.
I like the pre-compiled argument. And I never thought of the SQL statement being saved as a property of the QD, but that makes sense. I should admit that I use ADO even when connecting to Jet (because I’m lazy), so I’m a bit of hypocrite for bringing up performance issues in the first place.
Wednesday, 23rd April, 2008 at 9:30 am |
I have always used DAO – don’t know why. Access and performance is something of an art rather than a science!!!
Thursday, 24th April, 2008 at 7:23 am |
Simon,
“I assumed Dick meant ’select * from qryXXX…’ where … means WHERE or HAVING or whatever. I build queries up just the same based on others, does anyone else?”
Wouldn’t this be a textbook example of distributing logic across different layers of your application? This looks like a maintenence nightmare. Wouldn’t it be better to make qryXXX as param query so that the WHERE logic happens with the rest of your app’s data access logic?
Rob
Thursday, 24th April, 2008 at 7:27 am |
Maintenance. With an ‘a’. I blame the teachers.
Thursday, 24th April, 2008 at 10:48 am |
Rob
Don’t you base views on views or queries on queries?
I may do a base groupby, and then have various views of that, perhaps further summarised, or filtered or joined. Not stuff like dates, they would probably go in as parameters, but I would join to a product type table, of a geo one. I guess stability is the key, unstable – param, stable – view of view?
I’ll dig out an example for a future post, as I suspect we are thinking of different scenarios.
Tuesday, 29th April, 2008 at 4:43 am |
I use MS Query to build a SQL connection string, save the query file, then just open it from Excel 2003 VBA using the OpenDatabase Method. That executes the query without any need to touch Access (which personally I detest). Just assign the macro to a toolbar button and POOF! anyone with Excel can query the db. If you have a copy of the book ‘Managing Data with Microsoft Excel’, there’s an excellent section about querying an Access database from Excel.
–JP
Tuesday, 13th May, 2008 at 1:39 am |
Great conversation. I’m just starting to program Access and this post gave me the inspiration to write a class to handle Select Querydefs to feed into reports.