Best practices Forms/Reports record source (1 Viewer)

got_access:]

Registered User.
Local time
Today, 03:32
Joined
Jun 15, 2018
Messages
83
Access allows for a developer to create queries to function as record sources for forms and reports. If the developer uses this practice – he will end up with a number of queries sitting in the navigation pane which have no other purpose than to be a record source for forms/reports. In the unexpected event that one of these queries becomes compromised/deleted – its dependencies (forms or reports) no longer function.

It would seem that although this practice is an option for a developer – (and I suspect it is probably the way any number of new developers would build up a DB) – the practice appears to introduce unnecessary vulnerability.



The better practice would be to develop temporary debugged queries and load the SQL syntax into the DATA SOURCE property of the form or report, and then delete the temporary query.


I suspect professional developers address this issue?


Thanks in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:32
Joined
Oct 29, 2018
Messages
21,357
Hi. How could the query be compromised that could not also happen with the form using it? In other words, if the query is compromised, the form doesn't work. Conversely, if the form is compromised, the query is useless. So, what's the difference between having a query or not? If you're concerned about storage space, I wonder how much you are saving/gaining.


PS. Just so we're clear, I am not advocating either approach. I happen to believe it's a personal preference.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:32
Joined
May 7, 2009
Messages
19,169
as what I know, queries are optimized when you first load them.
access saved the optimized statistics somewhere on its Msys tables.
so the next time you run them, access knows how to properly execute them.

deleting the query also deletes the statistics that goes with it.
so everytime you delete, re-create the query, access again rebuilt/delete those statistics.
sometimes may lead to slow execution, since everytime there is a new query, it goes to another evaluation.
 

got_access:]

Registered User.
Local time
Today, 03:32
Joined
Jun 15, 2018
Messages
83
Hi theDBguy,
Yes a form or report can also be compromised. Which would more than likely result in some portion of the form/report not working as originally designed. Assuming a minor change - the form/report would still (for the most part) work.

But wouldn't it seem risk adverse to build the form/report with its own record source property (using an SQL query for example) - rather than leaving a number of query objects sitting in the navigation pain that have no other purpose than to be a record source?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:32
Joined
Oct 29, 2018
Messages
21,357
Hi theDBguy,
Yes a form or report can also be compromised. Which would more than likely result in some portion of the form/report not working as originally designed. Assuming a minor change - the form/report would still (for the most part) work.

But wouldn't it seem risk adverse to build the form/report with its own record source property (using an SQL query for example) - rather than leaving a number of query objects sitting in the navigation pain that have no other purpose than to be a record source?
Maybe, maybe not. As I said, I think it's a personal choice. What are the chances those objects (any of them) in the Nav Pane can get damaged? If you're worried about space (clutter?), not sure if it's worth it.
 

got_access:]

Registered User.
Local time
Today, 03:32
Joined
Jun 15, 2018
Messages
83
Right - understood. I think for me it is a personal choice.
I would rather reduce vulnerabilities whenever its easy to do so. And also - as you say - it clears out objects in the nav pane that don't otherwise need to be there.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:32
Joined
Oct 29, 2018
Messages
21,357
Right - understood. I think for me it is a personal choice.
I would rather reduce vulnerabilities whenever its easy to do so. And also - as you say - it clears out objects in the nav pane that don't otherwise need to be there.
Hi. Good for you. I view some things or approaches in software/application development as a personal style. I prefer to worry about what is than what might be. Good luck with your project.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:32
Joined
May 21, 2018
Messages
8,463
If you are making an ACCDE this could make sense since you can still mess with the queries but not the forms, reports, and code. So that would be more secure. If for personal use, I do not see as much utility. There is no reason to worry about cluttering the nav pane since you can put all your queries related to forms and reports and put in a group and then hide.
 

isladogs

MVP / VIP
Local time
Today, 10:32
Joined
Jan 14, 2017
Messages
18,186
Generally I prefer to write sql strings in code instead of using saved queries or using query defs. That's because I prefer having all relevant code in one place rather than several. But as others have said, that's a personal decision.
There is some additional security in doing this but in my opinion its not significant in real life situations.

You might find this article interesting Speed Comparison Tests - Saved queries vs SQL vs query def
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:32
Joined
Jul 9, 2003
Messages
16,244
I also avoid queries where possible and use SQL passed in via VBA.

I took this to the extreme recently, I made a product which allows you to build nice Switchboards.

the only way I could get it to detect if there were orphaned switchboard pages was to write a set of three queries.

I fretted about this, the last thing I wanted was to have the installation require the installing of these queries.

I solved the problem by writing some VB code, (a recordset loop) which extracted the the orphaned page names, the page number, and built them into a string and inserted the string into the value list of a combobox. (Replacing the queries with code)

If you ever find yourself in a situation where you have to include a query, then open the query in design view, select SQL view, copy the SQL text and place it in the form code module the query is associated with. Add comment delimiters to preserve it as a comment, so if a user happens to delete the query, you've got it ready and waiting, easy to put back in ....

Sent from my Pixel 3a using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 28, 2001
Messages
26,996
rather than leaving a number of query objects sitting in the navigation pain that have no other purpose than to be a record source?

First, an observation: ALL repeat ALL stored queries that are SELECT, JOIN, or UNION queries are there to be record sources. All of them. What other purpose CAN they have?

