Forum Discussion
opening a database with Sharpoint Links via Excel
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.
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?
- I just had a thought about a workaround until you get a better technical answer to the direct linking process.
I would try a two step process:
Create or update a temp table in Access from the complex queries just prior to when you need them. Instead of linking to the queries, link to those temp tables from Excel. They'll be local Access tables and should not raise this problem.
We often do that in Access for reports based on data from really complex queries.
If that'll get you going, maybe it'll buy time to find a way to pursue the permissions problem.
- I can get to the SP list from Excel by signing into the SP site first. I wonder if that could be done before you attempt to connect to the accdb. Next attempt....
- 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.
