UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Solutions For Access Over Vpn Slowdown, Access 2016    
 
   
frethack
post Apr 2 2020, 02:26 PM
Post#1



Posts: 14
Joined: 5-February 20



We use an access database in my group to track projects, which has worked well for us until we started working from home over VPN.

I expected quite a slowdown with the VPN latency, but did not have time to devise a solution before our forced social distancing. We have been dealing with a slower than normal database going on three weeks now, and it may last into the next month.

Has anyone else faced this problem? If so, how did you solve it?

My apologies...found topics covering similar. Please Ignore
This post has been edited by frethack: Apr 2 2020, 02:28 PM
Go to the top of the page
 
theDBguy
post Apr 2 2020, 02:57 PM
Post#2


UA Moderator
Posts: 78,448
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just in case it helps others in the future, consider posting a link here of where you found some help. Good luck!

Did you see this one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 2 2020, 03:23 PM
Post#3


UA Admin
Posts: 37,457
Joined: 20-June 02
From: Newcastle, WA


Please do share your solution (assuming you found something that helps).

As we head deeper into this situation, it's going to be more and more common for people to need to work remotely.

One thing I would look into is a Remote Desktop as opposed to a VPN based solution.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Apr 2 2020, 04:01 PM
Post#4



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


My users have been using AnyDesk to remote into their desktops at work. AnyDesk uses a VPN connection and there's no noticeable lag. As it currently is, linking local Access frontends directly over the internet to remote backends is very brittle, evn without a VPN connection. Users can easily corrupt the backend, so this is why virtually everyone uses a remote desktop. If the connection deteriorates or is lost, it wont affect the remote workstation or the backend on the server. You just reconnect and the screen will be where you left it at.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
AlbertKallal
post Apr 2 2020, 08:18 PM
Post#5


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
as opposed to a VPN based solution.


Quite true. But, to the original poster:
Keep in mind that to "get to" the company network, the VPN is going to be the first step in the solution.

In other words, you in most cases will fire up your VPN, connect to work, and THEN launch remote desktop to hit the server in question.

So, a VPN is most certainly going to be part of a remote desktop solution.

And I have to concur - we are seeing a lot of questions and activity in regards to wanting remote worker solutions. So we thus see a increase in such questions here on UA and in fact a lot of other forums these days.

And, as George points out? I could write out one of my typical and "too long posts here", but in a nutshell?

Remote desktop is the ideal solution here. I should also point that that most servers give a 120 day grace period before you actually have to install (purchase) a client CAL (Client Access License) for remote desktop. So, if you have say server 2012, 2016 or later at work?

Setup remote desktop - you can use it free for 120 days.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
frethack
post Apr 3 2020, 07:18 AM
Post#6



Posts: 14
Joined: 5-February 20



Thank you everyone for the replies. Unfortunately, remote desktop is not a good solution for us because, in its infinite wisdom, the company thought it would be best to issue laptops with docking stations to the essential personnel. There is no PC for us to log into on-site. Others have GoToMyPC and are getting good performance out of the database.

Another working group has found a (quite messy) workaround by keeping local copies of the their backend and autogenerating SQL statements to text files on a work machine for any changes made to the data. The SQL statements are run daily to update the main backend and the everyone re-downloads fresh data after this is done. Im definitely not interested in doing that lol, especially since we tend to have multiple users working with a similar set of records.

I am about a month away from a second database release, so I think Im going with a SQL Express (or equivalent) back end and delay the release time, depending on the blessings of our rather stodgy ITS department. We just need a system to last for two more years until we begin to phase in to an integrated system, which is in the works.

How does SQL Express with an Access front end perform over VPN?
Ill host it with a cloud service if I can, but will it perform if I cant?
Will the front end VBA translate directly to the SQL back end, or will there be quite a bit of adjustment? Its a new adventure for me, but Im rather excited to try.
This post has been edited by frethack: Apr 3 2020, 07:20 AM
Go to the top of the page
 
GroverParkGeorge
post Apr 3 2020, 07:42 AM
Post#7


UA Admin
Posts: 37,457
Joined: 20-June 02
From: Newcastle, WA


Let's step back and take a deep breath.

All of what you need to do is workable. Not easy perhaps, but workable. And, by the way, you're not alone in having to face this challenge, even if it's not necessarily on a schedule you'd choose.

Okay, a couple of keys concepts and how they apply to your questions and concerns.

