Solved Rules to be implemented with Ms Access against SQL Server Cloud

[nectorch]

Maybe I’m missing something here, I have been following the rules listed below to implement my cloud-based database SQL Server, I can confirm the rules listed below have sorted out all my reports, they pull data almost instantly BUT not data entry forms, the parent and child forms have these characteristics:

• Opening or loading they take something like 15 seconds.
• Cursor movement from parent form to child the cursor takes 12 seconds.
• Combo selection takes 3 seconds (which is fine)
• Cursor movement in the data sheet or sub form from one line to create the new (Send Line) line it takes 10 seconds.

It's okay for A4 Invoices (wholesale or manufacturing) creation, but not POS receipts (Retail business) this can create congestion, maintaining some tables locally can create more problems because data changes frequently, I want not to take such a risk.

Rules to be implemented with MS Access against SQL Server Cloud

When using MS Access and SQL Server you could obey the rules listed below:

(1) open a form with only ONE record (e.g., with Open Form "xx", Where: ="ID = 100"

(2) Use first a form for filtering the data and then open an Edit-Form with only the selected record

(3) don't use complex queries as data source for a form

(4) don't use a whole table as data source for a form (when the table contains more than a dozen records)

(5) All complex queries must be converted into views

Questions

• I very sure most of the experts went through this, my question is how possible you can filter a data entry form which essentially load empty as per rule number one on top? If we were editing record only it's okay that filter will work, now here I’m talking about entering data into a table, new data for the first time which never existed before.
• Linking forms to views sometimes the forms create duplicate data when capturing and so this is not an option here. Is linking the forms to linked tables to bad or not?
• Currently all the shops are using Wi-Fi as the method of connecting to the cloud database and most of the laptops like Lenovo they come without the provision for ethernet cable, could it be the cause of this slowness? I need to be clear here also before advising clients to invest on wired internet.

The worry here is the point of sales only which require speed as the items are being scanned the data entry must be instant like in MS Access.

 

[CJ_London]

• I very sure most of the experts went through this, my question is how possible you can filter a data entry form which essentially load empty as per rule number one on top? If we were editing record only it's okay that filter will work, now here I’m talking about entering data into a table, new data for the first time which never existed before.

Criteria -to use is 'WHERE False' or 'WHERE 1<>1'

• Linking forms to views sometimes the forms create duplicate data when capturing and so this is not an option here. Is linking the forms to linked tables to bad or not?

ideally should never link forms directly to tables - use a query. to minimize the amount of data brought through. The same applies to combo's and listboxes, although a few records would not be a problem

• Currently all the shops are using Wi-Fi as the method of connecting to the cloud database and most of the laptops like Lenovo they come without the provision for ethernet cable, could it be the cause of this slowness? I need to be clear here also before advising clients to invest on wired internet.

Access and Wifi are not a good mix, although using a sql server back end helps to mitigate this. You can get adaptors that with conver a rj45 ethernet connector to USB

However overall performance very much depends on the speed of the connection - wifi and broadband and width (the more POS machines you have, the more width required)

Also review your table design - are tables properly indexed? They are not using lookups/multivalue fields?
Your queries - they should not use domain or user defined functions

see this link for more suggestions to improve performance

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk