Linking the Excel sheets in SharePoint with my Access database

Question

<p>Hello,</p><p></p><p>I have recently developed an Access Database which can tracks the activities of all the Employees in my Team. Every week all my Teammates update the activities data in Excel Sheets in SharePoint. Now I want to link all those Excel Sheets with my Access Database. When an Employee updates the data in Excel file on SharePoint then that must automatically update my Access database.</p><p></p><p></p><p>Any Help would be greately appreciated.</p>

Answer 1

Hi Pavan Kumar Nookala,

Glad to be here and share my suggestion with you.

Per your description, you have a shared Excel file which stores in SharePoint Online library. Users can all update data in this file. Now you want to link this Excel file to Access database which will automatically update when the data change in original Excel file.

After my test, you can try this way to achieve your requirement. When we want to choose the external data source, it will let us Browser from the File Explorer to choose the file. We can sync the SharePoint library use OneDrive sync client. The library will be a folder in your File Explorer like this:

 

And you can choose the Excel file as External data source by these steps: Create a new database-> External Data-> New Data Source-> From File-> Excel-> And after choose this file’s path-> Select Link to the data source by creating a linked table, and then click OK.

 

After changing in Excel file online, the Access data will refresh after the file’s syncing process. (You may need to right-click the sheet and choose Refresh link.) That’s the workaround which can achieve your requirement. Hope it can save your time on work. 

For the further information, we sincerely suggest you check these articles, you may get some help:

Import or link to data in an Excel workbook (Link to data in Excel part)  

Sync SharePoint files with the OneDrive for Business sync client (Groove.exe)   

Best Regards,

Ivy

Answer 2

Hi Ivy,

Thanks a lot for the immediate Reply. But due to the limitations in my Company, I cannot use OneDrive sync. Do you think that using One drive sync is the only solution?. If there is any other solution please let me know.

Thanks in advance.

Best Regards,

Pavan

Answer 3

Hi Pavan Kumar Nookala,

When we want to link the Excel file, it will let us to choose from the Fie Explorer. So I find this workaround for you to achieve this requirement.

Using OneDrive sync client is the way we can choose the online document path from Fie Explorer. We recommend customer to use OneDrive to sync the files from SPO to convenient their co-authoring work. We are sorry to hear that you cannot use this duo to the limitation of company.

According to this, you can link to a local Excel file and make changes on it manually. We feel bad about the extra work caused by it. Seems like it’s the way we can choose for now.

Thanks for your understanding.

Best Regards,

Ivy