opening a database with Sharpoint Links via Excel

[GustavoEMiller]

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.

[#2]

@GustavoEMiller 

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?

[#3]

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.

[#4]

I pushed the tables into SharePoint Lists. Using the Database Tools/Move Data/SharePoint option in Microsoft Access.

[#5]

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?

[#6]

@GustavoEMiller 

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.

[#7]

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-2CD27209089...

So, here is where I think it goes wacko.

[#8]

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.

[#9]

@GustavoEMiller 

Okay, so I gave in and created my own test. 

Here's the result:

[#10]

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....

[#11]

This is good! Can I acccess those programmatically?

[#12]

I don't know that, unfortunately. Still trying things out. It didn't help to just sign in to SP and then try to open the query in the accdb, though. Maybe someone in a SharePoint forum will have better insight into how SP handles permissions....

[#13]

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.

[#14]

I work at the government and they are allergic to Microsoft Access or something like that. As a consultant I have to use what they give me. I asked originally for SQL Server but no cigar, then I asked for MySQL which they agree but they are not willing to install the drivers to connect (Grrr.....). So my last resource is SharePoint just to prove them it can be done. I am hoping I can get a SQL box from all this. I have been working on Access since the beginning I think it was Access 2.0, v1.0 was a disaster. Good old days; hmmm.... guess I am old too.

[#15]

I started with 2.0 as well. It kept me employed for over 20 years. I'm retired now (but trying to stay current as much as I can.).

I guess, if this is a Proof of Concept, the interim workaround of converting your SQL to make-table queries in order to generate temp tables that Excel CAN link to might fill the bill.

[#16]

Well, it has been profitable. Young professionals have a hard time with VBA, so they find us and pay as a much as they pay the youngsters (and even more). But I also do Razor pages in C#, VB.Net, Javascripts, TSQL and whatnot. But, I am ready to retire as well; just being a bit stubborn. Thank you for your time and efforts...