Forms and Subforms too slow in data capturing to Cloud SQL Server

[nector]

I have migrated my Ms. Access database to cloud sql database using the ODBC connection tables strings all the users are able to connect their front end to cloud server using any form of internet and its fast just like as if you are connected to the local database:

(1) Reports are quite fast to load I think because of the indexes I applied to binding queries

Data capturing Problem

This is where the problem is! the forms open quite fast BUT takes too much time to enter values on the tables, the calculated control keep on calculating far too long. Any solutions to this problem?

 

[GPGeorge]

We'd need details about HOW the data entry form works. Is it bound to a table or to a query, for example?

If you open the form while bound to an unfiltered table, for example, ALL of the existing records must be pulled "from the cloud" into the form's recordsource, even though you don't need them in order to add new records, or to edit one record. Moving data back and forth slows it down, sometimes a lot. If you filter the recordset using a parameterized query, on the other hand, only one record has to be returned for editing, and for adding new records, you don't have to pull any existing records down that long wire to "the cloud".

That's one example of design patterns that do work fine in local Access FE/BE installations which fall over when converted to local Access FE/SQL Server remote BE installations. But the more you explain the workings of this data entry form, the better the odds are that the experts can help you address specific problems.

 

[The_Doc_Man]

Points of curiosity that might or might not lead anywyere:

1. In this cloud-based SQL server solution, are you using any method to maintain a persistent connection between FE and BE?

2. In this slow-functioning calculated control, how many BE fields are involved?

2.1 Of the BE fields involved, how many of them include ANY of the Domain Aggregate functions?

2.2. Of the BE fields involved, how many of them include ANY data not obtained from the current record from the recordset that drives the form?

It is possible that these questions will not lead anywhere, but it should be easy enough to find the answers.

 

[GPGeorge]

I'm not sure a persistent connection has the same impact on cloud-based SQL Server solutions as it does on local accdb FE/BAE BE solutions.

 

[CJ_London]

much the same question asked here (as a follow on)

How to connect users to from Ms Access to SQL Server Express

Introduction Pardon me people on this one my full training will be paid in January 2023 for Ms. Access / SQL Server. I have finally installed the SQL server after following the procedures below: (1) Marching my ODBC 32 BIT to Ms. Access 32 BIT and compose a string with ODBC [SQL Server Client...

www.access-programmers.co.uk

 

[Pat Hartman]

Training in SQL Server isn't the issue here. It is training in Access you need. I'm pretty sure you were told in the other thread to expect this slowness and that the design as you have it was not a good idea.

As the others have suggested, you can do some things to speed up the form but don't expect miracles.

Let me summarize. NEVER, EVER bind a form to a table. PERIOD!!!! Forms should always be bound to a query that selects one record whenever possible, at least for updating.

If you are using combos on the form, each of those will be running a query to populate its RowSource. If these RowSources are fairly static lists, you might consider downloading them to tables in the FE when the app opens and use the local tables to populate the RowSource queries.

If you are using domain functions, each of those will be running a query.

If your queries include items that cannot be parsed by the SQL Server, then Access will request ALL table rows from the server for ALL tables involved in the query and then perform the functions locally. You really want to avoid this at all costs. Therefore, you need to be conscious of any functions you use in a query and whether or not, they can be converted directly to T-SQL. Some VBA functions have equivalents, so using Date() and Now() won't be a problem. Some other VBA functions such as Format() might be able to be converted to CAST but I'm not sure if all can be. If the function is in the Select clause, Access should be able to pick the data field arguments out of the call and select them and then apply the function once the record is returned. But functions in the WHERE clause will always be a problem as may function in other areas. Access tries its best to pass-through all queries to the server so you need to try to stay out of its way or pay the price with slowness.

 

[nector]

Many thanks to you all I have heard everything you have said, after going through one issue at a time I have discovered that the cloud database works ok like the local SQL express on the server.

Problems areas:

(1) Combo box: this is where the major slowness is, right now I'm thinking of how to generate a pass-through query since users do not need to edit and write data there.

Below is a sample SQL for the lookup combo box which I want to turn it into a pass-through query, the challenge I will have is how to use VBA to open the record source and attach this query

SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.BarCode, tblSalesTax.TaxClass, tblPricing.Prices, tblPricing.RRP, tblSalesTax.VatRate, tblSalesTax.Tourism, tblSalesTax.Insurance, tblSalesTax.TourismLevy, tblPricing.TaxInclusive, tblSalesTax.InsuranceRate, tblSalesTax.InsuranceRate AS Premium, IIf(([TaxClass]="D"),Round(IIf(([TaxClass]="D"),[Prices],([Prices]/1.16)),2),Round(IIf(([TaxClass]="C3"),[Prices],([Prices]/1.16)),2)) AS ExportPrice, tblPricing.NoTaxes, tblProducts.Sales
FROM tblSalesTax INNER JOIN (tblProducts INNER JOIN (tblcartergory INNER JOIN tblPricing ON tblcartergory.CartID = tblPricing.CartID) ON tblProducts.ProductID = tblPricing.ProductID) ON tblSalesTax.IDClass = tblPricing.IDClass
WHERE (((tblProducts.Sales)=Yes))
ORDER BY tblProducts.ProductID DESC;

For the Calculated control which are also not working they are looking like below done on a form

Sum((((([SellingPrice])/(1+Nz([Tax],0)))+(IIf([SellingPrice]>[RRP],(([SellingPrice]*Nz([Tax],0))/(1+Nz([Tax],0))),(([RRP]*Nz([Tax],0))/(1+Nz([Tax],0))))))*[QtySold]))

 

[Pat Hartman]

Below is a sample SQL for the lookup combo box which I want to turn it into a pass-through query, the challenge I will have is how to use VBA to open the record source and attach this query

Access makes every effort to "pass through" ALL queries. There is always a small amount of overhead with using standard Access queries because Access has to look at them first before passing them through but the difference is not likely to be significant.

The more important questions are:
1. how many rows will your RowSource actually return?
2. do you really need to join four tables?
3. Are you using all the fields you are selecting?
4. Do you really need to do the calculations in the RowSource?

Sometimes, you can speed up queries by turning them into views. Given the joins and the calculations, this might be an option.

 

[Minty]

Lookup lists that are used in combo's - two potential solutions:
a) If the data is limited to infrequently changed look-up's create a local table copy of it and use that as the source for the combo.
e.g. tbLocal_States. Update the local copy at database load just in case Alaska ever leaves.
However - never join any local table to a server table, therein lies the road to poor performance.

b) As Pat states - anything that involves calculations - making it pretty much un-editable - should be done in a view on the server. The performance difference is immense, as you are directly bringing in the results, rather than the data, then calculating the results.
Again if this is predominantly unchanging data you can create a local version of it at database open. If it's just for presentation, you can even format it in the view avoiding another secondary step.

 

[nector]

Many thanks to you all at least you have given me some ideas as follows:

(1) Bind the combo box to a pass - through query no calculations
(2) All calculation should be done in views

Point (1) has worked pretty very well
Point (2) I have more work here because it depends on the product selected and perform the taxes classes, tourism levy, decide whether export or local sales and then update the sub form with the correct figures and tax labels.

However, many thanks to you all, now its time to fix the remain issue.

Regards

Chris

 

[Pat Hartman]

Bind the combo box to a pass - through query

That isn't what I said.

Keep in mind that pass through queries are not updateable so binding them to forms makes the form not updateable. That will be a huge change to your application so it is the LAST option, not the first.

 

[nector]

So, in this case I remain with the views as an option