Solved Need Assistance: How to update record from form listbox? (1 Viewer)

[Pat Hartman]

A recorset allows you to position yourself in a record and modify the data, both in the form of a continuous list and in the form of fields.

Unless I don't understand the problem and I'm wrong.

If you've never used SQL Server or other RDBMS, you won't automatically understand why using a search box to find the record you want to update is actually best practice. Access is very tightly bound to Jet and ACE and so if one of those is the BE database, Access works pretty efficiently. Once you move up in the world to one of the "big guys", you want to think about how to make the process more efficient and that means letting the RDBMS to the heavy lifting. You want Access to send a query to the server requesting the minimum number of rows/columns for any particular update. Best is just one single row for a bound form. That minimizes the traffic between Access and the server and reduces the load on the server.

If you've ever converted an old Access application to link to tables in SQL Server and didn't make any modifications to the app, you were probably flabbergasted to find out that the SQL Server version was much slower if the linked table contained more than a trivial number of rows. Why? Because when you bind a form/report to a table, you force Access to request ALL rows be downloaded from the server and brought into memory on your local PC. VERY inefficient in the relational database world. Just bring specifically what you need to update NOW. So, the very first and possibly only change you need to make is to change your bound forms to be bound to queries with WHERE clauses to minimize the network traffic. You may never need to do anything else to get acceptable speed.

 

[Luis Fernandez]

If you've never used SQL Server or other RDBMS, you won't automatically understand why using a search box to find the record you want to update is actually best practice. Access is very tightly bound to Jet and ACE and so if one of those is the BE database, Access works pretty efficiently. Once you move up in the world to one of the "big guys", you want to think about how to make the process more efficient and that means letting the RDBMS to the heavy lifting. You want Access to send a query to the server requesting the minimum number of rows/columns for any particular update. Best is just one single row for a bound form. That minimizes the traffic between Access and the server and reduces the load on the server.

If you've ever converted an old Access application to link to tables in SQL Server and didn't make any modifications to the app, you were probably flabbergasted to find out that the SQL Server version was much slower if the linked table contained more than a trivial number of rows. Why? Because when you bind a form/report to a table, you force Access to request ALL rows be downloaded from the server and brought into memory on your local PC. VERY inefficient in the relational database world. Just bring specifically what you need to update NOW. So, the very first and possibly only change you need to make is to change your bound forms to be bound to queries with WHERE clauses to minimize the network traffic. You may never need to do anything else to get acceptable speed.

I understand and you are absolutely right.
Although with the new equipment speeds, fiber optics, SSD and M.2 hard drives, I don't know if it's still necessary to bother.
My systems search floating in 33000 rows to choose from while typing a text and filter the live records within a combobox for the user to select from a small list, and all this while typing each letter and 5 people at a time on separate computers.
So I think we should take new times and updated concerns.
but of course you are very right, no joke

Combobox display of 32000 live records, floating search
The data is on a separate server and the hardware store has 5 points of sale.
It is with access 2003 but also works with access 2016 32 and 64 bits
Spanish version: Ventas = Sales

Here you can see purchases, choosing between 32,000 items and moving through each purchase invoice taking the descriptions of the products live from inventory on the server
Spanish version: Compras = Shopping