SharePoint and Access: How do they fit together?
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 on 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?
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 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 SharePoint to. 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 requires 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 saves money by reducing the overall IT administration in contrast to traditional file sharing/networked hard drives, management of documents and so forth. Some people has suggested that SharePoint 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 addresses, it seems conceivable that they’d be peas in 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 were 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 brings 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.
19 Comments
Thanks for the blog and the well written posting re:SharePoint and Access: How do they fit together?
So, how do they fit together? I am the non techie that you described who needs to get one database shared between 6 users, 3 in the same office each with a pc and 3 who are working from home.
Tom,
You could use SharePoint’s Access web services, but then that would limit what you can do. Another alternative is to use SQL Server hosting in the cloud, but you are new to Access so that may be to daunting. If considering the former please checkout AccessHosting.com. If you need help with the latter give us a call to discuss.
Thanks
Juan
This is what I want!. Finally I understood. I am a Sociologist so you can imagine that this is not my field. I just need to 3 forms for my customers ands suppliers registration, and then connect it to my local data base. Therefore the publics forms are only to collect information. I really appreciate if you could write or send me and link for the cost of the service. Thanks!
information about the cost for these services.
Hi Maria,
Great to hear from you! I have sent you an email with my contact info, please call me to discuss how we can help you further.
Kind Regards,
Juan
This may be similar to a prior question. But I need to create a database that will hold implemented projects and the details behind each project. Not many users in my company use Access, but I was wondering if my answer solution would be to: create the database myself (I have Access 2007); create forms for other people to fill out with their idea information; then upload the Access database to our intranet/Sharepoint site; and once there all those within the company that we grant access to will be able to input their information themselves? Does that sound reasonable or am I missing something?
Thanks & I appreciate your blog!
I think we need to more precise about how we plan the infrastructure, especially with different version.
With Access 2007 / Sharepoint 2007 (aka WSS 3.0), the only thing you can do is the following:
1) Create a front-end client with linked SharePoint lists
2) Upload the file and store it in a document library for easy download/update. Users then download the file off the document library and open it in Access. When the file has been uploaded this way, there is a message bar to save the change and thus automatically update the copy in the library.
Note that you can do either one or both as you choose.
With Access 2010 / SharePoint 2010 (specifically the Enterprise edition), you can do the above in addition to:
3) Create a web database with web forms & reports & macros and make it available for people with web browser to use.
4) Create a hybrid web database which also contain client objects. Unlike #2, the changes to the objects are immediately synchronized and do not need to replace the file entirely.
I hope those help illustrate the choices available to you for solving the deployment question. Let me know if I can provide more details.
I am using Sharepoint 2007 and Access 2007. Are you saying that in the 2007 version, it is neccessary to save the Access database to my computer to update it and then upload the updated file to sharepoint?
Great question, Shannon!
No, it is not necessary to do so. There are different ways to interact with SharePoint, even with 2007.
You can opt to use SharePoint as a place to store your data, in which case you just link SharePoint lists in your Access database and use it like a regular Access table.
Likewise, you can opt to use SharePoint as a way to distribute your Access database by uploading it to a Document Library and emailing everyone else the link to the Document Library’s URL. When you use that feature, you will see changes in how Access behave such as displaying “Save to SharePoint Server”, allowing you to quickly upload any changes you make to your Access database. That may not be desirable when you’re building Access applications for other to run in which case, you would suppress the dialog by removing “PublishURL” property.
Either can be used in tandem or not, so you have choices. I hope this helps clarifies what you can do with SharePoint.
I appreciate your reply.
I work for a jewelry store and we use an Access database to maintain our customer information (history of purchases, repairs that we have done, appraisals, mailing addresses, etc.). We add/edit our database using forms. When I open the database from sharepoint, I click on the database and “Edit in Access.” The database then opens in Access on my computer, but in a Read Only format. In order to edit it, it requires that i “Save As”. We need to be able to edit the database without doing a Save As and then uploading it back to Sharepoint because it takes too long to download then upload (15,080 KB) and doing so creates version control issues for us. So…is it possible to edit the database Access 2007 using sharepoint 2007 without the downloading and uploading of the database or should I explore the Access Hosting you mentioned in a prior post?
Thank you in advance for your response,
Shannon
Clarify – are your data actually saved in SharePoint Lists or are you using Access tables to hold the data along with the forms you have?
To tell the difference, a linked SharePoint list would have a orange table icon while regular Access table would have blue/white table icon. If it’s the latter, that may be why it’s so big because you’re moving both forms and data from one place to other. Having it in a SharePoint Lists would make your Access file smaller and thus easier to move around.
As for being in Read Only — do you have permissions to write to the library where your Access file is stored in? Especially to edit an existing file?
If you’re still stuck, please feel free to contact us and we can provide a quote for the assistance. Thank you!
I am looking at uploading an Access program with linked Sharepoint lists, to Sharepoint itself so that multiple users would be able to use and update the Access program. However, if there is a need to download the copy before publishing the changes to Sharepoint, there will be a risk of changes being overwritten by different users, right? Is there a way to curb this?
Yes, you would use SharePoint permissions to prevent design changes by those who shouldn’t be making such changes. If you use the same technique that AccessHosting demonstrated in blocking the users from downloading an Access, you can disable the Modify Application permission which if I recall correctly will prevent users from publishing their changes to the copy saved on the SharePoint.
An alternative is to instead of publishing, save an ACCDE file and upload to a Document Library. 2010-style publishing does not allow you to use ACCDE, and as AccessHosting’s video show, you can effectively secure a web database in a web browser. In client Access, you can only prevent publishing from happening but not necessarily users making changes (which then get lost next time they re-sync, of course).
Link to video: http://www.youtube.com/watch?v=K2sgVzFlmJE&list=UUuDvDaG2xR3-tLaOrt49stQ&index=5&feature=plcp
Let me know if that helped.
I have been exploring for a bit for any high quality articles or blog posts on this sort of space . Exploring in Yahoo I ultimately stumbled upon this site. Studying this info So i am glad to show that I have a very good uncanny feeling I found out exactly what I needed. I such a lot unquestionably will make certain to don’t forget this website and provides it a look on a continuing basis.
Glad this was useful for you, Kyle. We’ll definitely continue to add content on SharePoint to this blog so stay tuned! Thanks for commenting!
Thanks for this article. Just a question or 2. I am creating a customer-information / sales forecast / actuals, etc database in Access 2007. This database will be uploaded to Sharepoint, will my users that does not have MS Access, be able to update the information directly in Sharepoint, or do I need to install Access onto their local computers ? Sharepoint is version 2010. Thanks
Good question.
If you’re storing your data in SharePoint lists, then you could just either create a Data View page or use the built-in view list page to view and modify the SharePoint list in a web browser without requiring an Access installation.
The alternative when you need to do more than simply viewing and doing some basic editing is to download the free Access runtime & install it on the computer so they can then run your ACCDB file. Note furthermore that if you have a full version of Access 2010, you could just distribute Access 2010 runtime (runtime is free & available for either 2007 and/or 2010 but you have to choose which version you want to develop for to minimize any potential headaches from cross version differences).
I hope that helps.
Ben,
Thanks for your response, this solves a lot of questions.
W
Is there any way to have two front ends for an Access 2010 DB that has been published to SharePoint 2010?
I would like to take advantage of the features publishing a DB to SharePoint gives… (edit without having to download, don’t need the applications loaded on every machine…) but want to limit the vulnerability these same features come with.
I have the same DB being used by Employees and Customers. I would like to limit the Customers ability to manipulate data in the DB but still need them to have contribute privileges for other areas/files in an “external” SharePoint site.
Is it possible to create different forms and queries for each group (on respective internal and external SharePoint sites) that all point to the same SharePoint lists (stored on the internal [no Customers]site) using the Access forms to limit what fields can be edited)?
By definition, there can be only one ACCDB published to a SharePoint site.
You say you don’t want to download Access file, so if we’re doing it through web browser exclusively, then I would suggest that you’ll be fine with just one ACCDB file. In the web browser, they have no way of circumventing the security you may put in the place. You can use CurrentWebUser(), CurrentWebUserGroups() and IsCurrentWebUserInGroup() functions to determine if your user is a client or an employee and show/hide the appropriate objects.
You can have a desktop Access file point to a SharePoint site (or multiple sites) and therefore have an application that allows more functionality for your internal uses that can reference the same lists used in a published Access web database while limiting your external users to only what is allowed in your web interface.
I hope that help illustrate the possibility.