Forum Discussion
opening a database with Sharpoint Links via Excel
I have created this utility in Excel (and connect to a MS Access) - the database -in Microsoft Access, is completed. I am in the second Phase of the project; I have successfully published database to SharePoint and will access it via Excel. The database I am using contains Linked Lists. The database open but crashes when reading tables.
Please explain what "publish to SharePoint" means.
Did you push the tables into SharePoint Lists? Or do you mean you put the accdb in a SharePoint location?
- I pushed the tables into SharePoint Lists. Using the Database Tools/Move Data/SharePoint option in Microsoft Access.
- It's also not entirely clear what Excel has to do with this. Can we get a more detailed picture of the overall design.
Access can link to data sources in a wide variety of formats, including both Excel worksheets and SharePoint lists.
Excel can also link to both Access tables and to SharePoint lists.
So we need to be clear about which situation is the one you have, and what it means to say that the "database open but crashes". How is the accdb being opened? Directly from within Access?
Thank you.- Hopefully I'll be able to explain properly. Excel can connect to sharepoint lists but performing complex nested queries would be a nightmare. Access in the other hand handles that nicely. So basically I am using Access as a 'middle man'. So I have an Excel App, via VBA I connect to Access which is linked to all SharePoint Lists.
I can open the database but when running an SQL using those linked tables, Excel crashes. I am suspecting some sort of permissions maybe?Thanks, that helps.
So Excel is used to pull data from saved queries in the accdb. And the saved queries are based on linked SP lists. Now I get it.
It's hard to know where the problem lies in a complex environment. I know that I've encountered problems getting Excel to even recognize queries in Access, but I don't do it often enough to have a lot of tricks up my sleeve.
As a trouble-shooting step, have you been able to link your Excel workbook to a very simple, one table query in Access just to verify that the link can be created? If so, then it's going to be down to the queries. If not, you probably are looking at permissions somewhere along the line.
Another consideration is whether any of the queries in Access are parameterized, i.e. contain criteria that reference things like controls on forms, for example. If so, that's probably a good suspect. The parameters have to be resolved at run time, such as referencing the current values in those controls.
If the queries do run properly in Access, and you can link to a simple test query, then the next thing I'd suspect would be something in the Excel VBA that is raising an unhandled error.
Another trouble-shooting step could also be to create a complex query based on local Access tables in the accdb to verify whether or not it is the SP list that is the problem.
Finally, it may well be that your hunch is right and that it is a permissions problem. Access needs permissions to open the linked SP lists. Perhaps there's a problem with Excel not having that permission. Testing step-by-step seems the only way to isolate where in the sequence the failure arises.
