none
Access app. with SQL Backend DB with linked tables RRS feed

  • Question

  • I am migrating an Access FE/BE application to Access FE and SQL Server BE DB. The main data entry form uses parameters table stored in the FE Access app. and a subform which contains the main data table stored in the BE. Normally you follow the default Access behavior where you simply select the Front End table (Main Form), the BE table (linked SQL Server table thru subform), and it works OK. Some questions:

    1. Does Access pull the entire main table from SQL server to the client computer, and then apply the filter to the subform? IF this is the case, then it is a waste of network resources and client computer resources.
    2. If answer to question 1. is True, Is there a better way to accomplish this?
    3. If I also store the Parameters table (contains only 1 row with the user's Year/Week/Username selection, used for filtering the data entry and for filtering reports) in SQL server and Link to it (Access linked table), will Access still pull both tables to the client computer and then apply the filter?

    Adrian Hernandez

    Monday, October 12, 2015 10:04 PM

Answers

  • Hi Adrian,

    >> main data entry form uses parameters table stored in the FE Access app. and a subform which contains the main data table stored in the BE.

    What did you mean with “parameters table stored in the FE Access app”? I know parameter query, but I know little about parameter table. Based on my understanding, when you use a FE/BE Access application, the data are stored in SQL Server, and the tables in FE Access are linked to BE tables in SQL Server.

    >> 1.Does Access pull the entire main table from SQL server to the client computer, and then apply the filter to the subform?
    I think Access does. When you open a form or datasheet of table, Access FE query the data from SQL Server, and store it at client computer. Before you refresh the form or post new request, the data would not change. Comparing with shared database that is not split, it sent data across the network instead of database objects themselves — tables, queries, forms, reports, macros and modules. It will not waste less resources, and it improves the performance.

    >> Is there a better way to accomplish this?
    I am afraid not, Comparing with shared database that is not split, it sent data across the network instead of database objects themselves — tables, queries, forms, reports, macros and modules. It will not waste less resources, and it improves the performance.

    >> 3.If I also store the Parameters table (contains only 1 row with the user's Year/Week/Username selection, used for filtering the data entry and for filtering reports) in SQL server and Link to it (Access linked table), will Access still pull both tables to the client computer and then apply the filter?

    Yes, you could store the tables in SQL, and link them from Access. The tables would work at client computer.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by ahpitre Wednesday, October 14, 2015 1:32 PM
    Tuesday, October 13, 2015 7:51 AM

All replies

  • Hi Adrian,

    >> main data entry form uses parameters table stored in the FE Access app. and a subform which contains the main data table stored in the BE.

    What did you mean with “parameters table stored in the FE Access app”? I know parameter query, but I know little about parameter table. Based on my understanding, when you use a FE/BE Access application, the data are stored in SQL Server, and the tables in FE Access are linked to BE tables in SQL Server.

    >> 1.Does Access pull the entire main table from SQL server to the client computer, and then apply the filter to the subform?
    I think Access does. When you open a form or datasheet of table, Access FE query the data from SQL Server, and store it at client computer. Before you refresh the form or post new request, the data would not change. Comparing with shared database that is not split, it sent data across the network instead of database objects themselves — tables, queries, forms, reports, macros and modules. It will not waste less resources, and it improves the performance.

    >> Is there a better way to accomplish this?
    I am afraid not, Comparing with shared database that is not split, it sent data across the network instead of database objects themselves — tables, queries, forms, reports, macros and modules. It will not waste less resources, and it improves the performance.

    >> 3.If I also store the Parameters table (contains only 1 row with the user's Year/Week/Username selection, used for filtering the data entry and for filtering reports) in SQL server and Link to it (Access linked table), will Access still pull both tables to the client computer and then apply the filter?

    Yes, you could store the tables in SQL, and link them from Access. The tables would work at client computer.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by ahpitre Wednesday, October 14, 2015 1:32 PM
    Tuesday, October 13, 2015 7:51 AM
  • I finally got this to work. I had to use and unconventional approach. The subform uses a SQL view to only get the subset of data needed by the user when he changes the filter parameters (Main form). The difference in performance is amazing, the original approach (that brings the entire table to the client) was bringing 13K records, the new approach brings less than 10 records.

    The downside to this approach is that the view allows adding and updating records, but not deleting them (SQL View uses 2 tables that is why deletions are not allowed). To overcome this I trap the Access error, run my own custom code to delete the BE record via a pass-through query and a Stored procedure.

    Thanks for your help and advice.


    Adrian Hernandez

    Wednesday, October 14, 2015 5:22 PM