Move split db (1 Viewer)

Danny

Registered User.
Local time
Today, 10:36
Joined
Jul 31, 2002
Messages
140
Greetings,

We are using office 365/ ms access db split into BE/FE. The BE is located on the shared folder on the server and each user has a copy of the FE on his/her desktop.
Currently, everyone is working from home using their laptops and accessing the database via remote desktop. Which seems to work very well. I’m tasked to:
  • Setup staff to use VPN from their laptop directly to the shared drive to access the database. In other words, using WAN which I think is not a good idea

  • Eventually, deprecate the shared drive and find a way to move the database to SharePoint or another platform. We also used OneDrive, Google Drive etc.
Any feedback?

TIA

Regards,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:36
Joined
Oct 29, 2018
Messages
21,357
Hi. Rather than SharePoint, try to see if you can migrate your data into SQL Server or Azure.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:36
Joined
Feb 28, 2001
Messages
26,999
Having a VPN over a WAN doesn't work well for technical protocol reasons.

Access touches the BE file via SMB, or Server Message Block, protocol. There is such a thing as SMBv2 and SMBv3, but any of the three versions are the same in one resepect. Because any version of SMB is a TCP sub-set protocol, it uses a sequential message sequence scheme and a dynamically negotiated port number. If the connection breaks, you have pretty much zero chance (verging on less than zero chance) of resuming the sequence. Which unfortunately means that the VPN did you no good if its carrier still dropped like WAN carriers frequently do. The value of a VPN isn't stability; it is security from eavesdropping. If that connection drops, any data pending writeback is now lost forever. If the pending data was part of a larger transaction, you now have a potentially corrupted DB even though a WAN and VPN were in use.

Any platform that will not allow you to use SMB simply CANNOT be used to host a BE file. Using a VPN doesn't mean that the other end of the virtual connection will allow an SMB connection. And Access won't use anything else. If I remember correctly, most cloud-based drives do NOT allow SMB. They prefer that you download or upload the ENTIRE FILE in one operation. But the whole point of SMB is that you can share selected parts of the BE file when needed. Having to upload/download the whole BE file means single-user-only for your DB.

If you can use CITRIX or stay with a remote desktop solution (the latter WILL work over a VPN), then you might still lose a connection, but it would not be the connection from FE to BE, which is the critical connection of the bunch. Can't advise you on Azure. SQL Server is less susceptible (but not immune) to connection losses. The difference is that you can design your DB to minimize net traffic when trying to manipulate a lot of data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:36
Joined
Feb 19, 2002
Messages
42,970
Although you can migrate the tables to SharePoint Lists, this is not recommended. SharePoint is not a real relational database and does not work well with large recordsets. It would be far better to migrate the BE to SQL Server if you don't want to use Jet/ACE. However, unless you want to rewrite the FE, you will need to continue to use Remote Desktop or Citrix to run the application. You CANNOT use ANY type of mapped cloud drive to run an Access application, period. Doc has also explained this at length and why it is dangerous/not possible.

Using VPN is also a poor option as others have mentioned when your BE is Jet/ACE. It will be better if the BE is SQL Server but it is still not the best solution. RD or Citrix is what you need.

Many companies are hot to get rid of their local servers but this will always cause a problem for Access users unless you NEVER use any automation for Word/Excel/Outlook. Access needs to run on a LAN. if the LAN is hosted by some third party service bureau, that's fine. They would be able to work out how to share Word and Excel documents but you almost certainly would need to convert the BE to SQL Server which comes with other problems. Most people are flabbergasted when they simply replace their linked Jet/ACE tables with linked SQL Server tables and their app slows to a crawl. To make effective use of SQL Server (or other RDBMS), you need to use good client/server techniques when building your Access FE and that starts with NEVER binding any form or report to a table. All forms/reports should be bound to queries that include selection criteria that severely limits the number of rows returned from the server.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:36
Joined
Feb 19, 2013
Messages
16,553
I would hazard a guess that 75% of performance issues over VPN is simply the speed of the connection. Moving to sql server on it's own will not change that. The other 25% is down to the way the FE pulls data across the network as alluded to by Pat and how well the BE is designed

you may already be aware of this stuff, but if not...

See this link about working from home with Access

this expands on improving FE performance

and this about indexing
 

Danny

Registered User.
Local time
Today, 10:36
Joined
Jul 31, 2002
Messages
140
Thank you everyone for your inputs. At this moment, based on the feedback I got from you, and from what I read online on this topic, I was able to convince the management to keep using the remote desktop solution.



I appreciate all your help!!

Regards,
 

Users who are viewing this thread

Top Bottom