Filtering a data entry continuous form in MS Access / Cloud Database (1 Viewer)

[nector]

I have a continuous form, that is the parent form and child form all linked to separate tables on cloud database, this form is used for data entry and its property called date entry is marked as ‘YES’ as result once it opens you cannot see any exiting data its completely empty.

Symptoms:

• The cursor moves from one filed to the next normally like it is in MS Access as long as there is no calculated control. At this point no problem, the same also happen with the subform.
• Problems areas, the switching from the parent form to the child form takes 45 seconds and the same happens in the subform when creating a new line, if you want to create 10 lines in the subform the cursor will take 450 seconds which is very bad.

Referring to my last posting one expert said I must filter the record. How am I going to filter the form because this not a report and it's not attached to any query but tables. He went on to say the form could be loading the entire record, okay agreed, then how do I filter this so that it loads without any underling records.

Remember here I’m not editing anything, but adding new record which means that I will not be able to use WHERE CLAUSE which used in editing a specific record.

The attached database uses MS Access, and it is fast, that is the kind of speed missing when the same is linked to Cloud database.



Please advise with examples or show the filtering using the attached database, that way it will help me a lot to understand the concept. THIS PART IS REALLY CAUSING ME TROUBLE, ITS JUST A SMALL PART, BUT WHAT MAGIC IS REQUIRED HERE??????????????

Following are working super-fast no issues at all.

(1) Reports with over 8 million records works excellent
(2) Queries after converting to views excellent
(3) Action after converting to passthrough queries works fast
(4) Combo Boxes after using the code below works fast

Dim lngCount as Long
lngCount=cboBox.ListCount

 

[Minty]

What happens if you leave the AccountID and CostID lookup tables as local, instead of linked?

Where are the calculated controls, I can't see any on the subform?
Do you mean the ones on the main form? They aren't visible - so what purpose do they serve?

 

[nector]

What happens if you leave the AccountID and CostID lookup tables as local, instead of linked?

The accountID keep on changing frequently except the costID. I removed all calculated controls to at least improve performance. The calculated controls were on the subform and are no longer there except just one on the main form to help balancing the journal processing.

 

[Minty]

Okay, but what happens to the performance if you make those changes (local versions of those two tables), do things get better?

 

[nector]

Thank you so much for the advice, yes there is marginal improvement, I think I leave it that way because If I use the ethernet cable again the software appear too much better than wireless.

 

[Minty]

Don't use wireless with Access unless you have no other choice, although SQL server and the later ODBC drivers make it more robust it's still not recommended.

It doesn't like the intermittent nature of the connectivity, and it can drop out with no warning/go to sleep etc.

 

[The_Doc_Man]

This article that I have linked for you discusses the difference between hard-wired and WiFi connections in general.

https://enterprise.spectrum.com/support/faq/network/what-is-the-difference-between-wifi-and-ethernet-connection.html

While speed always depends on configurations, the article clearly states that most wireless connections are likely to be 10 times slower than modern hard-wired or fiber networks. Reliability is also an issue EVEN if you have been tinkering with the "connection retry" settings available for ODBC connections. If you HAVE NOT been playing with that setting, look it up and adjust it to enhance what little stability there is with WiFi in play.

Open up the Windows command prompt and type NETSTAT -S to see some basic statistics on your network. (It would be best if you could do this from the cloud side to see what the overall stats look like.) You will probably get several sets of data for different kinds of connections. Look among each group for "retransmitted" and "errors" statistics. With a busy WiFi you would probably see a LOT of both of those factors. The more of those you see, the bigger the risks you are taking by using WiFi. I'm with Minty on this one.

You want the freedom of wireless, but wireless isn't free if you also want reliability. And if this is a vital part of your business, you will find that there is a Heisenberg Uncertainty principle (of a sort) in effect. You can have freedom and you can have reliability but you cannot have both at the same time. Every time you choose "free" you risk the stability of your database. All it takes is for a complex transaction to fail in mid-connection. Using ODBC you are less likely to crash the DB, but you make the SQL system work that much harder to roll back incomplete transactions.