Hi Everyone,
I made a small Access databse, its a split database, I have the tables back end shared in a network folder and the front end on the desktops. in the local network this option offers very good performance, easy to maintain and very basic. but when sharing it over a WAN its terribly slow (makes sense).
I migrated the tables to SQLExpress2017, the forms can connect without any problems and the performance is a lot better over the WAN, but still slow. I read that its because all my queries are being calculated on the forms and should instead be calculated in the SQL server. Then I bumped into something called query pass-through, easy enough but the queries in my forms (all of them) have combo boxes as criterias. unfortuntately (so it seems) this does not work in SQL because SQL does not see the Forms, so the criteria part of my query has to be removed.
then I read about pass-values and stored-procedures. But I really need to know if I'm understanding this correctly so I can continue my research (this is where you all come in):
basically its telling my combo boxes to 'pass-value' the value selected by the user to the SQL stored-procedure created in the SQL server. the stored-procedure would then find the value needed from the tables and pass the value on to my access form.
is this how it works?
if so, how the heck do I tell the combo box to send the value to the store procedure, and tell the stored procedure to send the value found to the text box in the form? I just need an idea and I can figure out the rest.
tks
Eric