SharePoint storage for FE database problem

[Minty]

Hi All, and forgive my ignorance on all things SharePoint!

We have a client who would like to store the current version of the Access FE file on share point as a central point for distribution to the end-users.
This sounds really cool as we have access to this and can simply upload test versions into a test folder and then the client can migrate the test version into the "Release" folder, following successful testing.

There appear to be 2 problems associated with this;
Firstly - We keep seeing an error Microsoft Access can't find the file \\Clients_Sharepoint_Address\etc\etc\ when opening the front end on a Local user copy as if it's trying to sync with the SharePoint version. I'm guessing this is something that can be turned off but can't see anything obvious or don't have enough access rights?

Secondly - I can't see an easy way of retrieving the file without mapping the SharePoint location as a local drive and copying it, to enable automation and distribution of the front end, assuming the first issue can be overcome?

 

[theDBguy]

Hi. Check out this other thread. Hope it helps...

Get UNC for WebDav folder from Sharepoint doc library URL

It's been years since I did this. Looking at the information here, but must not be getting it quite right. Does anyone know how I would get the UNC for a WebDav folder, in the format \\UNC , for this document library...

www.access-programmers.co.uk

 

[Minty]

@theDBguy That certainly helps point me in a sensible direction, thank you.

 

[theDBguy]

@theDBguy That certainly helps point me in a sensible direction, thank you.

You're welcome and Good Luck!

 

[Isaac]

Hi All, and forgive my ignorance on all things SharePoint!

We have a client who would like to store the current version of the Access FE file on share point as a central point for distribution to the end-users.
This sounds really cool as we have access to this and can simply upload test versions into a test folder and then the client can migrate the test version into the "Release" folder, following successful testing.

There appear to be 2 problems associated with this;
Firstly - We keep seeing an error Microsoft Access can't find the file \\Clients_Sharepoint_Address\etc\etc\ when opening the front end on a Local user copy as if it's trying to sync with the SharePoint version. I'm guessing this is something that can be turned off but can't see anything obvious or don't have enough access rights?

Secondly - I can't see an easy way of retrieving the file without mapping the SharePoint location as a local drive and copying it, to enable automation and distribution of the front end, assuming the first issue can be overcome?

On the first item,maybe they are opening it directly rather than saving a copy? If I click on an Access file that I've hosted in a SP library, and open it directly, I still need to save a copy to my local before it will behave as anything other than a 'server version'. I don't know if that's your case or not.

On the second item, (and I should probably add something to that thread as well), I'm still experimenting with the webdavroot thing. What happens is that if someone tries my code, like OpenTextFile("webdav path to text file"), it will err the first time 'path not found'. But if their PC has been 'woken up' to the service first, by (for example), navigating to the \\webdavrootpathtofolder , after that, then my vba code to open or transfer or access the file will work. Something to keep in mind?

Lastly, if the thread dbGuy pointed you to doesn't work out well for you, here is a script I wrote recently to map a drive on the fly to a doc library.
I ran it as vbScript.

'***************************************************************************************
'***************************************************************************************
'    THIS PROCESS CAN TAKE A FEW MOMENTS-LET THEM KNOW SOMETHING'S HAPPENING

Msgbox "Press OK to begin opening the database"


'***************************************************************************************
'***************************************************************************************
'    MAP SHAREPOINT DRIVE

dim strChosenDriveLetter
'Try a, b, c, d, e, f
If ReportDriveStatus("a")="false" then
    'doesn't already exist
    strChosenDriveLetter="a"
elseif ReportDriveStatus("b")="false" then
    'doesn't already exist
    strChosenDriveLetter="b"
elseif ReportDriveStatus("c")="false" then
    'doesn't already exist
    strChosenDriveLetter="c"
elseif ReportDriveStatus("d")="false" then
    'doesn't already exist
    strChosenDriveLetter="d"
elseif ReportDriveStatus("e")="false" then
    'doesn't already exist
    strChosenDriveLetter="e"
else
    strChosenDriveLetter="f"
end if
Dim objNetwork, strRemoteShare
Set objNetwork = WScript.CreateObject("WScript.Network")
strRemoteShare = "https://something.something.something.com/sites/site1/site2/Isaac%20Test%20Document%20Library"
objNetwork.MapNetworkDrive strChosenDriveLetter & ":", strRemoteShare, False
wscript.sleep 2000

'*******************************************************************************************
'*******************************************************************************************
'    GET LOCAL FOLDER PATH

