Access over WAN (same domain) so slow... How to replace Access...

[RyanP]

Have had users using a few simple access applications over WAN for years (same domain / intranet).


Load time was noticeably more for the remote users but not too bad.(up from 2 seconds to 20 seconds) Record locking etc is all managed well... have not once had any backend DB corruption (fingers still crossed)

Just tried a more complex application in this same network scenario... not good at all.
A 4 second app load time with average 1 to 4 seconds per transaction in the app is normal in the LAN scenario.
However for the remote end users (different province); the load time is almost 2 minutes with transactions taking 15 to 45 seconds. essentially a 4000% increase... crikey! I'm gonna call that un-useable.

ACCDE front end (did not notice much of a performance difference with the FE local or from the app server). MDB as the back end.

The apps are stellar on the LAN but horrible on the WAN. The simplicity of Access is what has made me keep it as my crutch for years.
Is there anything I can do to remedy this in Access?

Or do I finally need to ditch this crutch (Access) and learn to build Web based UI's with an SQL server for the BE.

The following is off topic from the main topic/title but i'll throw this out there?

Only a bit familiar with SQL Server (Express) and a total newb when it comes to web...
If ditching Access; still not sure which web framework / programming language to move to... leaning towards Django / Python / SQL but not committed.
Any apps out there to convert Access forms & VBA Modules to web pages / stored SQL procedures? That would save a ridiculous amount of time!

[ItsMe]

Today, the only good reason I can think of for writing a web app is because a client device does not have the capability of using the Access Run Time. So, if you need to serve multiple clients and one of those clients needs to use a Web Browser, then develop a web app.

Maybe the solution is to upgrade your BE. If hosting SQL server on premises is not an option, there is Azure. They offer various cloud solutions for SQL server as well as an Azure hosted SQL database. Another approach might be to stay with Access and change the BE from mdb to accdb.

Most likely, a different approach in how you query and connect to the BE is the solution. If you move to Web Apps, you will have the same issues to tackle regarding the persistence layer, domain layer, UI layer, etc. You could probably improve performance by selecting which data is queried and how it is queried, e.g. dynaset vs. snapshot.

[Rawb]

Today, the only good reason I can think of for writing a web app is because a client device does not have the capability of using the Access Run Time. So, if you need to serve multiple clients and one of those clients needs to use a Web Browser, then develop a web app....

I would actually argue the opposite. The only reason for using Access is its speed of development. There are a huge number of limitations in what you can do with Access, VBA, and Macros.

If you are dealing with a small-to-mid-sized app in a Windows-based LAN environment then Access can do the job. But if you fall outside those quidelines, Access very quickly becomes unfeasible as a solution. For many businesses that need to work with satellite locations or end-users, Access is absolutely NOT what I would recommend.

That said, it might be looking at Access [Web] Apps. Assuming they perform well over a WAN (I don't know, I've never used them), that may be the easiest solution. You might even be able to just convert your existing database Forms and Reports without having to redo everything from scratch.

[ssanfu]

You really should read this article by Albert D. Kallal.

Using a wan with ms-access? How fast, how far?
http://www.kallal.ca/Wan/Wans.html

I would suggest/try using Access as the FE and SQL Express as the BE (client/server setup)



He also has other good info: http://www.kallal.ca

[orange]

One of the members on another forum, who is very experienced, has this in his signature

Using Access BE over WAN is  like running to your city library, ripping out pages from a book,
  running back home and scribble notes on it then run back and glue the  pages back into the
 book in the library, all during a rush hour full of  drunk drivers in middle of a horrible 
thunderstorm, and hoping nobody  else has ripped out the pages you wanted.

[ssanfu]

LOL... Now THAT is funny!!

[orange]

How's your weather these days Steve?

I always look at the signatures- many are informative.

One that always gets my attention is

Yes I love all the little animals - right next to the mashed potatoes.

and there is one

Silence - is that brief period when both sides are busy reloading their weapons

[ssanfu]

@orange,
It's been a little overcast, but overall, not bad. Still have hundreds of fires in the interior. How about your weather?


Here is another one

I'm a second hand vegetarian - cows eat grass, I eat cows.

[orange]

@steve

That's a good one!!

Yes we're hearing of all the fires - it is quite devastating. Many more than usual.

We are having 23-28 C days with a splash of rain now and then.

[ItsMe]

...That said, it might be looking at Access [Web] Apps. Assuming they perform well over a WAN...