Access is a unique application in that it contains the three categories of objects needed to create a full relational database application.

  • The Data Layer
  • The Interface Layer
  • The Logic Layer


The data layer is the ACE database engine, i.e. the tables in which you store your data.

The interface layer consists of forms and reports to present and interact with that data.

The logic layer is either VBA, macros, or a combination of both.

When understood in that way, all you need to do is migrate your data from the ACE database engine to the SQL Server database engine.

The interface and logic remain as they are now in the Access accdb.

It's not that simple in practice, of course, but essentially that's what is involved.

In your case, it sounds like SQL Server Express is possibly a viable approach, but it is going to require a server on which to install it. Because you need to connect to it remotely, it would have to be on your organization's premises. For that reason, I'd go with a remote hosting service -- assuming your IT will work with you to do that. If you go that route, you'll have a full version of SQL Server. I use one called WinHost.com and have been happy with their service for years. There are many others. You could also go to Microsoft for their Azure databases. Lots of options are out there.

The main thing to remember is that performance is going to be a factor regardless of how you move forward, but you can minimize that by good preparation. One of my favorite resources is an oldie but goodie . There's a link in my signature to another good one here at UA.

This is not an overnight solution, by the way. Lots of long days (and nights) ahead I'm afraid. You can do it. Good luck.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
frethack
post Apr 3 2020, 07:52 AM
Post#8



Posts: 14
Joined: 5-February 20



Thank you for the reply and encouragement!

I expect migration to a "big boy" database will not be easy or quick, but given the circumstances, it is essential. Im not optimistic on ITS giving their blessing for remote hosting, but there should be server space available for our meager data needs.
Go to the top of the page
 
GroverParkGeorge
post Apr 3 2020, 07:58 AM
Post#9


UA Admin
Posts: 37,457
Joined: 20-June 02
From: Newcastle, WA


Refer to Albert's post. I scrolled past it before. He knows this stuff better than most.

If I understand correctly, you are going to have problems with performance in pretty much any scenario open to you. If you don't have desktops internally onto which you can connect, and have to rely on remotely connecting to an on-premises database (ACE or SQL Server) via a VPN, the options are limited.

I'd argue pretty strongly for a remotely hosted SQL Server or SQL Azure database.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
frethack
post May 29 2020, 03:09 PM
Post#10



Posts: 14
Joined: 5-February 20



Just for an update, I convinced IT to give us five desktops that the heavy database users can log into via GoToMyPC, and now that they have added higher resolution monitors, it is working swimmingly. The rest of the group is still stuck in VPN lag oblivion, but their needs are much less that the five heavy users.

We are petitioning the agency in June to purchase equipment and personnel to switch us to a SQL Server back end. Hopefully they buy it.
Go to the top of the page
 
theDBguy
post May 29 2020, 04:42 PM
Post#11


UA Moderator
Posts: 78,448
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
AlbertKallal
post May 30 2020, 12:57 PM
Post#12


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


The 5 desktops idea seems to work. (excellent).

The tipping point for Terminal Services? Well, if you did not have those 5 desktops.

the beauty of Terminal Services is that the ONE computer can create on the fly as many remote desktops as you require. So, for example, if those 5 users did not have fixed desktops at work (say they use laptops that they take with them?)
Well, then you don't have 5 remote computers to "remote" into, do you?

Note the cost numbers. If you don't have 5 computers, then you don't have to setup and configure and "have" those 5 computers. As you can see the cost numbers after a few computers can in many cases "easy" tip in favor of Terminal Services.

Terminal Services is not "cheap", but after the need arises for a few remote computers, then the cost numbers rather easy start to shift in favor of terminal services. And if you used "session" based, then ANY user that has a VPN, once they connect to Terminal services and use their company (domain) logon? The server will spool up a brand new desktop on the fly - all without the IT folks having to do anything.

So, now you can have 3 or 20 remote users - and no additional hardware setup is required. Last time I looked, I think the basic server 2016 windows license was only about $500-$600. The real cost "thing" will be the remote terminal services "CAL" license. You need 1 CAL for each user. The 10 pack, or 25 pack license can drop the cost per user to below $100 dollars. So, the licensing issue is not super cheap, but after the need arises for just a few remote computers, then the cost justification numbers pan out rather nice.

The main advantage of TS is that when any staff needs to use + run company software, they can do so, and the only setup by IT is ensuring that the user has some kind of VPN connection to work. Once done, then any legal computer user can connect and "remote" use your software on a "session" that the terminal server creates.