dim strDatabaseFolder, strDBPath_Local, strDBPath_Network, strUserName, wshShell
Set wshShell = CreateObject("WScript.Shell")
strDatabaseFolder = wshShell.ExpandEnvironmentStrings("%APPDATA%") & "\DatabaseName"


'*******************************************************************************************
'*******************************************************************************************
'    COPY CURRENT FE VERSION TO LOCAL FOLDER - + icon
dim fso, strIconLocal, strIconNetwork
set fso=createobject("scripting.filesystemobject")
strDBPath_Network= strChosenDriveLetter & ":\DatabaseName.accdb"
strIconNetwork = strChosenDriveLetter & ":\DB.ico"
strDBPath_Local=strDatabaseFolder & "\DatabaseName.accdb"
strIconLocal = strDatabaseFolder & "\DB.ico"

if fso.folderexists(strDatabaseFolder)=False then
    fso.createfolder(strDatabaseFolder)
end if

fso.copyfile strDBPath_Network,strDBPath_Local,True
fso.copyfile strIconNetwork,strIconLocal,True

set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase(strDBPath_Local)
AccessApp.CurrentDb.Properties("AppIcon") = strIconLocal
AccessApp.RefreshTitleBar

'*******************************************************************************************
'*******************************************************************************************
'    REMOVE NETWORK DRIVE TO SHAREPOINT

objNetwork.RemoveNetworkDrive strChosenDriveLetter & ":"








'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
'********************************************************************************************
Function ReportDriveStatus(drv)
   Dim fso, msg
   Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.DriveExists(drv) Then
      msg = "true"
   Else
      msg = "false"
   End If
   ReportDriveStatus = msg
End Function

Those are about all the options I can think of. We are going to be trying out doc library as distro method too - good luck!!

 

[Minty]

@Isaac - Thank you for that, that looks super helpful. I'll let you know how I get on in a week or so when that system is brought up to speed.

 

[IbrBadri]

@Isaac - Thank you for that, that looks super helpful. I'll let you know how I get on in a week or so when that system is brought up to speed.

Me also, looking for your replay.

 

[Minty]

I'm revisiting this finally, and am struggling.

I can open the Sharepoint folder without any issues in a browser window, and do the same via VBA.
But what I cannot do is map it to a network drive - I continually get an authentication error.

I can't seem to map the drive even outside of Access. Browsing to the location via explorer is troublesome, I had to open IE11 and allow 4 or 5 warnings through before it would open in file explorer.

If i cut and paste the correct path from the opened file explorer window, into a fresh file explorer window I get a "Windows can't find ..." error.

The same address pasted into any browser window opens the SharePoint folder without a problem.
I can create folders, and drop documents in there no problem.

Highly frustrating. So I thought what if I create a batch file and execute that from Access, that's bound to work. Nope, Access Denied. The Error description is slightly more helpful though;
Access Denied. Before opening files in this location, you must first add the web site to your trusted sites list, browse to the web site, and select the option to login automatically.

Buuuuttttt - I never need to login in. I have permission through my Microsoft account, which IS logged in.
It seems to be a Catch-22 situation.

EDIT : The reason for the need to map it is to check & create folders programmatically if they don't exist.

 

[Isaac]

That sounds frustrating, sorry to hear of your troubles. It always just worked for me - although I feel I vaguely remember seeing that bolded part somewhere years ago. If I recall anything else helpful I'll post

 

[Minty]

I have made some progress since posting, but not any actual final solution.
You have to add the site to your trusted sites in IE11, (Why of why is it buried in there!!) And then it fails with a different error.

My next ploy when I'm not bored of it all is to log out and in of IE11 on the SharePoint site and play around with that.
This page is written recently for Win 10 https://hardsoft-support.kayako.com/article/87-how-to-create-a-sharepoint-link-mapped-drive
and It looks like it is getting closer but still fails. It may be because my login only has access to a limited number of folders within the whole SharePoint site, but that will be true of the end-users as well.

 

[JMongi]

*This could thoroughly muddy the waters

I have a little experience mapping drives from a sharepoint but don't know if this is actually relevant anymore. We always had to log in using IE, then browse the sharepoint files by "Opening in File Explorer" or something like that. THEN map the drive. I'm not sure how that process changed in more recent Microsoft 365 updates. I do know that the whole hoop jumping of "view in file explorer" or whatever it is called is a way to trigger the windows network credential sharing needed to make it all work.

