4

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

Mike T
  • 271
  • 1
  • 4
  • 11
  • Why would any works be gummed up? SharePoint is based on a SQL Server database. Why would a little Access data bother it? – John Saunders Jun 28 '09 at 19:28
  • My experience with Access has not been filled with happy moments. My overall 'feeling' is that Access has a bunch of smelly kludges different from the ones in SQL server. My concern is with the translation process. If someone has actually done this with a 'real' access db ->with data<- in it. I'd like to know. – Mike T Jul 02 '09 at 21:49
  • It's hard for anyone to say if it will "gum up" a sharepoint environment without knowing more about the sizes involved and the processing requirments; almost every Access DB I've known has been running on low end desktop hardare while I've never known a Sharepoint install that didn't have a decently sized DB server behind it; the sort of thing that could handle the processing of Access without blinking. – DrStalker Jul 22 '09 at 05:42

7 Answers7

1

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.

Christopher_G_Lewis
  • 3,647
  • 21
  • 27
  • Good point. Every answer I gotten so far still is from a theoretical context. I hope someone in the future can actually say they have done this. – Mike T Jul 23 '09 at 17:39
  • 1
    This performance was improved in SP Server 2007 but is still a concern. I assume it's because SharePoint lists are complex field structures that don't map directly to a single row in SQL. They'll be stored as blobs maybe. Therefore, performance will be degraded. Also because SP is a web app at the end of the day - passing lots of rows in a grid via HTTP is always problematic. That's a problem with all webapps but hey, we all think they are the best thing since sliced bread :-) – Rob Nicholson Jul 30 '09 at 10:36
  • A good rule of thumb is that you don't ever want to look at 2000 items at one time in SharePoint. The default AllItems.aspx can be an issue with large lists. – Tom Resing Aug 07 '09 at 02:30
  • Note that Sharepoint 2010 improves performance of large lists significantly while also adding a few elements of referential integrity and indexing that the earlier versions lacked. Also, there are signficant new features integrating A2010 with Sharepoint 2010 with Access Services, such as the new Access web databases, which can be created in the Access client and run via Sharepoint in the browser. – David W. Fenton Aug 30 '10 at 20:24
0

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.

Tim Long
  • 1,728
  • 1
  • 20
  • 41
0

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.

Jim B
  • 23,938
  • 4
  • 35
  • 58
0

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.

JL.
  • 1,253
  • 9
  • 22
  • 35
0

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.

Tom Resing
  • 199
  • 2
  • 9
-1

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

SQLGuyChuck
  • 114
  • 5
  • My main reason to to have a unified Ui for the data we are using. You are rigth in that I could also upgrade the access database to a sql database and then use CAML (bleh) or other other connectionstrings in a Business Data Catalog to get the data. I would loose the interface my users are acustom to in the process though. – Mike T Jun 30 '09 at 23:41
  • I think i misunderstood your first post. So as I understand... forget the easy one touch method and just upgrade the access db to a RDP project. How would I then provide an interface for the users of SharePoint? I'm attemping to have a unified UI. – Mike T Jul 02 '09 at 21:42
  • -1 for not addressing the question. Access integration with SharePoint is a key selling point from MS. – Tom Resing Aug 07 '09 at 01:01
-1

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.

Kevin K
  • 833
  • 1
  • 6
  • 8