problem with Access and Word interop inside OneDrive

[lauro]

Hi,
I have a problem linking a Word file as an OLE object inside an Access Table/Form, if it is saved inside a OneDrive Folder.

In the attachement there is a small database to simulate the problem.
You should try to put this Access file inside and outside a OneDrive folder to fully understand the problem.

Inside the database there is a table tblFiles with a field for OLE Objects (Word documents); and the related form frmFiles with a Bound Object Frame control for those OLE Objects.
When you click the addRecord in the footer of the frmFiles a new Word file is created; something is written in its content and the only 3 fields of a new record are filled with the Name, FullName and OLE Object of the newly created file.
To link the file I used the following code, where BOFC is the Bound Object Frame control and the string sFullFileName is obtained or joining the path of the Access file with the name of the Word file or taking the FullName property of the Word file.
with BOFC
.Class = "Word.Document"
.OLETypeAllowed = acOLELinked
.SourceDoc = sFullFileName
.Action = acOLECreateLink
end with

If the files are saved outside OneDrive Folders everything works as supposed.
If the file are saved INSIDE a OneDrive Folder there could be one of the following two problems:

• If sFullFileName is the local path then Error 2737 (Impossible to found the file containing the OLE object linked that you try ti update through the comand link OLE/DDE) [or something similar, mine is in Italian]
• If sFullFileName is the cloud path then Error 2101 (setting non valid for this property) [or similar]

It seems very strange to me that there are no solutions.
I don't want to obblige the user
to save the files outside OneDrive folder
neither to abandon the sync of Office files with One Drive

The SourceDoc property of a Bound Object Frame control has always to be a local path?
There are other ways to link and show a Word file saved on the cloud in an Access form?

No problems with Google Drive or Dropbox

Thank you very much for every suggestion.

Lauro

 

[Pat Hartman]

Please define "problem" Are you getting an error message? Post your code.

 

[lauro]

Please define "problem" Are you getting an error message? Post your code.

Thanks for your very quick answer. I was editig the post.
Lauro

 

[Gasman]

Now crossposted at https://www.accessforums.net/showthread.php?t=87135

 

[The_Doc_Man]

Your basic issue may well be that working with Access over a OneDrive path is your problem. I did a quick check on the web and my research at least strongly suggests that OneDrive does not support SMB protocol, which unfortunately is a sine qua non for Access usage. There are workarounds using Remote Desktop Protocol (RDP) packages, but they are not free solutions.

You can control Access on your local system with SMB and can, at least in theory, start a whole-file activity to or from an external drive. But Access is based on use of Server Message Block protocol, which allows for partial file sharing. OneDrive does not appear to support that, or has some trouble with it.

As a side note, please be aware that cross-posting, the act of posting the same problem on two different forums, is generally frowned upon if you don't notify us ahead of time that you are doing so. Cross-posting is a way for us to waste our time by not telling us you have already asked for help elsewhere. It leads to potential duplication of effort, which is why we don't like it.

You are new to the forum so probably have not run into this before, but let's politely say it is considered a breach of etiquette to cross-post without notification. It is nowhere nearly so bad if you include notice. The idea is that before we answer your question, we can check the cross-post to see if you already have the answer we would have provided anyway. Since none of us are paid for our time here, "blind" cross-posting wastes our volunteer time and is therefore demeaning of our efforts.

Now, having given the obligatory chastisement, the good news is that a single case of cross-posting is not grounds for banishment. It is grounds for exactly this kind of comment. Please remember for any future posts that we don't like unannounced cross-posting and would STRONGLY prefer to know that you have asked for help elsewhere, too. As long as you don't do it again, we can easily and happily forgive you.

 

[isladogs]

I was expecting this to fail when the file was in OneDrive for the reasons stated above.
To my surprise, it worked. I ran it 3 times in OneDrive without errors

 

[Suznraj]

This is a common issue. The original file owner has locked you out without informing you. Open the OneDrive app and select the file. Click the Open the details pane button from the top-right corner and check the Has access panel.

 