I doubt you need to do those steps exactly, but it could be that Isaac's system is set up to create/persist those network credentials/handshakes where you may have to programatically force those interactions to occur. Sorry I don't know more details to help. I just know there is SOME hoop jumping needed to pass around the authenticating credentials to the appropriate spot.

 

[Isaac]

Actually jmongi reply made me think of something. A few months ago I was testing a proof of concept of writing to text files in a sharepoint doc library using VBA. I remember I had to do something via code to 'wake up' root dav ssl first. Here is the code that I got working that worked for a user "every time" - even the first time they tried it.

This has more to do with dav root than it does mapping a drive, but the concepts might be overlapped.

PS wait I know what it was--it was that I had so much trouble mapping the drive, that I SWITCHED to using davwwwroot in order to reference the path AS IF it were an explorer path - which is I think a superior method if you can find out your path. You can skip the whole drive mapping and end up with a useable path.

Sub AddTextToDocLibrary()
Dim fso As Object, ts As Object, strValue As String, strTextFilePath As String, retval, strFolderPath As String
On Error GoTo errhandler
strTextFilePath = "\\teamsb.server.com@SSL\DavWWWRoot\sites\parentsite\childsite\Test20200930\Test Shared Text file.txt"
strFolderPath = "\\teamsb.server.com@SSL\DavWWWRoot\sites\parentsite\childsite\Test20200930"
strValue = ThisWorkbook.Worksheets("Sheet1").Range("E3").Value
Set fso = CreateObject("scripting.filesystemobject")

'first wake up system to recognize these davwwwroot type paths:
On Error Resume Next
'retval = Shell("notepad.exe """ & strTextFilePath & """", vbNormalFocus)
Shell "C:\WINDOWS\explorer.exe """ & strFolderPath & "", vbHide
On Error GoTo 0

Set ts = fso.opentextfile(strTextFilePath, 8)
ts.writeline strValue
ts.Close
MsgBox "Successful"

Exit Sub
errhandler:
MsgBox Err.Description, vbCritical, "  "
End Sub

 

[Minty]

@Isaac That's interesting, I'm only interested in creating a folder and moving documents to it, no need to open them.

I've not seen the DavWWWroot part of an address anywhere though, at any point of my meanderings through this. Is this because I can only access certain subfolders do you think?

Unless I can solve it, I think I'm going to get the end-users to get a mapped drive set up through Group Policy. Not ideal, but at least I can easily replicate the functionality during development.

 

[Isaac]

I've not seen the DavWWWroot part of an address anywhere though, at any point of my meanderings through this. Is this because I can only access certain subfolders do you think?

I don't know much about it, except for it seems that DavWWWroot does exist - including for you - but it's just not visible anywhere. You basically just have to learn about the functionality and then code for it and see if it works. I remember reading various articles on how to structure the path correctly - for at least a day before I figured out the correct "path" for my organization. Once I typed that magical path into Explorer, it just "worked". I remember researching along the lines of "find davwwwroot path for sharepoint doc library" and some of the articles weren't very good. Whenever you find yours, it will 'just work' - at least that was my impression I got from the experience.

 

[Minty]

After messing around with this for hours, I had reached the point of giving up. I had decided to just get the end-users to map the drive and create the folders if required.
When doing some other code clean up, I thought I would just try to see if I could work out if the folders existed or not I could at least put the correct folder name into the clipboard for them to paste, and used the Files System object FolderExists() function with the \\sharepoint\folder1\folder2 etc etc\ path
To my surprise it just worked! So I then used the same process fso.CreateFolder() and that just worked!

I'm hoping that it's nothing to do with numerous other things I had been fiddling with and a setting somewhere that I have adjusted.
Fingers crossed!

 

[Isaac]

You mean with dav www root or some other path method?

 

[Minty]

Just the standard \\companyname.sharepoint.com\documents\personel\foldershere\subfolders type thing.

I couldn't ever get explorer to open with the DavWWWRoot part included.
I have not managed to get it to map to a drive letter either. The authentication token even with all the IE 11 login, trusted sites adjustments, etc etc, just didn't work. It refused to let me connect in code.

Which is why my gob was so smacked that the straight forward FSO methods just worked.

 

[Isaac]

That's awesome. That also makes me realize I may not have tried 'long enough' with FSO (etc) using your \\company name.sharepoint.com method before concluding that davrootwww was the only way to refer to a path without a mapped drive.
One more tool in the tool belt..