Solved A Query Learning Question (1 Viewer)

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
Hi All

Not sure how to phrase the thread subject better than the above as i'd probably end up writing most of the paragraphs coming as the subject.

Anyway

I'm trying to understand if there is any discernible difference between the following 2 options for a forms Record Source.

Option 1

If i create a query separate to the form which returns the records i require and then when building a form i use the query as the record source

Option 2

I create a form and use a few tables as its record source and then drill into the record source and build the same query from option 1 in there

I hope this question makes sense, i am still learning (we probably all are i guess but i'm still in the early stages of learning here) how to do things more efficiently as with my FE/BE setup i want to reduce the overheads where i can for the users.

Thanks in advance

Glen
 

June7

AWF VIP
Local time
Today, 04:18
Joined
Mar 9, 2014
Messages
5,423
Can just use table as form RecordSource.

Don't understand Option 2.
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
i knew i probably didn't describe things right, let me try again:

Option 1 would just refer to a query like

SELECT test.* FROM test;

Where Test looks like:

Code:
SELECT DISTINCT [FACT-Deliveries].del_arrival_date, [FACT-Consignments].cons_supplier_id, [FACT-Consignments].cons_albaran, [FACT-Deliveries].del_cust, [FACT-DeliveryProducts].del_prod_case_num_orig, [product_name] & " " & [del_prod_count] & "x" & IIf([del_prod_weight]<1,CStr([del_prod_weight]*1000) & "g",CStr([del_prod_weight]) & "kg") AS FullProductName FROM ([DIM-Products] INNER JOIN ([DIM-Companies] INNER JOIN (([FACT-Consignments] INNER JOIN [FACT-Deliveries] ON [FACT-Consignments].[cons_id] = [FACT-Deliveries].[del_cons_id]) INNER JOIN [FACT-DeliveryProducts] ON [FACT-Deliveries].del_id = [FACT-DeliveryProducts].del_prod_del_id) ON [DIM-Companies].id = [FACT-Deliveries].del_cust) ON [DIM-Products].product_id = [FACT-DeliveryProducts].del_prod_name) INNER JOIN [FACT-DeliveryInvoicesAndCNs] ON [FACT-Deliveries].del_id = [FACT-DeliveryInvoicesAndCNs].delivery_id WHERE ((([FACT-DeliveryInvoicesAndCNs].reference) Not Like "*INV*")) ORDER BY [FACT-Deliveries].del_arrival_date;

Option 2 ends up having this in the Record Source

Code:
SELECT DISTINCT [FACT-Deliveries].del_arrival_date, [FACT-Consignments].cons_supplier_id, [FACT-Consignments].cons_albaran, [FACT-Deliveries].del_cust, [FACT-DeliveryProducts].del_prod_case_num_orig, [product_name] & " " & [del_prod_count] & "x" & IIf([del_prod_weight]<1,CStr([del_prod_weight]*1000) & "g",CStr([del_prod_weight]) & "kg") AS FullProductName FROM ([DIM-Products] INNER JOIN ([DIM-Companies] INNER JOIN (([FACT-Consignments] INNER JOIN [FACT-Deliveries] ON [FACT-Consignments].[cons_id] = [FACT-Deliveries].[del_cons_id]) INNER JOIN [FACT-DeliveryProducts] ON [FACT-Deliveries].del_id = [FACT-DeliveryProducts].del_prod_del_id) ON [DIM-Companies].id = [FACT-Deliveries].del_cust) ON [DIM-Products].product_id = [FACT-DeliveryProducts].del_prod_name) INNER JOIN [FACT-DeliveryInvoicesAndCNs] ON [FACT-Deliveries].del_id = [FACT-DeliveryInvoicesAndCNs].delivery_id WHERE ((([FACT-DeliveryInvoicesAndCNs].reference) Not Like "*INV*")) ORDER BY [FACT-Deliveries].del_arrival_date;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:18
Joined
Oct 29, 2018
Messages
21,357
Hi. Since we really don't know your intent, I would just say neither of those options is probably a good idea. For example, neither of the queries you presented above would make for a form that would allow the user to modify the data. Is that okay?
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
again apologies for scant information, i am creating a continuous form that displays a set of records that will allow the user to determine where invoice information is not created. the list the query gives in both circumstances displays the right data

what i'm concious of is as part of the form loading it pulls all the records of all the tables then runs the query on it, as opposed to (as i understand it) the query gathering the data from the tables and then supplying that to the form

but again my knowledge and understanding is based off of things i have read which i may have misunderstood

the outcome at the moment is that the form that uses the query loads a little (and its like miniscule) bit faster but the number of records returned at the moment are only 50ish, what i'm hoping to do is bake in efficiency prior to the database growing
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:18
Joined
Oct 29, 2018
Messages
21,357
again apologies for scant information, i am creating a continuous form that displays a set of records that will allow the user to determine where invoice information is not created. the list the query gives in both circumstances displays the right data

what i'm concious of is as part of the form loading it pulls all the records of all the tables then runs the query on it, as opposed to (as i understand it) the query gathering the data from the tables and then supplying that to the form

but again my knowledge and understanding is based off of things i have read which i may have misunderstood

the outcome at the moment is that the form that uses the query loads a little (and its like miniscule) bit faster but the number of records returned at the moment are only 50ish, what i'm hoping to do is bake in efficiency prior to the database growing
At this point in time, I can't think or remember any difference or advantage of one approach over the other. There used to be one, but I think it's pretty much non-relevant anymore. The best practice approach for efficiency is to only load the data the user needs when the form opens. If the user will be searching for the record they want to work with, the common approach is to open the form to an empty record.
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
At this point in time, I can't think or remember any difference or advantage of one approach over the other. There used to be one, but I think it's pretty much non-relevant anymore. The best practice approach for efficiency is to only load the data the user needs when the form opens. If the user will be searching for the record they want to work with, the common approach is to open the form to an empty record.
ok this helps in some senses as it kind of stops me worrying over things when i shouldn't, thank you for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:18
Joined
Oct 29, 2018
Messages
21,357
ok this helps in some senses as it kind of stops me worrying over things when i shouldn't, thank you for your help
Good luck! If you run into a specific bottleneck, just let us know. Cheers!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Feb 19, 2013
Messages
16,553
the difference is minor - when you execute a sql statement a query plan is built, these typically take a fraction of a second. If you use a query, the plan is created the first time it is run (and recreated with any subsequent changes to the query) and saved for subsequent reuse, if the sql is your recordsource rather than the query then that query plan gets created every time the form is opened/loaded.

On the other hand, the problem (again, very minor) with reusing query plans is the underlying conditions can change which would result in a different (and more efficient) plan if created again. So a plan based on a few records used during development might be different to one created on thousands of records. Another may vary if the developer adds or removes an index.

Personally I use sql recordsources rather than queries. Reasons are 1) I supply an .accde front end to users. With an .accde the user cannot modify the form, but they can modify the query if they have access to it. 2) The vast majority of my forms open with an empty recordset and is only populated once the user has made at least one 'search' choice. It is much easier to modify the recordsource to include the criteria rather than the query. And in my experience there are very few instances where the same query would be used in more than one form recordsource, so 'bind' them together.

