Join 3,380 readers in helping fund MetaFilter (Hide)

Data connections btwn Access 2002 and SharePoint 2007, how do they work?
January 11, 2011 6:57 AM   Subscribe

I need to pretty quickly get up to speed on how to integrate an Access database with SharePoint. I know Access well, but I don't know SharePoint at all and don't understand how data connections work. The really fun part: I have Access 2002.

The scope of this thing has kind of exploded; it was supposed to be a small database with a few users, and now it's an indefinite size to be viewed by many people (but still edited by a small number). I'm sure Access was a really terrible idea and feel free to tell me so, but that's where I'm at. I'm fine with dumping the whole thing and starting in another program, but I don't think my bosses have the patience, and I'd have to um, learn a new program.

I'm good with HTML and CSS and designing stuff, but I have no idea how information gets from a Web form to its destination in a database, and how to pull that back out again. I think in normal websites this is PHP? Magic? But does that apply to SharePoint?

I have SharePoint Server 2007 and InfoPath 2010.

sorry my questions are so boring lately
posted by desjardins to Computers & Internet (8 answers total) 3 users marked this as a favorite
Well, Sharepoint is ASPx backed, with other code behind it...

It's kinda hard to recommend whether to dump and start from scratch or use current DB. without knowing somehting about the application your building ..

I'm a sharepoint admin (among other things),
If your data is mainly in lists, just import those into sharepoint, then you can create forms, and other views for people to view...

Then using the security settings, you can give read/write access to necessary people....
posted by fozzie33 at 7:03 AM on January 11, 2011

here are some good free tutorials too... i did training through learningtree (in class)
posted by fozzie33 at 7:05 AM on January 11, 2011

fozzie33, I have tables, queries, forms, reports, macros and modules in my Access database so that's why I'm a bit concerned about the complexity of moving it. I'm confident it Can Be Done, but I'm only going to do it if it will make a tangible difference down the road. (Or if someone tells me to.) If it's just a slightly prettier interface, then I dunno. But if the backend is really better in some way, OK. I need to be able to talk intelligently about WHY the backend is better, though.
posted by desjardins at 7:23 AM on January 11, 2011

well, is it backed to a SQL server or do you have a seperate SQL server...

The nice thing about sharepoint is it's an easier interface for non-technical people. but when you start adding macro's, specified quieries, etc... it makes it a little more complex...

Alot of macros can be converted into a type of workflow (if you have sharepoint designer) or you can use the out of box workflows (review items, approve items, etc...) .

Queries and reports become views of the data, and when you have multiple tables, you simply create either lookup tables in a list (for connections) or create a workflow to auto-populate data when needed.

You can also create dashboards, and other stuff for managers to have "their" view of the data.

Also, you said that it is scaling up to more users, i suppose what you can do is have sharepoint just view certain reports from you Access DB, and leave the rest as-is... i haven't done this yet, but i know it's possible... you'd probably have to do some ODBC stuff, but it can be done... take a look at this link:

it mentions access 2007, so using 2002 with SP 2007 might be a problem...
posted by fozzie33 at 7:34 AM on January 11, 2011

The internal database engine for Access has been the much maligned Jet engine, and it has typically changed for every version release of Access. Because Jet just keeps getting extended/changed for new Access capabilities, its compatibility with prior and later versions of the SQL Server database engine (which Sharepoint uses) are kind of finicky and sometimes, especially for complex multi-user Access projects, not all that easy to predict. And SQL Server is not a static product, either, especially as contained in the further rapidly evolving Sharepoint product.

So I think most responsible MS consulting operations would recommend upgrading your to Access 2010 as a first step, testing extensively for continuing existing functionality, then upgrading you to Sharepoint 2010, and going forward from that. If you've really relied on any discontinued Access 2002 features, you'll find out shortly in the first step, and you'll have to decide whether or how you can work around those, or rework your project design, but the cost of doing that is only a trial copy of Access 2010. After that, upgrade costs can rise steeply, depending on your current licensing model with Microsoft, but if you're on a standard multi-user commercial site license model, it shouldn't be prohibitive.

You probably should pick a vendor, and discuss upgrade/migration options and support. You don't want to get in over your head, technically, and its easy to do, as you scale up these small Access projects, to lots of users, and start having to deal with client-server issues, and more complex database engine products.
posted by paulsc at 7:36 AM on January 11, 2011

Here's a decent snapshot article from July, 2010 about migrating/publishing Access 2010 databases via Sharepoint 2010, that also discusses some upcoming cloud options for Sharepoint Services, which might really help in low cost prototyping and pre-ugrade, pre-deployment testing of Sharepoint.
posted by paulsc at 7:51 AM on January 11, 2011

You can actually do some impressive things with recent revs of SharePoint and Access. Unfortunately Access 2002 doesn't count as recent.

In a situation with Access 2007 + SharePoint 2007, it is possible to set up SharePoint lists to behave as if they were local tables in your Access database. I don't have a tutorial link handy but it is super straightforward along the lines of Link to SharePoint List... > Provide URL to the SharePoint List... > Magic Happens > You have something that you can SELECT/DELETE/INSERT INTO from Access.

With SharePoint 2010 + Access 2010 you even have more power in the sense that you can develop a whole application in Access and then deploy it to SharePoint where it will run as a multi-user web application. Very slick but you'll a.) need IT to administer a SP2010 installation b.) develop the skillset on how to build Access 2010 applications and have them deployed.
posted by mmascolino at 8:11 AM on January 11, 2011

I work with SharePoint every working day. Here are my thoughts.

Firstly, consider strongly upgrading to Office 2007 or 2010. WSS or MOSS is designed as part of a larger Office ecosystem, and having your tools be of a consistent version will provide better stability for your application. If at all possible, go from WSS to SharePoint Foundation Server.

> The scope of this thing has kind of exploded; it was supposed to be a small database with a few users, and now it's an indefinite size to be viewed by many people (but still edited by a small number).

This would be a big concern for me. Access pre-2007 has, in my experience, problems with data integrity in a multiuser environment unless you specifically design the database to work in a multiuser environment (which is a PITA, iirc).

How critical are your databases here? The more critical they are, the better off they are being in SP Lists (with associated ASPX forms and workflows replacing your modules and access forms) or SQL server and BCS/ BDC to expose that data to SharePoint.

If nothing else is feasible or palatable, you have to convince the powers that be to get your MS Office environment to a single version, whether it be 2007 or preferably 2010.
posted by boo_radley at 8:23 AM on January 11, 2011

« Older I want to get the full text of...   |  Dealing with the baby's regula... Newer »
This thread is closed to new comments.