[lauro]

Your basic issue may well be that working with Access over a OneDrive path is your problem. I did a quick check on the web and my research at least strongly suggests that OneDrive does not support SMB protocol, which unfortunately is a sine qua non for Access usage. There are workarounds using Remote Desktop Protocol (RDP) packages, but they are not free solutions.

You can control Access on your local system with SMB and can, at least in theory, start a whole-file activity to or from an external drive. But Access is based on use of Server Message Block protocol, which allows for partial file sharing. OneDrive does not appear to support that, or has some trouble with it.

As a side note, please be aware that cross-posting, the act of posting the same problem on two different forums, is generally frowned upon if you don't notify us ahead of time that you are doing so. Cross-posting is a way for us to waste our time by not telling us you have already asked for help elsewhere. It leads to potential duplication of effort, which is why we don't like it.

You are new to the forum so probably have not run into this before, but let's politely say it is considered a breach of etiquette to cross-post without notification. It is nowhere nearly so bad if you include notice. The idea is that before we answer your question, we can check the cross-post to see if you already have the answer we would have provided anyway. Since none of us are paid for our time here, "blind" cross-posting wastes our volunteer time and is therefore demeaning of our efforts.

Now, having given the obligatory chastisement, the good news is that a single case of cross-posting is not grounds for banishment. It is grounds for exactly this kind of comment. Please remember for any future posts that we don't like unannounced cross-posting and would STRONGLY prefer to know that you have asked for help elsewhere, too. As long as you don't do it again, we can easily and happily forgive you.

Thank you for your kind reply.
From now on I will avoid crossposting or at least I will immediatly make clear if and where I asked similar questions.

As you suggested I read something, superficially, about SMB protocol.
I still have a couple of doubts.
First. When I ask OneDrive to keep always offline the file of a folder, they are not treated as normal file on my file system?
Second. And in the offline folder the files are first saved on line and then sync locally or vice versa?
Third. There is a way (through OneDrive API?) to tell OneDrive to save the files of a certain folder first locally and then syncronize them on the cloud?

I don't know. But if I don't find an easy solution I'm obliging the user to install the Access back end and all the Word files created in a NON Onedrive folder. Which will be a pity.

Thanks, again, Lauro

 

[lauro]

I was expecting this to fail when the file was in OneDrive for the reasons stated above.
To my surprise, it worked. I ran it 3 times in OneDrive without errors

View attachment 104912

Thank you for the time you dedicated to my problem.
I'm really surprise that you don't get the error I'm getting.
Did you try to use for the full name of the word files both the FullName property and the one builded joining path+ name?

What could explains the difference between your and mine results?

Thanks, Lauro

 

[isladogs]

I used it exactly as you instructed.
To be honest, I've no idea why it worked without error for me but not for you.

However, I would strongly recommend against running any Access file from an online storage location such as OneDrive, GoogleDrive or Dropbox.
At some point you will experience corruption.

 

[The_Doc_Man]

First. When I ask OneDrive to keep always offline the file of a folder, they are not treated as normal file on my file system?
Second. And in the offline folder the files are first saved on line and then sync locally or vice versa?
Third. There is a way (through OneDrive API?) to tell OneDrive to save the files of a certain folder first locally and then syncronize them on the cloud?

1. The local file in a local folder is a normal file on your local file system. However, OneDrive has options about how it stores things.

2. From my understanding of the way Windows works, which is admittedly limited with respect to OneDrive, everything is local first.

3. Again, I think what you asked is the normal order of operation. I believe that if you don't tell it otherwise, local files are used locally. To do otherwise is the setting that ISN'T the default. (Sorry for the double negative there...)

It may be possible to name a file that is on the cloud and have OneDrive download it for you, but remember this: In Windows, EVERYTHING YOU DO is local unless you have a remote server that knows you and will take commands from you. Therefore, all major utilities will operate locally first. In the absence of SMB protocol, I am fairly sure that if you DID name a cloud-only (i.e. offline) file for some utility to work on, you would get a temp copy of it before the utility started working on it.

