We help IT Professionals succeed at work.
Troubleshooting Question

Can a local Microsoft Access frontend safely connect to a remote Microsoft Access backend over VPN?

billpars
billpars asked
on
79 Views
Last Modified: 2020-11-02
A client’s employees have been working from home since March.  They use several custom Microsoft Access applications on a daily basis.  Those applications are split-architecture (local copy of frontend MDE/shared copy of backend MDB).

Currently, a user does the following:

1.   From their home PC, they connect to the company VDI platform.

2.   From the VDI platform, they take remote control of their onsite PC.

3.   By remote control, they log into frontends that reside on the onsite PC.

Frontends on the onsite PC connect, via LAN, to backends (in a fileshare) on a dedicated, onsite PC.  This seems to work well.  In this scheme, they (essentially) are working onsite, but using really, really long monitor/keyboard/mouse cords that stretch all the way to their home.

Some other employees have only company laptops, which they use from home.  They VPN into the company, but have no onsite PC to remotely control.

Because they have access to the onsite fileshare, via VPN, it is possible for them to log into copies of frontends on their laptops at home, which would connect, via VPN rather than LAN, to backends in the onsite fileshare.

I am under the impression, however, that connecting to a shared Microsoft Access backend via anything but LAN is inherently unstable, and this proposed laptop scheme could lead to crashed/corrupted backends.

What experience, insights, or links can experts here share to address this concern, one way or the other?
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Yes it can be done, would I recommend it, no.  It will be inherently slow and significantly increase the possibility of data corruption.
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
If you are using windows 10 for your computers then you only need to ensure doing the following:

Studio Managements
(1) Enable TCP/IP
(2) Use a static port
(3) Restart the server after changes
(4) Login as admin in your server and add all the users to have the right on your database ( read/write) for sure after granting them access

Window file wall
(1) Just open the file

I did try it myself it works very well


See the link here for the video and notes

http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/


Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 He is using an Access JET back end (.mdb) though, not SQL Server.

Jim.
CERTIFIED EXPERT

Commented:
Its a non starter , JET back end only works very well on local area network not VPN , kindly avoid this, instead migrate your database to Ms SQL server and follow what I explained above you will be safe.

Regards

Chris

Author

Commented:
Jim,

You say:

<<The other option is to convert the BE's to SQL server.   But that's probably beyond what you want to do....>>

