connection access and excel files both saved on sharepoint

[vineet78]

Hi

can you help on how to create connection between excel file and access database both saved in sharepoint. i have code to connect them on shared drive but not able to do so in sharepoint.

Appreciate your help )

Vineet

 

[JonXL]

When you say "saved in sharepoint", do you mean they're just stored out there like any other file?

What is the code you currently have and what have you tried to make it work?

 

[vineet78]

Yes Jon, they are saved just like any other file on sharepoint.
Currently I have following code

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbname & ";"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

where dbname = path of the access database file name saved in share drive.

The above code works fine on shared drive but dont know how to use in sharepoint.

 

[vineet78]

any help please?

 

[stumac]

I have limited experience of this - I found a site on google that helped but ended up I had to change from the URL to the following:

\\[B][COLOR=#ff0000]company[/COLOR][/B].sharepoint.com@SSL\DavWWWRoot\sites\[B][COLOR=#ff0000]Sitename[/COLOR][/B]\[B][COLOR=#ff0000]libraryname[/COLOR][/B]\"

I found the exact path by opening the library and selecting Open with explorer, right click a file and select properties - the Location was then displayed.

 

[vineet78]

Hi Stumac

thanks for your help on this !
I am not able to find this path for the access database saved in sharepoint.
I would like to share with you the screenshot of database saved as following , oh i think cannot save the attachment here.

ok Access database is saved as XXX-YYY.accdb on following path

https://share.connect.mycompany/teams/ro/APAC/APAC-FPA/Shared%20Documents/Forms/AllItems.aspx?RootFolder=%2Fteams%2Fro%2FAPAC%2FAPAC%2DFPA%2FShared%20Documents%2FTest%2Ddatabase&FolderCTID=0x012000DCE1B94AD47E2646BD9AEB1450C96FD2&View=%7B798CCFA0%2DA2BA%2D4813%2D80BE%2D3C8C6496F5EA%7D