Second, if they are clutter... to whom ARE they "clutter"?? If your users can see them (as clutter or as objects of curiosity) then you haven't secured the database that well anyway. If you see them as clutter, that could only be true if you didn't use them and allowed them to exist unused. Hey, that happens now and then as you get hot and heavy in building an app.

Here is where there is a potential issue and a matter of style: Do you

(a) Always dynamically rebuild SQL strings to be your recordsources? or

(b) Always use static strings and then use the form or report .Filter property?

The comments about using static queries because the optimizer has already looked at them? The optimization scan SHOULD be quick most of the time. But if you have a huge query with multiple JOINs, there is something to be said for not making too many waves at once.

I actually agree that it is a style thing for simpler dynamic queries. I wonder about how complex it can get when we are talking five-way layered JOINs (which, sadly, I had to do more than once in my biggest project.)
 

Mark_

Longboard on the internet
Local time
Today, 03:32
Joined
Sep 12, 2017
Messages
2,111
The advantage of a query in your design view is when you have a need for more than one procedure to match the same query definition. This can occur when you have multiple reports that are dependent on all drawing from the same data source but are used for different purposes.
 

isladogs

MVP / VIP
Local time
Today, 10:32
Joined
Jan 14, 2017
Messages
18,186
Just to expand on my previous comments, the speed tests I ran comparing Saved queries, SQL and query defs showed that in many cases (not all) running saved queries is actually slower than using sql in your vba code. Query definitions are always the slowest of the three methods.
Having said this, the speed difference is usually relatively small, reinforcing the point that its largely personal preference which you use...though Mark makes a valid point in the last reply
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 28, 2001
Messages
26,996
A very valid point indeed - don't dynamically re-invent the same wheel when you have a good one lying around otherwise unused.
 

Micron

AWF VIP
Local time
Today, 06:32
Joined
Oct 20, 2018
Messages
3,476
I for one would rather look at a stored query if I needed to tweak it or just needed a memory refresher months or years later rather than dig through code to recall everything about its functionality or purpose. The only real upside I can see to coding all sql is so that queries could never be exposed, assuming anyone could overcome your efforts to protect them from being viewed in the first place. Either way, I presume that if the db is an accde or mde, you need to go back to the master to change either a query or code, so there's no difference in that situation either. Surely this isn't about db size as the forms and reports likely occupy a much greater percentage of most projects.

Also, this may be a moot point because I can't recall if you can read the query Object Properties (not the query property sheet) in an accde/mde db but if you can, you could see notes about the stored query. Can't do that with coded sql in such a db - have to go to the master.

If you can detect the speed difference in normal operation, you must either have complicated queries running over hundreds of thousands (millions?) of records or you have a quad core processor for a brain.:)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:32
Joined
Feb 19, 2013
Messages
16,553
my practice is to build my sql for each form using a function to dynamically build and return a recordset to assign to the form/report. Depending on the requirement, the 'hardcoded' part is usually stored in a table, or occasionally hardcoded in VBA.

The benefit for me is I can return a dao or ado recordset or even a stored procedure from sql server simply by changing a setting.

By storing the sql in a table (long text), I can also set it out in an easily readable layout with richtext formatting and notation, apply security parameters, etc. - the function removes notation and converts to plaintext

I do occasionally have queries in the back end - much like a view - which users of other apps such as excel might need to download without having to know the structure/relationships of the tables.
 

isladogs

MVP / VIP
Local time
Today, 10:32
Joined
Jan 14, 2017
Messages
18,186
I presume that if the db is an accde or mde, you need to go back to the master to change either a query or code

No. You can change the design of both queries and tables in an ACCDE file and I see no reason why that would be different in an MDE. You can also view the query object properties.

Obviously you can't view or change code in a ACCDE/MDE
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 19, 2002
Messages
42,970
I've been using SQL since the early 80's. At that time, all SQL was embedded in my COBOL application. I used to dream of a tool that would remove the tedium of writing embedded SQL. The QBE was one of the real selling points of Access when I discovered it in the early 90's. I don't think it is a challenge or even remotely interesting to have to type SQL when I can point and click to create joins and select columns. I never have to worry about how anything is spelled. Maybe if all you ever work on is one database at a time that you created you can keep all the names in short term storage in your brain so you never have to think about what you are typing but I am a consultant. I am working on a dozen projects at one time for different clients and some of the time, I don't have control over the naming standards so names don't just roll off my fingertips as I type.

As others have mentioned, I also use select queries in multiple places. Frequently we have forms and reports that display the same data. Why reinvent the wheel? They both use the same query. In some cases, calculations are needed. For these, I create base queries that other queries use rather than the underlying tables. That way, the sales price is always calculated the same way and the full name is always concatenated the same way and if I ever have to make a change to one of these calculations, I only have to do it once. If you've spread the calculations and SQL out over dozens of forms/reports, you'll be searching and changing all instances.

Yes, it is a personal choice whether to keep the SQL string in the form's code or as a querydef but "professionals" don't waste their time typing if they don't have to.

And just to be complete, I complain to MS at least once per year to fix the d*** SQL editor to stop reformatting my code because when I have to switch to SQL view, I too hate the mushing they do as well as their obsession with multiple parentheses. I understand why they do it. I just want them to keep their own copy of the SQL so they don't have to mess up mine. And while I'm at it, I also ask for the ability to embed comments in the SQL string.
 

Users who are viewing this thread

Top Bottom