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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-31-2019, 08:14 AM   #16
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,264
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Best practices Forms/Reports record source

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-31-2019, 12:44 PM   #17
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,438
Thanks: 112
Thanked 2,859 Times in 2,604 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

Quote:
Originally Posted by Micron View Post
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
__________________
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 09-03-2019, 10:28 AM   #18
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Best practices Forms/Reports record source

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-03-2019, 10:43 AM   #19
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,793
Thanks: 50
Thanked 1,096 Times in 1,077 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Best practices Forms/Reports record source

Quote:
Originally Posted by Pat Hartman View Post
I also ask for the ability to embed comments in the SQL string.
I'd vote for that!

__________________
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
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 03:13 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