Access corrupting Excel spreadsheets. (1 Viewer)

[wackywoo105]

I've been having issues with a couple of Excel documents that I access with Access. For some documents this manifests as the vba code for the excel document (not the code in access) completely vanishing. I wasn't sure what was causing this, but I'm now fairly sure it's something to do with Access. Another issue is the documents becoming corrupt after opening them in Access and then refusing to save. This doesn't happen every time, which is why it's taken me a while to link it to Access.

This has been an issue since late last year. Is it a known issue and are there any known fixes?

This document below was opened in Access and generated an error. Afterwards it has corrupted and refuses to save. Earlier today I did the same thing and it worked fine, so I have no idea why it suddenly decided to do this. This document has been used for several years without issue and only recently started exhibiting this behaviour. I have created a new document and copies over the data (which it look like I will have to do again) and this still keeps happening.

 

[arnelgp]

must have to do with "synchronization" with your Google drive.

 

[The_Doc_Man]

Arnelgp raises a good point. Are you storing this on a cloud drive?

 

[wackywoo105]

That's a good point. I've stored this document on google drive for years, as I need access from different locations. Is it likely a problem with Google drive, or could something have changed with office that is causing it to not like Goole drive anymore?

 

[The_Doc_Man]

If you know the date on which you first got this error, go to Start (lower left window icon) >> Settings >> Security and Updates and try to find the update history to see if you had an update the day before this started happening. Windows updates are notorious for fixing things while simultaneously breaking other things. They're quite good at that

 

[cheekybuddha]

Why are you saving as .xlsb?

 

[wackywoo105]

Why are you saving as .xlsb?

I'm not. The second image is what I'm trying to save as. I think .xlsb is some obscure excel format for trying to save a corrupted workbook when normal save fails. I haven't even looked at that file to see what it is.

If this is a google drive issue would I be better of switching to one drive or drop box? I really could do with keeping this file online if possible.

Sadly I've had a few issues recently with my Edge browser work etc, so it's hard to identify when it started.

 

[The_Doc_Man]

If this is a google drive issue would I be better of switching to one drive or drop box?

Re-reading this post, it occurs to me that it doesn't matter that it is an Excel file. I am not 100% sure on this but here goes nothing:

I am GUESSING that since you said that Access is the primary utility running this show, you are (perhaps unknowingly) using SMB protocol to do this file action, and Access just does not play well with cloud storage. Mostly because cloud storage doesn't play with SMB at all. Access looks at pieces of a file but native Excel and just about all cloud storage methods look only at whole files.

You asked about OneDrive and DropBox as alternatives. ALL cloud file systems use FTP-style protocols or HTTP protocols. Access always uses SMB. This protocol difference often leads to corruption in native Access back-ends that get cloud-hosted, so it is not out of the realm of possibility that you are running into the same problem using an Excel database linked as a table to Access. The reason this is a guess is that I don't know if Access is programmed to not use SMB when dealing with an Excel file. But I'm thinking that it does not change its normal method of file usage in that case, which means it is still dealing with SMB protocols.

If my guess is correct, then this is not a bug so much as it is a known issue of using incompatible protocols. The "fix" is to not store the file on a cloud drive and directly use it from Access. Search this forum for "cloud storage" to get a lot of articles on this subject.

Note that this is a GUESS and if one of my colleagues has more experience in this, I would not have my feelings hurt if they disagreed with me.

For new readers not up on networking protocols: SMB = Server Message Block, the Windows File and Printer Sharing protocol; FTP = File Transfer Protocol, used to transfer whole files; HTTP = Hyper-Text Transfer Protocol, used for web sites.