I think the ultimate solution actually is migrating the backends to SQL Server (especially given the Access Doomsday Scenario and Microsoft's inadequate response to it), but I am concerned about:

<<...you are still left with performance issues more than likely for the remote VPN users.>>

Can you speak a bit more to this (and to performance issues for LAN users)?

Thanks,
Bill

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Bill,

<<Can you speak a bit more to this (and to performance issues for LAN users)? >>

 The majority of Access apps out there are not written for true client / server.  You need a different mindset in developing an app like that in that you want to push as little data over the wire as possible.   For example, you'd never bind a form directly to a table and leave it like that.

 You'd also push as many operations server side as possible using things like views, pass-through queries, stored procedures, and triggers.

 For most Access apps, it would be a total re-write.

Jim.   

Author

Commented:
Jim,

Thanks for the critical insight.  For comparison, can you give me a rough benchmark of how a bound form would perform against a SQL Server backend vs. an Access backend?

Bill
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@billpars...let's put it that way..a bound form requires that all records are available..and that means linked table,if this is the case you can kiss performance goodbye, it will be similar to establishing a VPN to Access BE and treating as local...the same amount of data would be required to pass from the same "channel"
So you have to think more on the unbound concept.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Bill,

<<Thanks for the critical insight.  For comparison, can you give me a rough benchmark of how a bound form would perform against a SQL Server backend vs. an Access backend? >>

  Typically it's quite a bit faster as SQL does a more efficient job of working with data.    With an Access backend, the database engine is on the client side and all the data is being processed there, so a lot of data goes over the wire that doesn't when SQL server is involved.  

  For example, if you ask for a single record based on a PK, with an Access BE, the Btree index pages would need to be pulled over (assuming they were not already in the cache), then the record fetched.   With SQL, it would just hand you back the record.

  Beyond that it's hard to say anything because so many factors that are involved.  There are a few that see a slow down, but that's usually a rare case.

  My suggestion would be to try it.   It doesn't take all that long to setup and test, especially if you already have SQL installed and available.

  You use the SSMA (SQL Server Migration Assistant) to create the SQL DB, make a copy of the Access FE DB, delete the existing tables links, then create links to the new ones in SQL.  Then give it a go.

Jim.




CERTIFIED EXPERT

Commented:
Ok
If you decide to migrate to MS Sql server, then you will surely need to create the following:
(1) ODBC string to link your FE to the new BE ( SQL server)
(2) Create link manager which will be able to link your tables to all your users automatically at start up

We call this code below as Less file DSN, example :


Private Sub Form_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "ODBC;DRIVER=SQL Server; " & _
"SERVER=NECTORXXXX\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
Exit Sub
End If
End Sub

Author

Commented:
John and Jim,

Thanks for your insights regarding my question:

<<For comparison, can you give me a rough benchmark of how a bound form would perform against a SQL Server backend vs. an Access backend?>>

Unless I am missing something (and I often do!) they appear to conflict:

<<you can kiss performance goodbye, it will be similar to establishing a VPN to Access BE>>

vs.

<<Typically it's quite a bit faster as SQL does a more efficient job of working with data.>>

If so, what critical insight (if any) am I missing to bring those replies into accord?

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
LAN vs WAN.

In either case, moving to SQL server will usually give you a speed increase over JET.

But whether a JET backend or SQL Server, moving from a LAN to WAN you would see a significant decrease in performance.

Jim.

Author

Commented:
Jim,

Got it!  (I think...)

So -- all things being equal -- moving from an Access backend to a SQL Server backend should not decrease performance, even for bound forms.

And moving from LAN to WAN would decrease performance for both Access and SQL Server backends (although one would never use an Access backend over WAN, given the inherent instability and risk of data corruption), primarily because of form binding.

Is that right?

Bill
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
In my experience if you just move tables to SQL you will see a performance decrease.
In this case you are just replacing the File System with SQL and you are feeding everything via a 3rd party (ODBC)
Treating SQL as a Database engine will bring a definite performance boost but this is not straightforward.
For example suppose you have a complex query and you set it up on SQL --properly  - passthrough-- should give you a nice performance boost...but if you query the same tables via the "usual" linked tables...you should not be happy with the execution time.
Of course a number of things will count either for or against but at least with all my tests the results were pretty much the same.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
@bill,

 Yes, you got it.

Jim.

Author

Commented:
John,

You say:

<<i think your best option is to just copy the FE to the VDI and let the users work directly. >>

I am not a network guy, and have no experience with VDI, so please bear with me.  I think the platform is VMware Horizon.  If I understand your suggestion, it is to expose the custom Access frontends (somehow) to remote users through this web portal.

The (onsite) VDI frontends then would connect to (onsite) Access backends via LAN, eliminating the instability/data-corruption risks inherent to (offsite) laptop frontends connecting to those (onsite) backends via VPN.

Have I got that right?

Bill

Author

Commented:
My VDI question to John is for anyone.

Are there any experts at EE with actual experience exposing Access frontends through VDI?

John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
From what i am reading vmware Horizon is just another implementation of having a Virtual machine connected remote.
From what i understand from your writing the users already have an account and a VDI assign to them individually (maybe i am wrong so please enlight on this) and from that VDI you rdp to the local machines
If i got it right the VDI behaves as a proper windows installation (full Windows experience unless we are talking about Linux or something else) so why "inject" more complexity than needed...just copy the FE to the VDI(s) and let the users work from them.
Set a BE that is on the same network as the VDI to make use of the local network (LAN) and that should be all
Now Vmware Horizon has another "option" for delivering single apps like RemoteApp so the principle should be just the same...just publish Access as an App via Vmware Horizon and that should be all.
A topology and maybe a mockup of what you have right now should be most helpful.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I haven't worked with VDI directly, but underneath that, it's still RDP. It's still only KVM (Keyboard, Video, and Mouse) that is running across the connection.

VDI is nothing more than giving each user their own VM rather than a session on a RDP server.

 So it still boils down to the same thing; MSACCESS.EXE is executing on a server is that is on the LAN, and only KVM is going across the wire to the remote user.

 In contrast, if the user was simply using a VPN, MSACCESS.EXE would be executing on their remote machine and data would be going across the wire to/from that machine.

Jim.

Author

Commented:
John and Jim,

Thanks for the clarification.

If I understand correctly, IT would publish a "Win 10 Desktop for Custom App Users".  This VDI "application" would be (essentially) nothing but a snapshot image, taken from the onsite computer of a current custom-app user.  It would include an MSACCESS.EXE installation, of course, along with a single copy of each local frontend (MDEs and ACCDEs), a single copy of each local custom library those frontends reference (also MDEs and ACCDEs), and a single copy of each logon shortcut to those frontends.  When remote users launched it from home, it would create their own, isolated instance of Windows 10 (with that image) on the VDI server.

Is that correct?

If so, then I need to ask about one of the frontends which (unlike all the others for which users have a local copy) actually is *shared*.  (I know this is traife for Access, but I am stuck with a legacy inheritance.)  That anomalous frontend provides read-only links to backend tables, and allows users to create/save ad-hoc queries when predefined custom ones fail to anticipate weird data needs.  Sharing this common frontend allows users to see and use each other's ad hoc queries (and to make/modify copies of them).

Just to be absolutely clear, the published VDI "application" above would include a logon shortcut to that shared frontend (the single copy of which sits alongside backends on the network file-share computer), and users still could create/modify and *save* query definition objects in production, which would become visible to other users -- through their own VDI instances -- in real time?

Again, correct?

Not having any VDI experience (or the software/licensing to noodle around with it), I rely on insights from experts like you to know if this even is a viable solution to pursue, so forgive me if the above seems self-evident to you (or like I am beating a dead horse).

Thanks,
Bill
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<When remote users launched it from home, it would create their own, isolated instance of Windows 10 (with that image) on the VDI server.

Is that correct? >>

Yes.

<<which would become visible to other users -- through their own VDI instances -- in real time?

Again, correct? >>

 Yes.   The shortcut for the shared FE would need to point to a network share that they have access to.

<<Not having any VDI experience (or the software/licensing to noodle around with it), I rely on insights from experts like you to know if this even is a viable solution to pursue, so forgive me if the above seems self-evident to you (or like I am beating a dead horse). >>

 As I mentioned earlier, I haven't worked with VDI directly myself either, but I'm aware of what it does/how it works.    It's part of RDS/Citrix.    But unlike normally where you'd just get a session on a server, a VM is created based on an image and is assigned per user, so each user ends up with a "PC to themselves" (no one else shares the same VM while they are using it).

 VM's can be persistent - they see exactly what they had before from a prior session, including apps, data, etc.  or they can be assigned a new VM off an image each time, which give them a clean slate each time they connect.

Jim.

Author

Commented:
Jim and John,

I would like to split credit between you, but I am not sure how.

Can either of you advise?

Bill
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Mark both as solution and any other comments that were helpful as "This is helpful"

Jim.