connection access and excel files both saved on sharepoint

vineet78

Board Regular
Joined
Oct 22, 2017
Messages
73
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0
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.
 
Upvote 0
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:

Code:
\\[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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top