How does MS Access work with BE on a LAN that sits in the cloud?

[marlan]

Hi,

I have a system running in a large organization, 1-3 users, FEs on each work station, BE on a LAN network drive. Using Acc2002 mdb files in Office 2010-13 environment.
The user occasionally gets a few unexpected error messages.

- Some are about the BE file format, indicating file corruption, that I solve by compact & repairing the BE.

Others of unexpected data update error messages:

- This is the Hebrew version of the message indicating records can't be added to a table, do to Index or Data Type constraints. There were no constraint violations (Please note the two 0s in the top row). It occurs during a data manipulation process, containing a few UPDATE and INSERT queries. Please trust - the data obeyed all constraints.
View attachment 87964
- The 'Application defined or object defined error' while VBA runtime.
- 'Data mismatch' in key error.
- Write Conflict.

Again: these occasional errors occur in a closed process, working fine for years, there's nothing in the data or logic that should fire these errors.

I suspect it is the organization's LAN: If it was moved to the cloud, wold that cause errors of this sort?

Thanks in advance!

 

[isladogs]

Simple answer to your question. BADLY.
Access FEs should never be linked to a BE where the connection is anything other than rock solid. So that excludes WiFi connections on a local network and any cloud storage such as OneDrive, Dropbox etc.
If the file is being written to when a connection drops, there is a significant risk of data corruption....as you have already experienced on your LAN.
Moving to the cloud will not make this work better.

 

[Isaac]

I agree with @isladogs
Instead of paying for a cloud service on the back end, consider paying for Azure in the cloud as a back end.

 

[theDBguy]

Hi. I also agree with @isladogs. LAN stands for "Local." If they're using the "cloud," then it's not local anymore, and it's bad news.

 

[The_Doc_Man]

Depending on the nature of the cloud device, it is POSSIBLE (not guaranteed) that it won't even support the protocols required for Access to work "normally" over a network. Cloud storage that is like a "drop-box" only moves whole files, but Access absolutely requires SMB protocol support for its BE files. Drop-box styles of cloud storage use FTP transfers but not SMB. The difference is that FTP stands for FILE TRANSFER protocol (implying the whole file). SMB is SERVER MESSAGE BLOCK where you can send / receive a PART of a file.

Access over a cloud is a disaster waiting to happen.

 

[CJ_London]

if moving to the cloud is being seriously considered, consider using terminal server or citrix at least for access. I have a client who uses terminal server for everything rather than a LAN - so all their machines are effectively what used to be called 'dumb terminals'. Also means they can work from anywhere, can work wirelessly and are not limited to only using windows machines.

 

[marlan]

Thank's all for your replies,

Depending on the nature of the cloud device, it is POSSIBLE (not guaranteed) that it won't even support the protocols required for Access to work "normally" over a network. Cloud storage that is like a "drop-box" only moves whole files, but Access absolutely requires SMB protocol support for its BE files. Drop-box styles of cloud storage use FTP transfers but not SMB. The difference is that FTP stands for FILE TRANSFER protocol (implying the whole file). SMB is SERVER MESSAGE BLOCK where you can send / receive a PART of a file.

Access over a cloud is a disaster waiting to happen.

It is the main network directory the organization works with, implemented as directory P:\ on all work stations. It usually works, so i don't think it's ftp protocol. I don't know if it SMB, but multi users don't cause any special issues, so I think the protocol is fine.

Assuming that, is their any Access maintenance I could do? Would moving the FE to 365 make any difference?

Is this time to migrate th BE to SQL server?
TIA

 

[Pat Hartman]

I'm going to go with Colin's one word answer - BADLY. I can tell you this from experience. One of my clients lost the local server that hosted our SQL Server and so they just moved the BE to a cloud server until they got to build the new one. It took them a week and using the app was pretty much like watching paint dry. It was awful.

If you need remote access to your app, Remote Desktop or Citrix are excellent options. If you run them in house, you can mix both local LAN users and remote users. If you run them "in the cloud", then EVERYONE runs in the cloud. There are no local users. Some people try using VPN. That is also extremely slow with Access when the BE is Jet/ACE. It is somewhat better when the BE is SQL Server but still bad.