To my surprise and delight I read that an adminsitrator can import (nearly directly) an Access 2007 database into a sharepoint site. Automagically, the database in transformed into lists and views with some table lookup thrown in for good measure. With Access 2007 installed on the client machine, even the forms and what not can still be reused.
To me... this sounds to good to be true.
Has anyone actually dones this? With all this good news, where is the bad stuff and pitfalls to this. Depending on the size of the database, wouldn't this some how "gum up the works" in the SharPoint database?
Sources: http://madhurahuja.blogspot.com/2007/01/adding-data-to-sharepoint-l-ists-in.html http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/17745835-a861-4984-9f44-7291fdae7d07
One thing to remember is that SharePoint doesn't handle large tables (2000+ records) well - Performance goes to crap.
I also believe that any custom indexing on tables will disappear, although simple indexing stays.
Sharepoint is very good for dealing with "flat" data - lists, basically - but no so good if your data is highly relational. Sharepoint does handle lookup fields but it's never going to be as strong as Access or a data access application. So if your data is essentially a list, then Sharepoint may be a viable solution, otherwise you need to look elsewhere.
You may be better off migrating your data directly to SQL server, perhaps even the same server that is hosting the Sharepoint databases. You can then use Access as a front end to that. This is probably your easiest first step.
If you don't want to give Access to all of yoru users, then you can build an ASP.Net web application or a Windows application to handle your data access and distribute that to your users.
I can tell you why I haven't done it. It was certainly presumed that when one of my clients want to implement sharepoint that the exisiting access application could be moved directly in to sharepoint. What I had to make them understand and show them is that once you implement sharepoint, your workflow changes and it's possible to change the reasons that you had the access database in the first place. What we ended up doing is importing some of the tables as lists and reworking the whole thing to take advantage of sharepoint workflows.
As with any migration first make sure it makes sense to keep the status quo before you just dumping it in. Migrations are the time to reevaluate the business process to make sure it takes advantage of the new technology.
You're dealing with MOSS , never forget that. Don't think for a minute it will run as smoothly as you would like. Test it, and make sure you check everything for yourself, and don't rely on the marketing hype for this product.
I routinely move data back and forth between SharePoint 2007 sites and Access 2007. In fact, the "Edit in Datasheet" view on lists uses the Access Runtime in SharePoint 2007, where 2003 used Excel.
The list of Access interops with Sharepoint on the Office site should give you a good idea of how tightly integrated the two are.
Having said that, some of the warnings about SharePoint not being a relational database hold true. SharePoint is not Access. The two both exist for good reason. However, many Access databases that hold small amounts of data yet would benefit from a web interface are ripe for import.
I would recommend sql server express or sql server if you have an installation of it already licensed. Access can act like a frontend to a database; if I remember correctly, it is called RDP (remote data project), which allows you to connect to a sql backend and utilize all the Access gui capabilities. Sharepoint won't even come close to its capabilities and you will have a headache to manage. There are wizards for upgrading to RDP project, I have done this in the past (access 2002 I think) and it works great and you can use full tsql, not access sql.
HTH, Chuck
I do not have the points yet for adding comments (newbie to SF) but I believe Mike T is mis-understanding SQL Guy Chuck's response - You can keep your Access front end and point it to a SQL Slower :) back end. You can the have the best of both worlds - the Access GUI with the SQL Slower integrity, scalability and performance.