Note: This is the first part of a three part series, you can find the second part here and the third part here.

Hello, Access with SQL Server blog readers! This is my first post since joining the firm and I look forward to many more! Both Juan and I will be blogging here about Access, SQL Server and SharePoint and we love reading your comments, so please leave us feedback below.

My specialty is Access and SharePoint, so what better way to get started than with a series on both?

SharePoint and Access: How do they fit together?

Access and SharePoint

If you’ve been working with Access for a while, it’s hard to miss the push Microsoft has been putting behind SharePoint integration with Access. The integration started as early as Access 2003, continued through 2007 and has blossomed with 2010. Without question, the number one feature in the latest release is publishing your database to the web with SharePoint. In this first post, I’m going to discuss what is SharePoint and why was it used to publish Access web databases, replace the Jet Replication and other integration features.

So what is SharePoint?

If you were to go over to the Microsoft SharePoint page, you may see a long list of features and success stories described in nebulous terms. Indeed, there is no one simple definition to tack onto SharePoint. Instead of discussing its capabilities, I believe we’re better off discussing the intention behind its capabilities, and I’m going to use Access as an analogy.

SharePoint is to Web Apps as Access is to Visual Studio

Why do we use Access instead of Visual Studio? Isn’t Visual Studio more powerful and lets us do all those cool things in code? Or better yet, why not just hire a professional to set up everything for us? The fact is, Access is accessible (excuse the pun) to *non*-programmers; people who need to track their data as part of their job and this is the crucial difference. To use Access, you’re not required to be a professional to get started, and because you can do it yourself. You don’t have to go and talk with your IT department who may be overworked with other, more important projects. Precisely because of this ability to create something on a worker’s desktop, Access is has become the most popular database in the world.

Some of you may be thinking, “but there are complex Access applications that require specialized consultants out there!” Well, yes, but that usually comes later in the Access databases’ life and not all Access databases out there end the same way and to me that’s the beauty of Access – it enables companies to develop a line-of-business applications at far less risk, whether monetary or time & effort than if we opted to use Visual Studio.

SharePoint solves a different set of problems exactly the same way as Access does: it enables *non*-web-designers to build web pages quickly and without any specialized knowledge because it’s merely a part of their workflow and not their livelihood. Likewise, it enables workers to manage files without having to think about the organization of the files, backing up the files and how they would find the files again.

SharePoint helps the company save money by reducing the overall IT administration in contrast to traditional file sharing/networked hard drives, management of documents and so forth. Some people have suggested that SharePoint should be thought of as a platform providing easy-to-use building blocks, and suddenly the analogy from Access is much more accurate; we get to build web applications or develop a certain document management strategy at far less risk and expenses than if we built one from scratch.

In fact, when you examine the difference between Jet Replication and the Offline mode with SharePoint, it’s impressive in how simple it is. All you have to do is just use SharePoint as your data source and everything else about working offline and synchronizing is automatic. No manual configuration. No coding or additional installation. This truly demonstrate the raison d’être behind Access and SharePoint; solving IT problems for common people. And that’s just one of many integration points between Access and SharePoint.

SharePoint is not a relational database

Access and SharePoint are very similar when we look at how they can solve business problems. Given the similarity in the audience both programs address, it seems conceivable that they’d be peas in a pod, right? Well, here’s the other thing. Many professional Access consultants are actutely aware that SharePoint is anything but a true relational database system and they’re right. Even though SharePoint is powered by SQL Server, it introduces additional abstractions in the form of “lists” which are not similar to a SQL table and has some ramifications on how we can use lists in Access. That’ll be addressed in a future post.

SharePoint 2010 + Access 2010 = Instant Web Database!

The latest version of Access will allow you to publish your work to SharePoint 2010 with just one URL and a click. Gone are the days where you needed to upload files, configure security, configure the web server and countless other details. Granted, not all of the functionality of Access made it into this version of SharePoint’s Access Services, but we expect improved functionality in the years to come.

Sounds great! So why isn’t SharePoint used in small businesses?

Price is another point where SharePoint and Access diverge, SharePoint’s cost are much higher, both in terms of licensing fees and man hours required to support and maintain it. Microsoft has made some headway in this area by offering Office365 which essentially promises to bring SharePoint functionality to small businesses. Of course, there’s also AccessHosting which specializes in hosting Access web databases and we provide consultation for developmental work on web database that get hosted. This inevitable reality of moving services off the desktop and into “cloud” will also be examined in a future post.

My second post will discuss SharePoint lists in more detail.