Solved Rules to be implemented with Ms Access against SQL Server Cloud (1 Viewer)

[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

 

[Pat Hartman]

I'm not sure where you heard that this setup was a good idea. It wasn't from us. Access works extremely well over a LAN when the PC is hard-wired. Using WIFI makes the connection between the FE and BE more fragile and slower but will usually work OK when the BE is SQL Server because SQL Server is more robust and better able to reconnect when there is a "blip" in the network.

The best way to use Access over a WAN is to use Citrix or RD to host the application and run it on your LAN. The remote users send keystrokes and receive images in this scenario. Actual data never crosses the internet so the response time is very fast, frequently faster than a local LAN user because with Citrix or RD, the Access FE and BE are on the same server and Access runs in the memory space of that same server server so nothing has to cross the LAN as it does with a typical setup.

People have reported some levels of success when the BE is Azure. I do not know how they have it configured but the times I tried it with a third party provider, my response time was abysmal and not usable from the client's perspective.

Access, when using ODBC to connect to any RDBMS is "chatty". Numerous messages are exchanged each time any data retrieval action is performed. LAN speeds are fast enough to make this not an issue. WAN's are extremely slow relative to a LAN, even the fastest WAN connections.

If you have the option to switch the BE to Azure, that might improve the performance. The other option is a whole lot of work and that is to convert to unbound forms. I can't even guarantee that that will solve the slowness.

Citrix and RD are your real best options and they don't require any changes to the application.