Custom forms in Sharepoint with MS SQL Server as Backend. Is it possible?

Question

We're evaluating using SharePoint 2010 as our project management tool.

Specifically, the system needs to satisfy the following:

1. Discussion groups
2. Project management (simple issue tracking, no complex workflows or vcs integrations)
3. News feed for the project(s)
4. File sharing based on authorization/user-roles
5. Custom homepage
6. Custom forms using MS SQL Server as a backend and contents of old forms searchable from the user interface.

Now, I think [1-5] is possible using SharePoint (Comments are always welcome :)). I'm not sure about [6]. Is it possible? For instance, can an admin or a user of the SharePoint portal, create a custom form (without any programming) that uses MS SQL Server as a backend and publish it to the portal so that other users can also perform data entry?

If it can be done (be it with or without some programming), can users perform text search on form data using the SharePoint interface?

Answer 1

In Sharepoint 2010 OOB, it looks like you can create forms that do full CRUD (create, read, update, delete) of SQL Server tables without programming. There are a series of videos that can give you a pretty good idea of how that is done.

OOB sharepoint foundation 2010 seems to have the following limitations:

1. Only tables (in Sharepoint speak Office item-types) that were anticipated by Sharepoint are supported OOB (contacts, tasks, calendars...)
2. Subforms are not supported.
3. It seems like only one table and no relationships can be worked with period. So no combo-boxes populated by queries.

Sharepoint Enterprise 2010 seems like it supports subforms and the possibility of handling table relations (in sharepoint speak "associations") in a rich manner without coding. You can get a sense of what's possible from this video.

For rich forms without coding, the answer seems like it should be Access Services on Sharepoint. However, Access Services can't use Sharepoint's Business Connectivity Services (BCS) to connect to a SQL Backend. In other words, Access Services cannot connect to SQL Server. See this MSDN communit post for what seems like a conclusive statement about this limitation of Access Services.

I do not know whether the SQL backend data can be searched using the Sharepoint interface.