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