It may be an incorrect viewpoint, but I see OneDrive as the equivalent of an automated offline (or "nearline") backup service that allows retrieval of the backup files from alternate places, systems, and accounts (based on permissions, of course.)

"Nearline" merely refers to how long the file system waits before making the backup. The shorter the delay, the "nearer" it is. I think the majority of the controls that are REALLY important have to do with the length of the SYNC delay. Everything else is for security and reliability issues.

 

[lauro]

I used it exactly as you instructed.
To be honest, I've no idea why it worked without error for me but not for you.

However, I would strongly recommend against running any Access file from an online storage location such as OneDrive, GoogleDrive or Dropbox.
At some point you will experience corruption.

Do you have One Drive for Business or Personal?
Lauro

 

[lauro]

1. The local file in a local folder is a normal file on your local file system. However, OneDrive has options about how it stores things.

2. From my understanding of the way Windows works, which is admittedly limited with respect to OneDrive, everything is local first.

3. Again, I think what you asked is the normal order of operation. I believe that if you don't tell it otherwise, local files are used locally. To do otherwise is the setting that ISN'T the default. (Sorry for the double negative there...)

It may be possible to name a file that is on the cloud and have OneDrive download it for you, but remember this: In Windows, EVERYTHING YOU DO is local unless you have a remote server that knows you and will take commands from you. Therefore, all major utilities will operate locally first. In the absence of SMB protocol, I am fairly sure that if you DID name a cloud-only (i.e. offline) file for some utility to work on, you would get a temp copy of it before the utility started working on it.

It may be an incorrect viewpoint, but I see OneDrive as the equivalent of an automated offline (or "nearline") backup service that allows retrieval of the backup files from alternate places, systems, and accounts (based on permissions, of course.)

"Nearline" merely refers to how long the file system waits before making the backup. The shorter the delay, the "nearer" it is. I think the majority of the controls that are REALLY important have to do with the length of the SYNC delay. Everything else is for security and reliability issues.

Thank you again.
I was keeping in asking you where are the files saved first, because if they are first saved locally - as also you think - I should not receive the Error 2737, when I link using the local path name of the file...
But maybe as you say OneDrive save in a particular way.
Also the always offline files seem to be diffrent from the other ones.
Lauro

 

[isladogs]

Do you have One Drive for Business or Personal?
Lauro

Both!

 

[The_Doc_Man]

@lauro - You asked where Windows stores files using OneDrive. It all depends on how you set it up.

Save disk space with OneDrive Files On-Demand for Windows - Microsoft Support

Learn how to use OneDrive Files On-Demand to sync your OneDrive files and save disk space in Windows 10.

support.microsoft.com

This article might help a little in answering that question.

 

[lauro]

Both!

And in both my code works fine?
I have Onedrive personal

 

[lauro]

@lauro - You asked where Windows stores files using OneDrive. It all depends on how you set it up.

Save disk space with OneDrive Files On-Demand for Windows - Microsoft Support

Learn how to use OneDrive Files On-Demand to sync your OneDrive files and save disk space in Windows 10.

support.microsoft.com

This article might help a little in answering that question.

Thank you for your suggestion.
But I'm still in the ... dark ... cloud
Lauro

 

[isladogs]

And in both my code works fine?
I have Onedrive personal

If you look at the screenshot in post #6, I tested it in OneDrive Personal
I didn't check the Business version

 

[lauro]

If you look at the screenshot in post #6, I tested it in OneDrive Personal
I didn't check the Business version

I see.
Very strange.
Thanks again

 

[lauro]

This is a common issue. The original file owner has locked you out without informing you. Open the OneDrive app and select the file. Click the Open the details pane button from the top-right corner and check the Has access panel.

Thank you Suznraj for your answer and I'm sorry if I'm so late to reply.
I don't understand very well what you are suggesting. To me it seems that the ownership of the file are not strange or changed.
Here what I get after selecting the file and open and clicking on Properties.
Thanks again, Lauro