1

Is there any way to write a query in Access Services for SharePoint Online, and surface its results through either web services or the Client Object Model for .NET? Like the way that the tables are surfaced as lists?

Background


We have an Access database that is surfaced using Access Services for SharePoint Online. I've been given the task of retrieving some data from Access Services as an XML file to feed into another system.

There is an existing query, written in SQL to get at this data (from the time before the Access database was online) that is highly complex and I fear if I convert it to a CamlQuery or use Linq to create the query, that 1) it won't have all the functions that exist in SQL, and 2) it will become bloated, making it slow to run and hard to maintain.

Simplified example query:

select * 
from members m, info i 
where m.id = i.memberid
and year(m.date) = 1900 
UNION 
select *
from members m 
where m.id not in (select memberid from info) 
UNION 
select *
from members m, info i 
where m.id = i.memberid
and i.date_to = (select max(date_to) from info where memberid = i.memberid)
Order By m.Name
  • Hi and welcome to SharePoint.SE Catherine. You have posted a superb question, which is well within the scope of how we do things here. Hopefully someone will have an answer to your question. I'll try to find some good resources on the topic if no one else answers the question. Nice to see you here! Enjoy! – Benny Skogberg Dec 13 '13 at 7:49
0

I ended up doing this by using the C# client object model to import all the data from the relevant lists into an in-memory SQLite database, and then running a modified SQL query on the SQLite tables. As there wasn't that much data this was an acceptable method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.