The important thing is not to base a form on a whole table (or a query that returns a whole table) for subsequent filtering on a form - that will result in the whole table being brought across which will have a significant impact on performance for large recordsets. Note that the docmd.openform WHERE parameter is actually a filter
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
@CJ_London thank you for your detailed reply. and i think where my lack of understanding and worry is coming from is basically highlighted by your final paragraph.

Currently i have a continuous form which gives the user a view of currently in progress deliveries with a bit of summary information. the user can then double click on any of the lines of the continuous forms (as in a specific record) and open up a form that displays all of the details. i do this as follows:

Code:
Private Sub cons_albaran_DblClick(Cancel As Integer)
    DoCmd.OpenForm "form-Consignment", , , "cons_id=" & cons_id, , acDialog
End Sub

So this opens the form with the correct record using the record ID as the WHERE condition and the pulls in information in sub forms related to that record.

As far as i can understand from your last paragraph this is NOT the best way to open this form as this will pull the whole table and filter it using the WHERE condition. So if i understand that correctly my question is that if thats not the way to open a form the most efficiently what is the better way to do it. I'd really appreciate pointers/direction as this will help my understanding of best practices that i'm retro actively adding in to my database creation so that i can improve things
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Feb 19, 2013
Messages
16,553
it is easy to check - if not showing already, display the form navigation buttons - you will see the filter button is active (coloured). click on it to remove the filter and all records will be displayed. Aside from potentially loading an entire table, this can have serious consequences if a user is only supposed to be able to see certain records (those limited to their branch or department for example)

