none
Advice about form structure in access RRS feed

  • Question

  • Hi,

    I would like to ask how to make my access project faster and without extensive lags. Ill provide some info first.

    Project consist of about 60 tables, most of them in relationships and a main form with about 30 subforms - most of those are loading data from multiple tables. All the fields are meant to be editable by user. Tables were moved to mysql server which introduced huge lags. All forms do select from linked tables now, yet it seems like these tables have tendency to update as a whole before every action. With some it will even warn me that the data were changed before he manage to go tru all subforms. It looks like every action will start some huge cascade of refreshing of all these tables, maybe even multiple times.

    I need some theoretical advice about changes to be made here.

    I was thinking about pass-through query, but they cant update and it looks like a lot of vba code. Maybe divide forms to select and update, while only update part will work with tables? Should i take apart subforms, make more closed groups and then load them as necessary, if thats even possible? Server and communication with it are without problem, also actions with a single linked table are instant.

    Im not pro when it comes to access, so any advice about architecture of project and possible changes are welcome.

    Friday, June 7, 2019 11:43 AM

Answers

  • You can attempt to adopt pass-through queries, calls to store process etc...

    However, for the most part, you don’t really need any of the above to get great performance with say SQL server, or your case of MySQL.

    The first issue is to think of how just about any software package you used works.

    If you say fire up a QuickBooks, or some accounting package?

    Well, they NEVER just toss up a form say with 100,000 rows.

    Why load 100,000 rows into a form to THEN work JUST on one record?

    You can bind an access form directly to a table of say 500,000 rows.

    However, WHEN you launch that form? Ask the user BEFORE you launch that form, what record.

    So, you go:

    Dim strInvoiceNum    as string

    strInvoiceNum = inputbox("What invoice to work on")

    Now, when you open that form, filter the form to the one record like this:

    Docmd.OpenForm "frmInvoiceEdit",,,"InvoiceNum = " & strInvoiceNum

    The access form will and should load instant.

    Even if the form does NOT use a query but is bound directly to the linked table (that resides on MySQL), only ONE record will be pulled down the network pipe.

    Think of when you use Google.

    You don’t download the “whole” internet into the browser, and then say use control-f to search the HUGE result set. (You first type in and search).

    So the #1 tip and trick here is to LIMIT the data you pull into a form.

    And you do this by simply asking what the user wants to look for and work on before you launch a form to edit data.

    In fact, the above advice not only applies to web based, desktop based, accounting packages, but also works equally well for Access forms.

    In other words, good software design will simply limit the records pulled. So, pull the data “as you” work, and “only” what you need.

    Using vb.net, or Access to hit MySQL server runs at the SAME speed.

    The network connection does not go, hey, this is .net code, and then decides to pull less data over the network. If Access (or vb.net) application was not designed to restrict data pulled?

    Then all such applications will run poor. It has near zero to do with Access.

    Now, of course the above is a horrible user interface.

    So, most of the time, you want to provide some type of search form for the user.

    They can say type in part of the company name, or whatever, and then display the search results.

    So, say a form like this:

    In above, the user typed in a bit of the name, and then we display the results.

    (the above is a standard Access form - a continues one).

    Now, you can click on a row (the glasses icon in above), and you code simply launched the one form to the ONE record.

    As for 20 sub forms? That REALLY sounds excessive.

    Are the 20 sub forms related to the “main” record, or is this just 20 forms being loaded?

    And even if the 20 sub-forms are in fact related to the ONE main master record?

    Well, why load 20 sub forms when the user not even used say 19 of them yet?

    So, if the sub forms are behind a tab control, then ONLY load the sub form when the user clicks on a particular tab.

    So, just like searching, you don’t load up everything under sun and THEN let the user work.

    With the above nice form, then:

    User searches.

    Picks the one record to work on.

    And when done say on the phone with the customer, they close the one form, and are right back at the search form to do battle with the next customer or task at hand.

    The other great part about the above? Well, if the user just closed the form, then the data is saved, and users “feel” they just done that task. But MORE important, in a multi-user environment, any other user can now search for that data, and it will be instant available to all users.

    If you simply load up a form with a huge number of records, and anther user adds a new customer, you will NOT see that new customer until such time you re-load the form (or do a re query and re-pull all the data).

    So, when you load up a form, new data added by other users will NOT be seen nor available until such time you re-load that form.

    But with the above work flow and cycle? Well, you ALWAYS close out your main edit form, thus making the data and record available to all users. (Because now users FIRST tell access what data and record they want to work on).

    So the above workflow is the hallmark of ANY application you will use. Don’t pre-load up massive amounts of data – because the users not figured out what data or record they need to work on just quite yet!

    So, for the most part, good performance is achieved though the “design” and your approach. Not that you need to use pass-though, or other fancy tricks.

    Access ONLY pulls the records you tell it to. And if you don’t restrict the records pulled, then Access is only doing what you are telling it to do (or not do).

    A few more tips:

    You will rare need pass-through quires, and rare require store procedures.

    However, if you going to launch a form (or more often a report?).

    If that report is based on a complex sql query?

    (One with multiple joins – multiple tables).

    In this case, access often does a poor job, and will pull tons of extra data.

    So for a complex multipole table query?

    Convert the query to a view, and then link to the view.

    Then base the form (or report) directly on that linked view.

    Once again, then all of the complex “dance” and join of the data will occur server side.

    And again, to restrict the records to that form? Use a where clause. Access does a VERY good job of filtering views, but of course that assumes you the developer choose a design in which you restricted the records to be pulled.

    Keep in mind this “join + views” trick has nothing to do with say a main form, and sub forms. Because sub forms are automatic filtered from the main form, then in most cases, you don’t have to do anything. So the main form, and sub forms are STILL based directly on the linked table (and most forms will STILL be based on a single table, or view).

    In the above example search form, because it is going to be open (likely all day long – since you can’t work on any record until you find it, right?), then you can’t use the where clause.

    So, in the after update event of the text box, we have this code:

    dim strSql          as string

    strSql = "select * from tblCustomer where LastName like " & me.txtLastName

    & "*"

    me.MySubFormname.Form.RecordSource = strSql

    So, note how in above, I created the sql string and simply shoved it right into the forms record source. (Or in above example, the grid part of the form was in fact a sub form).

    On demand loading of sub forms.

    As noted, it makes VERY little sense to load up 20 sub forms.

    Only load the data and sub form as required.

    (Use the on-change event of the tab to achieve this).

    You can read about this searching concept here:

    So, I would shy away from pass-through query, and store procedures.

    And VERY careful with PT query, or store process. The Access client CAN NOT and DOES not filter a PT query or a store procedure.

    So, do NOT use PT query to fill a combo box, unless you restrict the data for that combo box (or it is an un-bound combo box).

    So for bound combo boxes, do NOT use PT query or store process – they are NOT filtered by the client.

    (However, a view is fine).

    At the end of the day, using views is certainly the least amount of work, and gives near PT query performance. However, MORE important is your existing VBA code that filters forms does not need to be changed.

    So far, not really knowing how this application was setup? It looks like data being pulled into forms was an afterthought.

    The above concept to restrict data works very well for Access even when you not using a server based back end.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada


    • Edited by Albert D. Kallal Monday, June 10, 2019 10:25 PM
    • Marked as answer by Jurco Wednesday, June 12, 2019 1:56 PM
    Monday, June 10, 2019 10:24 PM