When I think web app, I am thinking something along the lines of .NET, Ruby on Rails, Python, PHP, etc. I am not sure what advantages or disadvantages using SharePoint technology has. It seems using Access as a Web App development tool is geared for enterprises that have hybrid data centers, SharePoint, and Power Users. The Power Users are comfortable building team sites and Access web apps to interact with SharePoint.

I think I did mention that if there is the need for a single User/Client to have access to the data via a Web Browser, to use something other than Access. That is my opinion on the matter. It can be feasible to use Access as an FE to SQL Server over a WAN. This is why I mentioned on prem SQL Server and Azure SQL Database. If the OP is already invested in Access over a WAN, there may be ways to put a band-aid on things by looking at how the data is queried.

Rawb, what is your preference for building web apps? Much like the OP, I am looking into different technologies. Looking into the future, I plan on using C# and .NET. I have looked at using MVC and its Code First approach. Not sure if I am sold on that. ADO.NET and the Entity Framework seems more attractive to me.

[Rawb]

Most of what I work with is a homegrown system that uses the typical LAMP setup. All of our stuff is custom generated PHP that we've hooked into the Smarty templating system.

We're currently looking into several technologies that would allow us to rebuild with more features and better functionality without forcing us to recode everything from scratch by hand. Currently my boss is the one doing most of the looking around but just yesterday we were talking about Angular as a possible framework. I believe Django was mentioned in the past as well, but I'm not familiar enough with either to know how well they stack up against each other - or if they even do.

[CJ_London]

there is another option which does not involve any significant changes to your access application - use terminal server or the citrix variant.

As humorously described earlier, Access is not good across networks because all the calculations are client side rather than server side so there is a high volume of network traffic. Front ends can usually be designed better to work along the same lines as a web page - only bring data through that is required - for example, don't design forms with a simple table as it's recordsource, apply criteria to only bring through records actually required (and leave it empty (i.e. SELECT * FROM myTable WHERE 1=0) until some criteria have been selected) but that still doesn't provide server side processing.

With terminal server/citrix, both front end and back end are on the server (still with a front end for each user) so you effectively get server side processing (same sort of performance as if the backend was on the users machine) - and the only network traffic created is changes to the screen - not even whole screen refreshes, just the bits that have changed, So your remote users should experience much the same service as those connected to the LAN - just depends on the performance of the remote users internet access.

Yes this has a cost, but so does rewriting your application as a web app etc

[ItsMe]

I have been struggling with this topic for the last seven to eight months. Although I have never been a web app developer, I have been aware of HTML and CSS for many years. I have used Dreamweaver (kinda) and even Front Page. Because of the trends and the huge marketing campaigns for "The Cloud", I knew I needed to anticipate the viability of "Desktop" apps over the next several years. I feel developers will need to offer solutions for small businesses to get their desktop apps to work as mobile apps.

I am going with Visual Studio as an IDE. Also, I believe Microsoft Azure is a one stop shop for cloud services. I do not see anything that is offered by AWS that cannot be supported by Azure. Now, Visual Studio offers cross platform development and there are SDK's for all sorts of stuff. Using Visual Studio is not simple. But if you are focused on a specific technology, using Visual Studio to develop apps in Azure is a snap. You can maintain downloads of packages via Visual Studio's NuGet package manager. Visual Studio and Microsoft Azure seem to offer small businesses the benefits of enterprise technologies as well as technologies that have been used historically.

Moving forward, I am going to focus on bringing PaaS to small businesses. It seems my Access apps will work just fine with Access 2016 and Windows 10. Now, I just need to ensure I can quickly adjust these apps to connect to SQL databases hosted in the cloud. With that, any web apps I develop can connect to the same persistence layer.

[CJ_London]

Using Visual Studio is not simple

Nor is Access when you first start

I'm trending the same way - visual studio/azure - but living out in the sticks I have loads of clients who do not want to put their data on the web either because of a trust issue or because their internet access is c**p and performance really suffers. So I think Access still has life in it yet.

[RyanP]

Have so far reduced the initial time to load the app (for the remote clients) from 90 seconds to about 20. (still under 4 seconds on the LAN)
1) I unbinded all the forms (by default), and now only bind them when I load them. This had a huge difference on the initial time to load the app and only a marginal increase in the time to load the individual forms / records.
2) Not sure if it was a good idea or not; but also unbound the subforms & removed the master/child relationships / replaced them with queries based on the main form they are within...
(my thinking was that I would not have the luxury of master/child subform relationships in any web based UI?) Regardless, didn't seem to have much effect either way

3) Had some continuous forms of static data that were set to dynaset. Changed them all to snapshot as they should have been in the first place. Mybad


So far so good with my crutch; "Access".