there are a number of ways, depending on your requirements and actual setup but as a principle you might have

SELECT * FROM myTable WHERE False

as the recordsource to your form - this will return an empty recordset

for your openform command, pass your criteria ( "cons_id=" & cons_id) as an openarg parameter rather than the Where parameter

in your form open event put

me.recordsource=replace(me.recordsource,"False",me.openargs)

That is basically it. depends how your forms work so there may be more to do

Another way is to assign a recordset so your dblclick event might have code something like this (freetyped so may have errors)

Code:
Private Sub cons_albaran_DblClick(Cancel As Integer)
dim rs as dao.recordset
    
    set rs = currentdb.openrecordset("SELECT * FROM myTable WHERE cons_id=" & cons_id, dbfailonerror)
     if not rs.eof then
         DoCmd.OpenForm "form-Consignment", , , , acDialog
         set forms!form-Consignment.recordset=rs
     else
         msgbox "No record found" 'usually would apply if your were using this for a search so not really relevant here but shown FYI
     end if

End Sub
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
@CJ_London this helps alot, i will try out what you suggested, i'd like to definitely improve the way things are done so this will certainly help, i'll update when i've added it to confirm that i've understood what you have supplied, once again thank you
 

LGDGlen

Member
Local time
Today, 12:18
Joined
Jun 29, 2021
Messages
229
@CJ_London ok so things i have found so far:

the second option you suggest won't work for my main form right now as it opens in acDialog mode which means the next line won't run till i exit the form so it won't set the records for the form

the first option proved "interesting" in that when the form loads it previously did the following:
Code:
    If Me.NewRecord Then
        Me.btnAddProduct.Enabled = False
    Else
        If Len(Me.OpenArgs) > 0 Then
            Me.RecordSource = Replace(Me.RecordSource, "False", Me.OpenArgs)
        End If
        Me.btnAddProduct.Enabled = True
    End If

so when i use openform with the consignment ID as the arguments it always ended up in the Me.NewRecord bit. Dawned on me that if arguments are present then its not a new form so changed it to:

Code:
    If Len(Me.OpenArgs) = 0 Then
        Me.btnAddProduct.Enabled = False
    Else
        Me.RecordSource = Replace(Me.RecordSource, "False", Me.OpenArgs)
        Me.btnAddProduct.Enabled = True
    End If

That solved it so i'm happy thats resolved it, i am going to look into the other forms i have and as they are pretty much all popup forms from the main consignment form option 2 that you suggested will be possible to do but i'm going to do them 1 at a time to minimise impact and help with testing
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
42,970
There used to be more of a difference between the two methods but now Access saves those embedded queries as querydefs. Open MSysObjects and look for queries that start with "~", Those are the queries for Record nd RowSources.

However, I have found bugs in Access that occassionally break the embedded SQL. It is always when the string is long and complicated so it is always a problem. Therefore, I always use external saved QueryDefs rather than embedding the SQL string.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Feb 19, 2013
Messages
16,553
glad you got it working

Must admit my form style is to use a single main form with a number of subforms - one for menu, one for information, another for the 'active' form, etc. I provide a facility for the user to be able to 'lift' the active form off the main form as a popup form so they can for example compare two records side by side. I(Bit like a web browser opening a link in a new window).

@Pat - never had a problem myself
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
42,970
@Pat - never had a problem myself
I guess I'm just lucky. If you ever get lucky, you'll understand why I use saved querydefs:) I reported the error a few times but I've never been able to determine what caused the problem to begin with. All I can say is it only happens to long/complex queries which are the ones you don't want to have to recreate.
 

Users who are viewing this thread

Top Bottom