Cursor Delays in movement between the Parent Form to The Subform (1 Viewer)

[nector]

After coming to the closing of this project I'm now experiencing the cursor delay movement from the main form or parent form to the subforum in MS access. This software uses cloud database. I had a similar problem with the items below but are now sorted out:
(1) Serial Port delay sending and receiving data
(2) Runing the double entry
(3) Reports compilations
(4) Combo boxes delays

Below is video link if you want to look at what I'm talking about:

 

[Minty]

It looks as if the subform is constantly calculating something.
What is the row source for the drop down - if it's a product list that doesn't change very often then make a local copy of it and use that, rather than a linked table. You can update it every time the form opens if needed unless it's got millions of rows.

 

[arnelgp]

if you are saving and retrieving your data on the cloud, then that is not an optimal solution.
in fact if the store/supermarket is in your Place, then surely the stocks are also in your Place
and not in the Cloud?
make all your transactions saved to Local db.
make a special utility to Upload it later (when the store closes).
this will make your db more responsive.

 

[nector]

Ok thanks noted but each shop has a minimum of 248000 product rows, the problem here you will find that some products are discontinued in some other supermarket others are still using them and new products are coming monthly. The ideal here is that all the 70 supermarkets must use the same database and the update of products must be done at head office not individual outlets. This is the company policy which I cannot change. Only the board of directors can change that if they are Truely convinced.

That is where my challenge is

Below is the row source of the productid combo box but based on a view where the calculation of prices, discounts, vat and other incentives is done

SELECT ViewtblProductSelect.ProductID, ViewtblProductSelect.BarCode, ViewtblProductSelect.ProductName, ViewtblProductSelect.TaxClass, ViewtblProductSelect.Prices, ViewtblProductSelect.RRP, ViewtblProductSelect.VatRate, ViewtblProductSelect.Insurance, ViewtblProductSelect.TourismLevy, ViewtblProductSelect.InsuranceRate, ViewtblProductSelect.TaxInclusive, ViewtblProductSelect.ItemCodes, ViewtblProductSelect.Tourism, ViewtblProductSelect.ExportPrice, ViewtblProductSelect.NoTaxes, ViewtblProductSelect.Sales, ViewtblProductSelect.TurnoverTax, ViewtblProductSelect.Bettings, ViewtblProductSelect.Excise, ViewtblProductSelect.ExciseRate
FROM ViewtblProductSelect
WHERE (((ViewtblProductSelect.TaxClass)="A" Or (ViewtblProductSelect.TaxClass)="B" Or (ViewtblProductSelect.TaxClass)="C3" Or (ViewtblProductSelect.TaxClass)="D" Or (ViewtblProductSelect.TaxClass)="TV" Or (ViewtblProductSelect.TaxClass)="BG" Or (ViewtblProductSelect.TaxClass)="E") AND ((ViewtblProductSelect.Sales)=Yes))
ORDER BY ViewtblProductSelect.ProductID, ViewtblProductSelect.ProductName;

Supprising even a small combo box also takes the same time

SELECT tblaccounts.AccountID, tblaccounts.AccountName, tblaccounts.AccStatus
FROM tblaccounts
WHERE (((tblaccounts.AccStatus) Is Null Or (tblaccounts.AccStatus)<>"2"));

 

[arnelgp]

i understand, but...
what if one location has no internet connection (disconnected), then the store cannot function or will not
accept any customer until the connection is restored?

 

[nector]

It's true the internet can be a challenge sometimes, it's a puzzle. But on this I'm now running out of Ideas but I'm not going to give up looking the hard problems I have managed to sort out.

 

[Minty]

If the stores can't update the product table then in that case load the products table to a local table in the database front end.
Replace it every time the database is loaded as part of a start-up routine.

It might take a while but if head office set a flag every time they updated it, you could check the flag and only refresh the local copy when that was set.

 

[nector]

Ok thanks we will discuss with my director on that one

 

[nector]

After a proper investigation and even bring back the product table it confirmed actual that the problem of slowness in the movement of the cursor and creating new lines has nothing to do with the forms but ODBC.

I'm currently using plan ODBC "SQL SERVER" which odbc is recommended for cloud? Or what are using yourselves.


The combo boxes perform normally after adding this code below on the form load event, though it has a small overhead on loading.

Dim lngProductID as Long
lngCount=ProductID.ListCount

Any suggestion on odbc?

 

[Josef P.]

Any suggestion on odbc?

Have you already implemented the "usual" optimizations for the interaction of Access with SQL Server?

Some examples:

• Use the appropriate indexes.
• Use the appropriate indexes.
• Use the appropriate indexes. ;-)
• Avoid joins in the frontend (This usually results in a suboptimal execution plan or a cumbersome data fetch, since everything usually runs through the primary key.)
• Timestamp data field in the table still helps with data changes (update through linked table).
• If possible, have the SQL code run directly in the server - for example, using an ADODB recordset with a direct OLEDB connection or a pass-through query. (The data source of a combobox can also be a recordset.)
• ... and much more can be found with the search engine of your choice.

I'm currently using plan ODBC "SQL SERVER" which odbc is recommended for cloud?

First of all: I don't have any experience with Cloud SQL Server (I've only done a few functional tests so far).
Since you probably only want to transfer the data encrypted, I would rather use the newer ODBC drivers. The old {SQL Server} driver should be able to do this as well, but I would rather use the newer ones. The newer ones definitely have the advantage that you could also use an Azure AD login.

 

[nector]

After using ODBC SQL Server 17 atleast the speed is better than the plain one . Now I can settle on this. All the indexes are done including the foreign keys

 

[Minty]

I would definitely recommend using version 17 or 18, it performs much better and handles dropped connections much more elegantly.
We use 17 and are migrating to 18 for new clients or on major updates for existing ones.

 

[nector]

Many thanks for your advice its valuable