Linking of Excel sheets in SharePoint directly to MS Access (1 Viewer)

[PGL]

I have a number of Excel spreadsheets (data sources) hosted on Sharepoint that I need to link to one single Access database.

At first. I cannot link the spreadsheets directly to Access because Access cannot read the spreadsheets on SharePoint to make the links.

Secondly, I can export the spreadsheets from SharePoint to a local folder on my PC and keep them 'refreshed'. The problem with this is that I cannot link Access to the local refreshed spreadsheets because of the (auto) extention .iqy. Access does not know the extention .iqy and hence the two cannot be linked at all.

Thus: How do I link an Excel spreadsheet hosted on SharePoint to MS Access.

Please help (urgently).

Appreciate
PG

 

[Pat Hartman]

It seems like you can't.

Can Access copy the spreadsheet from SharePoint to a local folder where it can link to it? If it can, then you would copy and link the file each time you wanted to use it in Access. If Access can't get the spreadsheet out of SharePoint, complain to Microsoft. If enough people complain, they listen.

Another possibility is to export the data from the spreadsheets to a SharePoint list. Then the spreadsheet, which I presume has calculations and formatting, can link/import the list and Access will be able to also.

 

[PGL]

Thank you Pat,

Scenario 1: Access cannot copy the sheets from SharePoint. BUT, SP can export the sheets to a local hard drive effectively and keep it 'mirrored'. The problem here is that the exported sheet's extention is .iqy and Access does not recognised it.

Scenario 2: I can try this but I'm not up to par with SP Lists. Why is it called lists? I am going to try this route and see what happens.

 

[Pat Hartman]

They are called lists because that's what they are. They are not relational tables. I wouldn't recommend them as the BE for an Access app but for sharing data that is maintained by other apps, they should be fine. Don't expect good performance if you have more than a few thousand rows.

I don't work with SharePoint so I'm not even sure that Excel can link to lists.

If you are only using Excel to manage the list and not to do any calculations, then moving from Excel to a SharePoint list is the best solution.