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

marlan

Registered User.
Local time
Today, 11:36
Joined
Jan 19, 2010
Messages
409
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

MVP / VIP
Local time
Today, 09:36
Joined
Jan 14, 2017
Messages
18,186
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

Lifelong Learner
Local time
Today, 02:36
Joined
Mar 14, 2017
Messages
8,738
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

I’m here to help
Staff member
Local time
Today, 02:36
Joined
Oct 29, 2018
Messages
21,357
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

Immoderate Moderator
Staff member
Local time
Today, 04:36
Joined
Feb 28, 2001
Messages
26,996
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

Super Moderator
Staff member
Local time
Today, 09:36
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 11:36
Joined
Jan 19, 2010
Messages
409
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

Super Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom