This recordset is not updatable? (1 Viewer)

Can someone please tell me what this message that I get in the status bar means? I was not getting it and now I am. I'm dealing with a name search form that does a requery to populate a list box on the form. The database is not read only.


It does not appear to affect the query results.


Thanks.

A recordset may not be updatable for several reasons. It is well explained here
http://allenbrowne.com/ser-61.html

The back-end is on a cloud server. That's where I get the message. When I link to back end on local NAS, instead of "This recordset ...', I get 'Datasheet view', which is what I would expect. Also, when I see the 'Access' symbol to the left of the file name on the cloud folder, there's a little blue symbol on the Access symbol. Can't make it out exactly, but it looks like a blue circle with a vertical lightning bolt. Any ideas?

OK, I think it means someone is using the database. I'm not going to worry about it. Case closed.

Doesn't it mean data is being synced?

You didn't say what you meant by a cloud server.
If its Azure or similar then that is fine.
If you meant something like OneDrive or Dropbox then that's definitely NOT fine

Here's what it looks like.


The server/service is something called 'Egnyte'. It's enterprise storage that you rent and where you can do file sharing.

Yup. Its syncing.
I know nothing about Egnyte but a quick look at their webpage indicates to me that its a professional version of Dropbox - fine for uploading / downloading / sharing files BUT I think not fine for use as a BE database server.

I think others with a better knowledge of cloud servers may be better placed to advise. I will PM two such experts and ask them to contribute

I've taken a quick look, not an organisation I am familiar with. Agree their offering looks like dropbox or perhaps sharepoint in which case it would not be suitable for Access.

I presume the OP is trialling this for free at the moment. I would be very wary of using it to store the back end. The blurb says users can share files - sharing is not the same as multiuser access. As soon as a user wants to edit, the file is copied to another location (perhaps in memory) for editing and then copied back when saved. That is death to an access database. It is a file, yes, but the locking is not at file level (such as Excel) but at record level and maintains its own internal locking facility.

Thanks. This is a back-end database and it is only used to retrieve data by a select query form by a few users. The back-end will be updated by only one person quarterly. Do you still believe this is not a suitable location for the database?

In my view no.
No matter how infrequently it is used, if the online connection is broken during use, the backend file will almost certainly get corrupted.

By all means distribute the file online if you need to, but it should be downloaded to the users hard drive when it is used

I would go a little further. Once you distribute a database into the wild where you don't have control over whether or not it gets updated, you have to protect it somehow.

1. Add a table with a data expiration date. If your intention is to update the data every 6 months, then pick a date 6 months in the future and log that.
2. As part of the opening procedure, check the data expiration date against the current date. If the current date is > the expiration date, the app should display a warning and close down.
3. I agree with Colin, I still wouldn't use a cloud service to "share" the app. I would just use the cloud as a way to host the current version where they can download it when the old version expires.

I'm going to toss in a definition or two. The word "sharing" - as in "file sharing" - has multiple meanings depending on the intimacy of sharing.

To put a file where it can be downloaded to/by others IS definitely file sharing, but of a very limited nature. The sharing in many such sites is to use the web page's ability to pick up a file via FTP or SFTP, a File Transfer Protocol. This is a WHOLE FILE transfer protocol. I.e. you pick up whole files at a time. Drop boxes use this ability of browsers (to use FTP or FTPS mode instead of HTTP or HTTPS mode) to claim that they are a good way to share files. And they are not technically wrong.

But there is another level of file sharing and that is the intrinsic Windows File Sharing. This uses the SMB (Server Message Block) or secured variants thereof. When you use SMB, you are able to share components / contents of a file without sharing the entire file in a single operation. I.e. piecemeal rather than wholesale.

The reason that FTP-based transfers work OK even for large files is that FTP includes the ability to request a re-transfer of a block in mid-stream without voiding the entire process. The details of the protocol include the ability to take several blocks at once and reassemble them in the correct order on the receiving end of the transfer. However, if you lose a block with SMB, it doesn't do so well and the transmission sequence becomes garbled. SMB does not recover as well from lost blocks mid-stream.

Access, when it is dealing with a "native" back-end file, depends heavily on SMB. If the back-end is some sort of smart "active" app like SQL Server, My SQL, ORACLE, or one of the other ODBC-capable products, there is yet ANOTHER protocol that is common to all ODBC products. Both SMB and ODBC require robust networks. Drop-box systems don't like - but CAN survive - flaky networks.

The issue of using SMB over the cloud to get to a back end file is that the cloud more often is less robust than a hard-wired Ethernet or Token Ring connection. And every time you encounter one of those connection breaks, you risk file corruption.

Thank you all for your comments and suggestions. I am going to add more information because I'm hoping to hear different or at least more encouraging comments regarding using this method of sharing the back-end. The front-end of this database will be distributed to end-users to their local desktops. When the end-user opens this front-end file, Access opens but it immediately defaults to a form view. The Navigation Pane is cleared, as well as the full menus and shortcut menus. All they will see is the form filling their window. So, unless they are savvy enough to get to objects, there is no way that they can update the database. In addition, as I mentioned earlier, the back-end will be updated locally and uploaded once a quarter. Isn't the risk of corruption, then, dependent on what Access does internally to its tables as queries are executed against its tables. Does Access perform these internal updates? If not, the syncing process should not affect any changes to the back-end since nothing has been updated, and no corruption should occur. Am I right?
Thanks.

If you want to go against the advice of everyone here, then go ahead. My own view is why take the chance? In a normal multi user environment it would be a definite no-no. In a read only environment it might be OK - just be aware that just opening the backend and closing it, changes it - unlike excel/word etc. Locks are set, pointers created. If you don't believe me. take a note of the backend last updated value. Open the file then close it again - the value will have changed.

You would be much safer simply emailing a copy of the backend for each user to store locally.

My view also hasn't changed. Even though the data should not be editable according to your description, to view the data the FE still has to be connected to the online BE. At some point that connection will go down whilst it is in use and corruption may still occur. Less likely perhaps but still possible.
If you decide to go ahead, you may 'get away with it' for a while but you also may not immediately realise that data has been corrupted. Of course, I'm sure you will setup a rigorous backup procedure.

As the data is intended to be read only, then I agree that it should just be distributed to end users and only allowed to run from a fixed location on their local machine e.g. From the same folder as the FE

Thanks again to everyone. I'll recommend that the back-end be distributed to desktops. Only problem is, I see another app down the road where distributing b-e is not feasible.

Only problem is, I see another app down the road where distributing b-e is not feasible.

Click to expand...

for multi user applications where users are not on the same network - if you want to use access investigate using a terminal server.

Slightly less flexible consider using a sql azure backend with access front end. Less flexible because for security reasons you have to specify the IP addresses of the users who require access - fine if known, but if they are sitting in a café or hotel somewhere or on the train, it is not going to work.

You might also consider using sql server express as a back end, but suspect it can not be installed on your cloud server.

If you are going to use access within its design parameters you should not have a problem, but if your requirement is outside those parameters, you need to use something else.