So, how gotto mypc, or terminal service works is similar, but with Terminal Services you don't actually need the physical desktop computers t remote into - the terminal server creates desktops on the fly as needed.

Edit:
If I had a choice between learning and adopting SQL server, or getting my hands on terminal services? Hands down Terminal Services wins!
And as noted, even for the vast majority of my Access to SQL server applications? They simply do not work well over a VPN, so even for these cases we near in all cases use Remote Desktop + terminal services.

It is a "lot" of extra work to get access + VPN + SQL server to work and give good performance. It will depend much on how large and complex the application is - but the cost + time you spend optimizing the SQL server side of things in near all cases will far outweigh the cost + time of adopting Terminal Services.

You can spend quite a bit of time and effort optimizing the SQL server side, and you still not get an experience close to that of the response and performance that Terminal Services will give you. In other words, you can spend a lot of time optimizing the SQL server stuff, and you may VERY well STILL wind up using Terminal Services.

Remember, with that VPN, you can't control the quality of the connection. The user might be on wi-fi, or have a lower cost home internet plan, and thus you have to deal with all kinds of bottle necks in terms of speed that you have no control over.

If you can ensure that EVERY remote user is going to have a great VPN and great internet provider that gives good speed, then the VPN to SQL server can work, but it tends to be a losing battle since you don't have control over the possible poor qualify of the internet that remote user may be using, or have.

As a result of the above? Even if you did and do migrate your application back end to SQL server? To get a good user experience, you likely wind up having to use Terminal Services if you really going to provide a quality end user experience.

It is a question do you need just 2-3 users? And then kind of "band aid" some remote desktops for remote use. Or will have have 15 users needing to rock and roll work wise any place any time, and with near any kind of internet connection.

R
Albert
Go to the top of the page
 
tina t
post May 30 2020, 01:26 PM
Post#13



Posts: 6,675
Joined: 11-November 10
From: SoCal, USA


QUOTE
If I had a choice between learning and adopting SQL server, or getting my hands on terminal services? Hands down Terminal Services wins!
And as noted, even for the vast majority of my Access to SQL server applications? They simply do not work well over a VPN, so even for these cases we near in all cases use Remote Desktop + terminal services.

Albert, are there any security issues to consider re using VPN vs Terminal Services?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
AlbertKallal
post May 31 2020, 01:03 AM
Post#14


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Albert, are there any security issues to consider re using VPN vs Terminal Services?


That’s a good question.

No, there is no difference.

And the reason is that near all cases for SQL server, or terminal services?
You need and want and use a VPN

Remember, all the VPN does is “get” you connected to your work place network.

So, once you use the VPN to “connect”?

At this point you really are just like ANY computer on your company’s network.

The best way to think of this?

Imagine that you have a very long network cable running to your work location, and you just plugged it into a wall network plug.

So, say you took your work laptop home.

You connect via VPN.

At this point? Any printer, server, folders or anything that you would find or use on your company network? You can now connect and browse those folders (say some company sales documents as PDF saved in some folder on a server).

And, if you had a SQL back end for your access application? Well at work, you launch access on your desktop, and it would connect to the SQL server running on that same network.

Once again, once you VPN into work, then if you launch Access on your laptop (say now at home), then it will see and find that SQL server running on the network, because YOU are now part of that network like any other user.

So, any work printer or anything else you used or could use while sitting at your computer at work, can be used.

So, the same applies to using Remote desktop.

All versions of windows (for a very long time) have the RDP client (remote desktop Protocol) client installed.

So, you VPN (connect to work). At this point you launch RDP client, and you are free to type in the name of the terminal server.

Because you are part of the work network like any other user? Then you can type in the name of the terminal server.

So, there not really a VPN vs Terminal Services issue here.

In near all cases, you thus use a VPN with Terminal Services.

And if say you not running terminal services, but your Access back end is running on SQL server? Then access on your local computer can connect and find that SQL server, since as noted you really are part of your work network, and you are really just like any other user on that network.

In fact, if you connect via VPN? Well now even your classic Access FE and BE setup will work. If that backend is a simple accDB (standard file) sitting on some shared folder at work? Then Access will launch and it works!

It will see + find that accDB back end on the shared folder at work. As noted, it in most cases will run too slow, and worse, if your connection breaks then Access does not take at all well to this occurring. But even a standard split access database will work over a VPN – but as noted, not well.

So the VPN purpose is:
It allows a secure connection to your company network.

So the VPN simply extends the work place network to YOUR computer that could be anywhere in the world.

