Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-29-2019, 06:31 AM   #1
got_access:]
Newly Registered User
 
Join Date: Jun 2018
Posts: 83
Thanks: 2
Thanked 1 Time in 1 Post
got_access:] is on a distinguished road
Best practices Forms/Reports record source

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

got_access:] is offline   Reply With Quote
Old 08-29-2019, 06:38 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,695
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Best practices Forms/Reports record source

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 08-29-2019 at 06:44 AM.
theDBguy is offline   Reply With Quote
Old 08-29-2019, 06:41 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,426
Thanks: 68
Thanked 2,709 Times in 2,594 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Best practices Forms/Reports record source

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-29-2019, 07:03 AM   #4
got_access:]
Newly Registered User
 
Join Date: Jun 2018
Posts: 83
Thanks: 2
Thanked 1 Time in 1 Post
got_access:] is on a distinguished road
Re: Best practices Forms/Reports record source

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?
got_access:] is offline   Reply With Quote
Old 08-29-2019, 07:13 AM   #5
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,695
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Best practices Forms/Reports record source

Quote:
Originally Posted by got_access:] View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-29-2019, 08:19 AM   #6
got_access:]
Newly Registered User
 
Join Date: Jun 2018
Posts: 83
Thanks: 2
Thanked 1 Time in 1 Post
got_access:] is on a distinguished road
Re: Best practices Forms/Reports record source

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.
got_access:] is offline   Reply With Quote
Old 08-29-2019, 08:34 AM   #7
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,695
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Best practices Forms/Reports record source

Quote:
Originally Posted by got_access:] View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-29-2019, 11:16 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,791
Thanks: 34
Thanked 540 Times in 513 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Best practices Forms/Reports record source

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.
MajP is online now   Reply With Quote
Old 08-29-2019, 12:09 PM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,392
Thanks: 112
Thanked 2,850 Times in 2,598 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best practices Forms/Reports record source

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-29-2019, 02:43 PM   #10
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,276
Thanks: 528
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Best practices Forms/Reports record source

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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-30-2019, 08:12 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,367
Thanks: 87
Thanked 1,644 Times in 1,526 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Best practices Forms/Reports record source

Quote:
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.)
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-30-2019, 08:01 PM   #12
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,027
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Best practices Forms/Reports record source

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.
Mark_ is offline   Reply With Quote
Old 08-30-2019, 10:48 PM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,392
Thanks: 112
Thanked 2,850 Times in 2,598 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Best practices Forms/Reports record source

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-31-2019, 05:33 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,367
Thanks: 87
Thanked 1,644 Times in 1,526 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Best practices Forms/Reports record source

A very valid point indeed - don't dynamically re-invent the same wheel when you have a good one lying around otherwise unused.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-31-2019, 06:09 AM   #15
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 935
Thanks: 10
Thanked 191 Times in 181 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Best practices Forms/Reports record source

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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Source Code Control, or other best practices GBalcom Modules & VBA 9 02-27-2017 05:30 AM
Setting a reports record source in VB GaelicFatboy Reports 3 05-25-2011 06:47 AM
Updating a reports record source ODUK Modules & VBA 1 05-24-2007 07:57 AM
Using a reports Record Source in code wallis1905 Modules & VBA 7 05-11-2007 01:40 AM
[SOLVED] Set Reports Record Source with Vb wizcow2 Reports 4 11-25-2003 05:49 PM




All times are GMT -8. The time now is 09:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World