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...
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?
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.
- Thank you for the comments and desire to help -I appreciate that. I was able create a class with...
Public oAccess As Access.Application '<!-- This will open an 'invisible access'
Set oAccess = New Access.Application '<!-- Instantiate Access
Set rsData = oAccess.CurrentDb.OpenRecordset("SELECT * FROM tblUsers WHERE AccessLevelID=1")
Works like a charm; so I discarded permission.
Yet, I like to use ADODB in order to access information. I create my queries on the 'fly' so there isn't any 'canned' queries. I store my SQL statements in an XML file -when I modify the XML then everyone gets the latest version, without having to deploy a new version.
To initialize the ADODB.Connection I use .Provider = "Microsoft.ACE.OLEDB.12.0" and the corresponding .ConnectionString = "Data Source=C:\Database\DB.accdb;Persist Security Info=False"
and -here comes the "but", but
Access in the other hand is also connected to SharePoint (with its own connection string)
ACEWSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=https://163gc.sharepoint.com/sites/NSDSProcessandReporting;LIST={17B31B95-A34B-4469-B389-2CD272090895};VIEW=;RetrieveIds=No
So, here is where I think it goes wacko.- This is good! Can I acccess those programmatically?
- Then it looks like you should be able to verify that pretty easily by creating a saved query with that test string based on the linked list. If you can connect to it from Excel using the Data Source connection, then it's probably not permissions. If you can't then it probably is permissions.
Sometimes, trouble-shooting comes down to one step at a time, building up a profile that defines the problem in a way you can then solve.