So Terminal Services really does not have any special security or anything different than any other server or computer running on your company network.

Nor is this different than just opening up the windows file explore to view and look at some files in some folder on a computer at work.

Once you have this connection, then what you do is not relevant to Access, to SQL server, to terminal servers or anything. It just means ONCE you have that magic VPN connection to your work place?

Then anything and everything you could use and connect to while sitting at your work desktop is now possible where ever you are.

So, just like we would not open up SQL server to the wild internet (so you can connect while at home), we would not open up terminal services to outside connections either.

So a VPN gets you a secure connection to your work network. In near all cases you would use that VPN if you back end is running SQL server on that network, or if you going to use terminal services which is also running and part of your network.

And say if you were traveling in Canada and pulled out your laptop in the Hotel Room to watch Netflix? Well, if you using Wi-Fi, and launched Netflix? It would detect your IP address and detect you are in Canada. As a result, then Netflix would ONLY dish out Canadian content.

However, assuming you have a VPN setup for connecting to work? (Which is down in the USA for example?). Well, you simply would fire up your VPN – connect. Now you are part of the company network, and your IP address will ALSO be as if you are in the USA. Now launch Netflix – it will think you are connecting from the USA (and think you are connecting from your work location). You now can watch + view USA content on Netflix, and they will think you are in the USA.

So, VPN is a technology that “gets” you connected to some system or network in a different location – that location is most often your work network.

So you use (need) a VPN to connect to SQL server or Terminal server.

This allows a secure connection without having to “open up” SQL server or terminal servers to the outside world.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
tina t
post May 31 2020, 03:22 AM
Post#15



Posts: 6,675
Joined: 11-November 10
From: SoCal, USA


thank you, Albert! as you can see from my question, i don't work with servers, etc, and really don't know anything about how they work - only as a user of a PC on a LAN, not as an administrator. but i do have another question, if you will bear with me:

QUOTE
Remember, all the VPN does is “get” you connected to your work place network.

So, once you use the VPN to “connect”?

At this point you really are just like ANY computer on your company’s network.

as most Access developers - or anybody who googles the subject - know, Access works well on a LAN with a split-db setup, backend on server and frontends on desktops/laptops connected to the server. but Access is vulnerable to poor connections and works poorly on a WAN, with an increased risk of backend corruption.

so if i'm understanding right, a VPN can be a poor connection to a server because each "at home" user is using their own ISP and there is no way to control the quality of the service being used.

which brings me to: is using a VPN+TS "safe" for using a backend Access db? i mean, as safe as using the backend db when you're actually at work and directly connected to the LAN? or is it more hazardous, due to the potentially iffy connection over the VPN?

tia, tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post May 31 2020, 09:27 AM
Post#16


UA Moderator
Posts: 78,448
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina. Please pardon me for jumping in... Albert can correct me if I am wrong, but from what I understand in his post earlier, using TS bacically creates a thin client. In essence, the user's computer becomes a dumb terminal. He said the TS server will spool up a desktop environment for each user when they log in, which means the FE and BE stays in the same vicinity, and all the user is sending are key strokes and receives screen images in return. I hope I'm right. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
AlbertKallal
post May 31 2020, 03:26 PM
Post#17


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


DB Guy quite much nailed this.

Edit: but to answer your question? Yes, a split access db without SQL server will work just fine with terminal services.


The quality of the connection does not really matter (unless really really bad - but that only speed - your data does not currpt because you are using TS and the VPN). This is like use goto my pc or any kind of "remote" technology. In most cases if you are using remote desktop and you close your laptop, or cut the wi-fi?

Well, ok, you go find another coffee shop, or when you do find a connection? You can re-connect to remote desktop, and the screen will be EXACLTY as to where you left off when you broke the connection.

As DbGuy noted, remote desktop means that no real data or anything travels down the network connection. The only thing that "leaves" the server is screen painting and your mouse clicks, or keystrokes are sent up to the terminal server.

So, in this setup?
You don't need or even install Access on your laptop. You are using another computer "remote". So, as long as that remote computer is on the same network? Then you can even use a classic split FE/BE with Access. It will work well because the computer you are connecting to is on the SAME company network. So, a classic split access database will work as before. We all know that using access on a LAN, or a local network works quite well. So, there no network issues of corruption, since all you doing is using a "window" into that remote computer.

You not running any software on your laptop - only the RDP client which is a viewing window to a whole remote computer.