All replies

  • Hi,

    To help you better resolve the problem, I will move the thread to Access for developers forum. You may get more helpful replies there.

    Thanks for your kind understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Monday, June 10, 2019 3:00 AM
  • You can attempt to adopt pass-through queries, calls to store process etc...

    However, for the most part, you don’t really need any of the above to get great performance with say SQL server, or your case of MySQL.

    The first issue is to think of how just about any software package you used works.

    If you say fire up a QuickBooks, or some accounting package?

    Well, they NEVER just toss up a form say with 100,000 rows.

    Why load 100,000 rows into a form to THEN work JUST on one record?

    You can bind an access form directly to a table of say 500,000 rows.

    However, WHEN you launch that form? Ask the user BEFORE you launch that form, what record.

    So, you go:

    Dim strInvoiceNum    as string

    strInvoiceNum = inputbox("What invoice to work on")

    Now, when you open that form, filter the form to the one record like this:

    Docmd.OpenForm "frmInvoiceEdit",,,"InvoiceNum = " & strInvoiceNum

    The access form will and should load instant.

    Even if the form does NOT use a query but is bound directly to the linked table (that resides on MySQL), only ONE record will be pulled down the network pipe.

    Think of when you use Google.

    You don’t download the “whole” internet into the browser, and then say use control-f to search the HUGE result set. (You first type in and search).

    So the #1 tip and trick here is to LIMIT the data you pull into a form.

    And you do this by simply asking what the user wants to look for and work on before you launch a form to edit data.

    In fact, the above advice not only applies to web based, desktop based, accounting packages, but also works equally well for Access forms.

    In other words, good software design will simply limit the records pulled. So, pull the data “as you” work, and “only” what you need.

    Using vb.net, or Access to hit MySQL server runs at the SAME speed.

    The network connection does not go, hey, this is .net code, and then decides to pull less data over the network. If Access (or vb.net) application was not designed to restrict data pulled?

    Then all such applications will run poor. It has near zero to do with Access.

    Now, of course the above is a horrible user interface.

    So, most of the time, you want to provide some type of search form for the user.

    They can say type in part of the company name, or whatever, and then display the search results.

    So, say a form like this:

    In above, the user typed in a bit of the name, and then we display the results.

    (the above is a standard Access form - a continues one).

    Now, you can click on a row (the glasses icon in above), and you code simply launched the one form to the ONE record.

    As for 20 sub forms? That REALLY sounds excessive.

    Are the 20 sub forms related to the “main” record, or is this just 20 forms being loaded?

    And even if the 20 sub-forms are in fact related to the ONE main master record?

    Well, why load 20 sub forms when the user not even used say 19 of them yet?

    So, if the sub forms are behind a tab control, then ONLY load the sub form when the user clicks on a particular tab.

    So, just like searching, you don’t load up everything under sun and THEN let the user work.

    With the above nice form, then:

    User searches.

    Picks the one record to work on.

    And when done say on the phone with the customer, they close the one form, and are right back at the search form to do battle with the next customer or task at hand.

    The other great part about the above? Well, if the user just closed the form, then the data is saved, and users “feel” they just done that task. But MORE important, in a multi-user environment, any other user can now search for that data, and it will be instant available to all users.

    If you simply load up a form with a huge number of records, and anther user adds a new customer, you will NOT see that new customer until such time you re-load the form (or do a re query and re-pull all the data).

    So, when you load up a form, new data added by other users will NOT be seen nor available until such time you re-load that form.

    But with the above work flow and cycle? Well, you ALWAYS close out your main edit form, thus making the data and record available to all users. (Because now users FIRST tell access what data and record they want to work on).

    So the above workflow is the hallmark of ANY application you will use. Don’t pre-load up massive amounts of data – because the users not figured out what data or record they need to work on just quite yet!

    So, for the most part, good performance is achieved though the “design” and your approach. Not that you need to use pass-though, or other fancy tricks.

    Access ONLY pulls the records you tell it to. And if you don’t restrict the records pulled, then Access is only doing what you are telling it to do (or not do).

    A few more tips:

    You will rare need pass-through quires, and rare require store procedures.

    However, if you going to launch a form (or more often a report?).

    If that report is based on a complex sql query?

    (One with multiple joins – multiple tables).

    In this case, access often does a poor job, and will pull tons of extra data.

    So for a complex multipole table query?

    Convert the query to a view, and then link to the view.

    Then base the form (or report) directly on that linked view.

    Once again, then all of the complex “dance” and join of the data will occur server side.

    And again, to restrict the records to that form? Use a where clause. Access does a VERY good job of filtering views, but of course that assumes you the developer choose a design in which you restricted the records to be pulled.

    Keep in mind this “join + views” trick has nothing to do with say a main form, and sub forms. Because sub forms are automatic filtered from the main form, then in most cases, you don’t have to do anything. So the main form, and sub forms are STILL based directly on the linked table (and most forms will STILL be based on a single table, or view).

    In the above example search form, because it is going to be open (likely all day long – since you can’t work on any record until you find it, right?), then you can’t use the where clause.

    So, in the after update event of the text box, we have this code:

    dim strSql          as string

    strSql = "select * from tblCustomer where LastName like " & me.txtLastName

    & "*"

    me.MySubFormname.Form.RecordSource = strSql

    So, note how in above, I created the sql string and simply shoved it right into the forms record source. (Or in above example, the grid part of the form was in fact a sub form).

    On demand loading of sub forms.

    As noted, it makes VERY little sense to load up 20 sub forms.

    Only load the data and sub form as required.

    (Use the on-change event of the tab to achieve this).

    You can read about this searching concept here:

    So, I would shy away from pass-through query, and store procedures.

    And VERY careful with PT query, or store process. The Access client CAN NOT and DOES not filter a PT query or a store procedure.

    So, do NOT use PT query to fill a combo box, unless you restrict the data for that combo box (or it is an un-bound combo box).

    So for bound combo boxes, do NOT use PT query or store process – they are NOT filtered by the client.

    (However, a view is fine).

    At the end of the day, using views is certainly the least amount of work, and gives near PT query performance. However, MORE important is your existing VBA code that filters forms does not need to be changed.

    So far, not really knowing how this application was setup? It looks like data being pulled into forms was an afterthought.

    The above concept to restrict data works very well for Access even when you not using a server based back end.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada


    • Edited by Albert D. Kallal Monday, June 10, 2019 10:25 PM
    • Marked as answer by Jurco Wednesday, June 12, 2019 1:56 PM
    Monday, June 10, 2019 10:24 PM
  • Thank you very much, this article really did help me a lot. Exactly the right type of info that will help me to plan the project how it should be planned from the beginning.
    Wednesday, June 12, 2019 1:59 PM