Query SharePoint based Db

[mikeymay]

I have a Db located in a sharepoint folder and I am struggling getting a connection to open.

I have an Excel front end file which needs to query and write to this database.

When building in test mode on my desktop, the following in the Excel front end file worked perfectly well and the connection was established

Public Const strDbFile = "Drivers Master List.accdb"

Sub ADODBConn()

Dim strPath As String

strPath = ActiveWorkbook.Path

Set cnConnection = CreateObject("ADODB.Connection")

strDbName = strPath & "/" & strDbFile

cnConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDbName

Now the Db file is in a SharePoint folder, the

cnConnection.Open

command produces an 'Automation Error'. I am confident I have the correct folder names in the strPath as this will be a constant variable (the Db file will always be kept in one place) so I am unsure as to why the connection won't work. A bit more info - I previously stated the 'https:' in the SharePoint folder location and the automation error displayed instantly. I have taken the 'https:' out so there is just '//foldername/etc/etc' and this thinks about it for 10 seconds then produces the Automation error.


TIA

 

[Micron]

I've only worked between Access and SharePoint Excel files, but what I learned was that the path had to be the network path, not a web type of URL. It resembled the path of any other network drive that we had, but I had to get access permissions on it first. What might help you is to have an error handling routine to see if you're getting a specific error number that you can use to get on the right track (assuming all you get isn't just "Automation Error" and nothing else). I had to handle checking the workbook in and out as well, otherwise it was pointless to write to it. Not sure if that will be a hurdle you'll have to overcome as well. I supposed it depends on how they set up the file, but then again, Excel isn't really a multi-user app like Access, so it may not even be relevant.

 

[mikeymay]

Thanks Micron

I have spoken to Microsoft and they have confirmed the only way to write to a db is to check it out to the users desktop....but I need to the db to be accessible my multiple users possibly at the same time so it seems it's not possible.

 

[Micron]

I guess the better approach would be to have a split (front end / back end) whereby each user had their own fe copy and keep the be tables in a shared network folder. This way you could reverse the approach and have Access pull in the Excel data instead of the other way around. If need be, Access could also push the data to Excel, even if the workbook is in SP. It's not too hard to check in/out the workbook and move data to it for charting or whatever. Perhaps some users could get by with the runtime version of Access if licensing is an issue.
Good luck!