In fact, some companies tired of updating and having to maintain computers? Well, they go to some garage sale, or find a bunch of computers in a dumpster. They then buy a REALLY nice server - something that really kicks you know what! 32 gigs of ram. Multi-raid drives that scream and run so fast it will spin your head. I get to remote into several locations where they have one of these "wonder boxes". They are so high performance, and so stunning in performance. You think a nice new SSD drive is fast? Yes, they are stunning in speed. But on a high performance server? Well, disk speed is so beyond stunning, you never want to go back to anything else. They are stunning.

Ever get a ride in a high performance car? Say like a high end Porsche, or a Ferrari? You have no idea?

So, with remote desktop? Well, you not using any resources or anything on your local computer. Everything is running "on" and "from" that remote computer.

So, you find a bunch of old computers - real junk. (or you don't upgrade ANY of the work computers - they are all old out of spec 10+ year old boxes). You don't even have to upgrade the OS - your 25 computers can still be running windows XP.

So, you NEVER install software on each workstation. You never upgrade the hardware. You never have to clean out virues. You never have to add more ram. You just have a el-cheapo dumb box. So, all the users simply launch remote desktop, and they are now using the super duper high speed beyond incurable terminal server. You would NEVER spend that kind of money for ONE workstation.

But, with remote desktop, all you running is "RDP". It is a tiny program that all windows boxes have. In fact, some companies actually build and sell terminal server workstations. They don't even have a disk drive. You buy one, and you done - you run it for the next 15 years. All that the terminal does is when you turn it on is launch RDP. Now you logging into that fantastic out of this world super high performance terminal server system. So the software and performance, and what you have on your laptop does not matter.

In fact, some companies buy a $35 raspberry PI computer. You install RDP on that $35 computer, and you can now use that super duper high end terminal server. And you can connect with a Mac computer, Linux, or even your smartphone. (I in a pinch actually connect remote to windows and even access on my smartphone. (there are RDP clients for phones). So, you don't even need a windows computer to use terminal server. All that the system does is send the drawing of the screen down the network pipe. It really kind of how a web site works. The web site dishes out the content, and any computer with a browser can use that content. Same goes for RDP. There are RDP clients for any computing device I can think of. Including $30 raspberry PI computers. If the workstation has a problem? You just toss it in the garbage and go to the pen supply room, and while grabbing a pens and pads of paper? You grab one of those $30 little boxes, and back at your desk, you plug that tiny box into the network, and then plug your mouse, keyboard and monitor into the $30 computer.


This is so called the "dumb terminal" approach. Before PC workstations came along, you used a dumb terminal. So, in banks and motor vehicles etc. they all used terminals, and did not have a local computer workstation. All processing and software was centralized on ONE computer.

So terminal services works on the above concept. It don't matter if you pull the plug on that dumb terminal (or in this case turn off your laptop, because the window you are viewing and the software you are running is NOT running on your computer.

Think of what occurs when you close your web browser? Closing it does not effect or close down the web site. The web site is a 100% box running that software.

So, some companies tired of maintaining and have to go around to 40 workstations to install some new software? They simply use a terminal server, and they only have to maintain + install software on that main box. The remote desktop clients thus never need software upgrades or anything. So if the terminal server is running say windows 7? Well they can upgrade to windows 10 - all workstations will now have the latest and greatest software - but the workstations are those dumpster computers still running windows XP.

So, from the client side? It don't matter if you run a Mac computer, your laptop or whatever. You only using a "remote window" to another computer that has all the software you are running (in this case terminal server). If 20 users remote into that box? The terminal server will dish out 20 separate desktops for each user (again, kind of like how a web server and site works).

So, some companies not only use remote desktop for stay at home workers, or sales people on the road. They use terminal server for ALL OF their work desktops, and thus never have to upgrade, or even install any software on each work station. You never lose data if your client side computer dies, is turned off, or even destroyed.

R
Albert
Go to the top of the page
 
FrankRuperto
post May 31 2020, 05:47 PM
Post#18



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (Me)
so this is why virtually everyone uses a remote desktop. If the connection deteriorates or is lost, it wont affect the remote workstation or the backend on the server. You just reconnect and the screen will be where you left it at.

Ditto, I second the motion cheers.gif

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
tina t
post May 31 2020, 06:52 PM
Post#19



Posts: 6,675
Joined: 11-November 10
From: SoCal, USA


thank you, Albert and DBguy! i doubt that i get it as thoroughly as an IT person would, but i understand it much much more than i did! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 12:45 AM