Note: This is the third part of a three part series, you can find part one here and part two here.
Authored by Ben Clothier
To continue the series on Access and SharePoint integration (part 1 and part 2), we’ll be looking at the latest kid on the SharePoint’s block; Access Services. Access Services is included with SharePoint Server 2010 and there are hosting solutions available, including AccessHosting.com and/or Office365 for those who do not want or need or cannot have a local SharePoint installation.
You are undoubtably aware that with Access Services, you can publish web databases. For this article, we’ll be looking at how it helps us solve our business problems. A web database is practically a different beast from a regular Access database (referred to in Microsoft literature as “client database”), and there are several considerations we have to give as to whether we actually need web database. So we’ll be examining the business needs to help us decide an optimal strategy for brining our Access applications forward.
First question: Do you need it in web browser or just the ability to use your application across large distances?
Access MVP Albert Kallal asks the excellent question and gives some rationale behind whether to choose a web database or the traditional solutions for enabling Access databases to be used across WAN. It’s important to consider that we’ve always had solutions for enabling distributed access using Access, chiefly using either one of two methods: 1) Using a server-based RDBMS or SharePoint as the backend, 2) Using Terminal Server/Citrix or similar technology. I should note there’s also the Jet replication but since it’s not under active development, this is not something we may want to use in new projects anyway. Nonetheless, either methods enable you to keep your investment in your Access application while enabling it to be used across WANs and both can be cheaper (both in terms of money and time) to implement compared to web-enabling your database. Also because it’s still a rich-client application, you have more flexibility in regards to development.
Second question: Do you need your entire application online or just a subset?
When you think about it carefully, I expect that the answer to the question is going to be almost invariably a subset. To illustrate, if we had an Access database that’s used to track employees’ records for the HR department, does it makes sense to put it online? With all of employees’ private personal and payroll data? Likely not. We DO want to have a web page where employees can log in and request time off on so and so days. We DO want to have some kind of separation in how we use our data. Employees should always only be able to view and edit their own data and never others. Only HR can do that.
And here’s the important point: The existence of Access Services enables us to add features to our existing Access application that was previously not possible. Back to the scenario of employee requesting time off — it’s not always practical and/or desirable to ask the employee download an Access application and if necessary Access runtime also in order to be able to request time off. The download requirement also means they can’t request time off anywhere but their workstation.
For this reason, I do believe that a “hybrid application” is the best approach we may want to take toward enabling our application on the web. I’ll talk more about the hybrid applications later.
Third question: Who’s your audience? What will they do? What are your security requirements for those users?
Access Services currently requires some kind of login even though SharePoint itself supports anonymous access, thus ruling out the possibility of having a public-facing web database. For example, If you’re thinking about building surveys that should be available to any anonymous users to fill out, you’re going to have to look elsewhere, (e.g. SurveyMonkey.com).
What about different database roles? If it’s being used only by employees and hosted internally it’s a fairly simple matter but non-employees, (vendors, contractors and customers), can get more complicated. When you use a hosted solution on the web than there is no way to distinguish between an employee and a non-employee. You would have to require a separate login for each user to your database to ensue they are assigned to correct set of permissions. A mix of these groups of users will require an approach that will allow you to segment data security. That approach may involve creating a hybrid solution where some users have Access Services and others a traditional Access application.
There are three general security approaches available to us:
1) Block non-privileged users from using a downloaded Access application.
This is a very simple and no nonsense approach. Because you control the web forms & reports, they can’t just circumvent the protections you have in place, neither would they have access to the sensitive code you use to manage the security. AccessHosting.com has a video tutorial showing how download can be blocked.
2) Create separate Access files
For scenarios where we need to use ACCDEs & Access client for some users, there is no reason why an existing client database can’t link to web tables (using SharePoint Lists) and be available to users via SharePoint’s Document Library for easy download. Though you can only have one web database file associated with one site, you have as many client databases as you need linking to the same site. By separating out the duties per-file & using SharePoint permissions to control access to the files, you can provide your users the best of both worlds. An extension of this approach is to create separate web databases accessible at different URLs. You could direct your vendors to http://mysite.com/vendors/ and your contractors to http://mysite.com/contractors while your employees uses a traditional Access client database that links to both sites’ data.
3) Use SharePoint permissions to manage access to the web tables
The approach allows for more granular control at expense of being more involved and possibly costing you some performance. If this is important enough, you may get better performance by denormalizing lists into multiple instances based on a common list template. I recommend managing permissions at the list level and not at list item levels, since you incur in higher overhead with the later. Using SharePoint permissions is much more complicated, consider it only if you have complex security requirements and are willing to invest time/money into it.
Also, note that SharePoint allows you to restrict editing of other users list items with a checkbox. Check it off if you just need a broad policy of allowing users to add and edit but not see & edit other users’ items.
Fourth question: Do you have plans for other features of SharePoint?
One common complaint with Access Services is the fact that you have to have SharePoint, whether installed locally or via a hosting company. Understandably, people were asking for web databases for long time but they may not have anticipated the SharePoint tie-in. Because SharePoint is a big investment in itself, it can be an issue. If you’re thinking about getting web databases and nothing more, you may be actually missing out. To help see what SharePoint can also do for us, let’s consider the following SharePoint strengths:
Do you have a need to track, manage and share large set of documents?
Document management is arguably SharePoint’s strong point and its Document Library offers many great features built-in. Versioning is a great example, suppose we have a set of templates that periodically need to be edited, going through group approval every time. If every person decides to save the template with a new name indicating the latest version, you quickly end up with a morass of versions. But if you place the document in the SharePoint document library with versioning enabled, you no longer you need to rename each draft. Everyone gets access to either the latest draft or the final at all times without confusion or uncertainty.
Do you have open ended processes that need to be tracked?
A natural offshoot of document management is tracking the process of a document, SharePoint provides a simple programming model from draft to final. Let’s say we have a need for writing contracts and a contract can contain several options that may be included or not and they also need to be modified by responsible parties then submitted to the other party. By using workflows, you can describe what happens when a step is completed, for example, you can design a workflow where a reviewer saves the document to the library, it then triggers an email to the supervisor notifying it’s ready for review, with a reminder in the next week. When the supervisor edits and decides it needs more editing, they save it back to the library as a draft which goes back to the reviewer, repeating the first step. When the supervisor is satisfied with the doc, he marks it as final which then forwards the document to the department head for final approval and by then it becomes the template. Workflows are a wonderful fit for review processes and provides great flexibility.
I’ve only touched on one aspect of SharePoint, and there’s others such as ability to self-administor a set of websites or searching documents & content within an organization. Bottom line, with SharePoint a database may not be the best solution. If you have any more questions or want to know more about SharePoint and Access, feel free to share and we’ll be more than happy to answer.
Thank you everyone for all your comments – it is great to read everyone’s posts for how they are using Access and SharePoint. I have an Access database that I have done for a non-profit organization which stores information on clients and how they utilize services (3-6 people entering data). Previously I was using a form that stored data in two related tables which worked great in Access, but I could not get it to work once I linked the tables in SharePoint. Thus I went back to one large table 227 columns and 3580 records currently. Now I am getting the “Property Value is too Large” error. I am stuck between a rock and a hard place as the Center has begun to depend on the database and I don’t know how to fix the problem other than to install a local computer to store the backend and return to the related tables instead of using SharePoint. I don’t see others having this issue as much as I have and using SharePoint is ideal as I can make modifications remotely. Is anyone else having this issue with related tables and SharePoint? Can those with greater experience than mine suggest the best path to get me out of my bind?
Hello. Thank you for your posts. I am an old Access user and in my organisation, they want to integrate our access project database with sharepoint online capabilities. The database is full of very complex queries with multiple subqueries, many written in SQL which, as far as I have understand, would require a desktop Access database to carry out. My question is: would I be able to synchronise the queries from a desktop access database (created from synch’d Sharepoint lists as tables) as Sharepoint lists so they would be updated and available to create online reports in Sharepoint?
I really appreciate your help.
Regards,
Fernando,
The only thing that is really synchronised with SharePoint is the raw data itself. The queries, being an Access object remains as a part of the Access file. They will continue to work on a linked SharePoint list (provided you don’t change the name), however.
Note that performance of queries may change when you change from a local Access table as source of query to a linked SharePoint list. One way to make it less a problem is to open all linked SharePoint lists before you run queries. Doing so “warms” the cache and thus ensure that you have data already in your file so that your queries runs fast because it’s working with local data rather than waiting on SharePoint to return the data.
I created a 2010 web database and published it to sharepoint online through office 365 and it is very slow. Do you have any advice or do i need to look for other options? Would 2013 app be faster using office 365? I would also really like my reports to work through the browser and i saw that accesshosting.com can do that but for quite a bit more expensive than office 365. I have about 4 users who will need access to the database and i would rather not have to redistribute the front end (if i would split it) as we do not have a network. what are my options?
what a wonderful report. I am using 365 forms to create an asset management system for our school. I am now hoping that in using 365 with access that I will be able to query the id field of a table, in order to update the asset number. Unfortunately I have been unable to do this in 365 forms, even using workflows, do you have any suggestions for this in Access 2013? What I am wanting to do is to look at the form id field, and then add 1000 to it – showing the updated amount in the Asset number field and then update the barcode field with that number, adding a *at the beginning and a * at the end thus id of 1 would show as *1001* in barcode and 1001 in Asset number.
Great info in all the three parts! Had a question on using linking documents in SharePoint document library into Access. Typically I would like to attach documents in Access that evetually must end up in a SharePoint document library, how might I approach this?
We covered that in the book, “Microsoft Access in SharePoint World”, sold here:
http://store.advisicon.com/products/microsoft-access-in-a-sharepoint-world
The gist is that you use a web browser, customize the upload form so that you can then upload the documents via the web browser using the SharePoint’s form then associate it with an Access database record. This is necessary because while you can link a document library, you cannot add new record to a document library; you can only update certain fields, so to manage documents in a library, you need to use SharePoint’s forms for this. Using web browser control in your Access application can help streamline the experience so your user don’t have to go to a real browser to do those things. I hope that helps.
Ah, yes, I think that would work well! I need to re-write some of my “sharing” and retrieval code for that purpose, but not much. Very good. THANK YOU!! By the way, I have had my company order your book. I think I need to learn more about MS Access and SharePoint and how they work together. I can program myself silly in MS Access, but I need to broaden my horizons a bit! 🙂 Thank you again!
afaista,
Excellent! I’m so glad this helped and I do certainly hope the book will be of further help. Best of luck with your endeavors! Additionally, if you have any feedback on the book or questions, feel free to post back.
Thank you!
Ben
Well, yes, sort of. The two back end files are for retrieval purposes only, like libraries. User’s can retrieve data into a local back end file, or post data from their local to the shared location. They do not do any work, so to say, in those locations. But, as you said, that may not be the best way to go. These folks do not always have internet access, so running the client off that is not feasible. They only connect to those back end files when they need to retrieve or post, then the link is terminated as soon as it is done. They can never, ever, post the same data, but they can attempt to retrieve the same data. We are only talking a few users, 10 or less. So, we can use our network locations, but we have consultants who are not part of our intranet so I was hoping I could use a SharePoint site they could get to for the same purpose. Maybe not the way to go though, hmmm. I do not know much about these other services, I am an Access programmer who has always done stuff for small in-house groups.
Well, here’s what I think –
If you are ok with working in “disconnected” mode, then you could put your 2 files on a SharePoint document library. You can then map them to a network drive or use \xxxyyymybackend.accdb notation to first copy the file into a temporary location on the local hard drive, perform the work, then put it back. If you wanted to get fancy and assure only one person can work with it at a time, you could have the document library use check in/out so that only one person has exclusive editing rights in the duration.
That may work well enough for your purposes?
This is great information! Thank you! I am just learning this also, although I’ve been coding in Access for much longer than I care to share. 🙂
Question, I have a client based app that has 5 back end files. I’d like to host two of those on SharePoint, and link to them. My code takes care of permissions within the app. I password these back end files (yea, not great, but better than nothing) for a bit of security on the structure.
So, can you point me to a tutorial on exactly how to do this? Or is it more simple than I think it is? It is just placing the files on SharePoint and using the same Access code that I have to link the files? It already links across the work intranet, i.e. //networkname/folder1/folder2/, will this code work?
Thank you!! I’ve searched for information, and your site is the best I’ve come across!
Alfaista –
It sounds like you want to use your Access backend located on network location? If so, then I strongly recommend against it. Simply put, Access wasn’t designed to work across internet and due to how it works, may be suspect to corruption and other problems. For in-detail explanation, I refer you to http://www.kallal.ca/Wan/Wans.html
If you want to use your Access front-end across internet, then your options boil to the following:
1) upsize your backend to SQL Server or one of any other DBSMS and link your Access frontend to those tables instead
2) use Access web database
3) use Terminal Services (or one of similar products such as LogMeIn, Citrix, EQLData and few more)
Note that we offer SQL hosting and coaching on upsizing so if this is something you think best fit your needs contact us.
Thanks for reading and for the kind words!
I’m not sure if I’m knowledgeable enough to ask my question in clear terms. My goal is to have an Access front end on a website, accessible from anywhere, and a back end using Access or, preferably, MySQL. After reading your articles and others, I’m thinking I may need an alternative to a SharePoint solution. Are there any resources available that may guide me into how to use an Access front end on the web without using SharePoint? Thanks very much.
Foch –
You certainly can use a hosted server + client Access instead of web client to provide more rich user experience. Have a read through those blog articles: http://accessexperts.net/blog/?s=SQL+Server&x=0&y=0
Though they are for SQL Server, you’ll find that many tips apply to MySQL, and if you want, you may want to look at this offer: http://accessexperts.net/blog/sql-hosting/
I hope those help provide some idea to what you can do with a hosted SQL database and well designed Access client application. Best of luck!
Hi There
Im new to coding. I have a access 2007 database that i would like to put over to web i tought about doing it in php and and sql but it would take to long. Then i found a blog that said that you could move your database over to Sharepoint. So i checked out a few youtube movies and i quit like it so i downloaded WSS 3.0 and installed it. But i dont know where to start to get it to publish to WSS i have created a new web application that contains nothing in it used the
path and pasted it into access to publish but no luck it could not connect to the path. So what i want to ask is is it worth the trouble learning sharepoint to move my database over to wss or must i just rewrite the whole database to be able to work on php and sql.
Thank you
Albert,
It all depends what your comfortable with, I am much more at ease with Access so I always use Access and SQL Server in the cloud. You can purchase a subscription to SQL Server at AccessHosting.com
Thanks
Juan
Great articles, and thanks very much. Are you aware of any commercial or freeware solution which allows you to integrate with / extract data from SP lists in a normalised fashion, just to extract data?
I’m aware of Axioworks, but that requires the Windows User Account Control to be disabled.
Or do you reckon web database and Access / SQL Server is the way to go?
Personally, I don’t think a 3rd party tool is needed for ETL of SharePoint lists since you can use Access for this job. Note that you do not need to use a web database. You simply can just use a traditional Access database, create new linked tables that point to any SharePoint lists on any site within your SharePoint farm. From there, you can write Access queries to extract and transform the SharePoint lists’ content into your normalized tables, whether it’s a native Access table or a SQL Server linked table.
Furthermore, if you have SSIS, and you want the data to end up in SQL Server tables, there is also a add-in that you can use with SSIS for direct ETL of SharePoint data into SQL Server:
http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
Best of luck!
Over many years I have written an access application to track all kinds of compliance and process data for my plant. Each parameter is really a daily value, so I just created a table with primary key as the day(date/time format) and based all queries, forms, etc. using that. I simply preloaded the table with 15 years of records. Well it ran out of limit so created another one with another 256 available fields and loaded it with 15 years of records and made it one to one on first table and did simple join in all queries, etc. Everything works now as it has for many years. But I have come to think that this is an incredibly useful app that could be used for literally thousands of similar plants all over the world. And with sharepoint, it appears that there is now a clean way of not only installing them and keeping them updated, but providing a relatively easy way of for the user to be able have reliable backup, be able to use web to enter and update data, and always be synchronized with desktop Access to do most of the work. So my idea would be to sell the application with proviso that they had a sharepoint site (365) and Office 2010 installed. What are the downsides of this idea. Understand, I am much more of a home taught Access programmer, but know what I am doing when it comes to plant operation.
Hi Tommy,
Taking your Access app nationwide will present many challenges for a professional developer, more so for someone with your experience. My best advise would be to partner up with a firm that can help you get the app online. They would most likely wish to see some sort of study on the viability of the concept.
Kind Regards,
Juan
I use SharePoint 2010 lists in a normalized, relational structure. In SP 2007 and Access 2007 I built an app that links to and downloads from these SP lists. In SP 2010 and Access 2010, I’m excited to see the web database but seem not to be able to use existing linked SP lists in any of the queries or reports. Am I missing something?
Adam
Its not designed to work that way. if you need a web solution you may wish to consider a sql on the web database with Access.
As an addendum – be aware that Access web database allow us to store both web object and client objects, thus creating a hybrid program. So while you can use linked SP lists in your web database IF you are running Access client, but that does not mean you can use it in web browser. To be used in web browser, you must use the web tables, which are essentially SP lists on the same site. SP lists hosted on a different sites (even if it’s in the same SharePoint farm) are not accessible via the web database (inside web browser). One workaround is to use webbrowser control and a data view web part. I hope that clarify, Adam.
Can a desktop version of access 2010 look the same in sharepoint 2010?
In otherwords, if the database has a dashboard, can this be present on the sharepoint, rather than just lists? Can it look just like access.
If you use a web database and publish it, then you will have forms/reports in both web browser and in Access and thus have identical layout. You cannot use desktop version and have it appear on the SharePoint; you must create a web database with web forms/reports. Note that web database allows you to create desktop version of forms/reports (they won’t have a globe icon on the form/report in the navigation pane) so be sure to not use client forms as they cannot be used in a web browser.
I hope that answers your question.
Ben,
Great article.
Here’s something I did just to see how it can all work together:
http://wsantosf.wordpress.com/2010/12/02/creating-a-project-management-board-kanban-style-with-access-services-in-sharepoint-and-reporting-services/
Love the use of new WebBrowser control to integrate the web application with sources outside, like your Kanban report. The really cool thing about the WebBrowser control is that it can be also manipulated via web macros to change URL dynamically so as long the web application has the url somewhere in the tables, we can use information to concatenate and build a URL requesting what we need.
Thanks for sharing, Wanderlei!
Ben,
You’re right – the webbrowser is a great way to integrate things seamlessly. I have an outlook custom panel that renders a report using the email sender’s address as a filter. Then we add a link to sharepoint’s enterprise search, and all this still within outlook. All with minimal coding.
Love it, Wanderlai! Document Information Panel is pretty a nice way of controlling data for the rest of Office and can be used to synchronize with web databases.
Hi Ben, you mention this video on preventing users from downloading the web database to Access 2010 and thereby gaining design access. I have done everything in this video and I cannot get it to block downloading to a No Native Access user. I am using the Office 365 30-day trial and creating a mobile extension of my current desktop Access 2010 database. It is essential that I be able to lock down the web database, and this is really frustrating me. I must be missing something, because when the user with No Native Access permissions tries to download into Access 2010, there is no challenge for credentials as in the video. I am new to Sharepoint 2010 and suspect I don’t have things configured correctly, or at least not in the same way as the aspecthosting.com demo.
Any assistance would be greatly appreciated.
Sambodh
Sambodh
I’m so sorry to hear about that.
Few possible ideas:
1) are you by any chances downloading this from the same computer where you’ve saved & trusted SharePoint connection? Maybe it’s actually getting re-used and hence you don’t get challenged.
2) You can verify that you’re an user that can’t download by using CurrentWebUser() function which you could then display on your startup form. That would validate that you’re logged in the user you expect to be.
3) I recall that you were able to download the file but you couldn’t actually open it. Is that the case?
Let me if any of those works or not.
Thanks for reading & asking!
Ben
Hi Ben.
I have seen your blog, I am really learning a lot with each post. About Access and Sharepoint, I would like to ask you:
How can I connect from Access to Sharepoint using current access logged User? I mean, I would like to bypass the login window, using CurrentUser() and reading his/her pass from an encrypted local table. Is there any way to “launch” a connection given the Server’s URL, username and pass?
Thank you
Pete
Hi Pete –
This is a good question, so I decided to write up an article about that subject. I hope it’ll help answer your question — let us know.
Ben
I have had this same problem. Can’t get the unique permission level to work; users who are assigned this permission can still open the database in Access 2010. Did you ever solve the problem, Sambodh?
Sambodh & Lois —
Just to be super duper sure – is this a matter of users needing to log out & log in? It’s possible that if they were already logged in or had their credentials saved, their old permission sets may still be in effect and thus there is no challenge.
In our case, the person who was testing for us was never assigned any permission other than the special permission level described in the video. So the credentials didn’t ever change. They always were, and still are, the special permission level described in the video.
Sambodh/Lois,
I’ve not forgotten about you two — I’m trying to get hard answer on the problems you’re describing.
Ben —
We finally discovered the cause of our problem. When the administrator of our parent site set up the sub-site to which we published our web database, she thought she had correctly set up our site so that it did not inherit permissions from the parent. However, there seems to have been some flaw in that procedure, and the reason our testers were able to open our database with Access2010 from the browser is because they had permissions on the parent site. An administrator of the university’s SharePoint server was able to correct the problem, and now the special limited permission is working just the way it should. So — as usual — it was “user error” that was the culprit. Thanks for spending some time on this, though. It’s much appreciated.
— Lois
I really appreciate your articles. We are definitely on the same page when it comes to the Access/SQL Server development world. On your “subset” Access Services scenario…I have found this to be a real world need but the show-stopper seems to be that the rest of the applications data lives in SQL Server. Have you come up with some synchronization SQLServerSharepoint to address this or are you just leaving it all in Sharepoint when the Access Services is part of the scope?
Wonderful question, tsmith60.
I’m of the mind that when we have an established RDBMS backend, we’re better off developing some kind of synchronization than moving all data into SharePoint. There’s generally two approaches and it depends on your needs.
If you are content with getting your web data synchronized at an interval (say, by end of every day or maybe hourly for instance), you may be able to build an Access client database that contains linked tables to both RDBMS source and the SharePoint web tables and can run set of queries.
OTOH, if you’d want something more immediate, I wrote an article demonstrating how one can do this using SharePoint’s EventReceiver, immediately synchronzing with MySQL source and the article is available at Access Team Blog. The same technique can be used with SQL Server equally well.
I’ve developed using both methods and both serve their purposes very well. Admittedly, both methods require that you roll up your sleeves and get your elbow dirty, and with latter, may require you to write .NET code. At least we do have ways to make such synchronization possible. In meanwhile, I do encourage you to tell Access team via their blog what you think – I can’t promise that they’ll do exactly what you ask for but at least telling them is better than leaving them in dark about what we really want from them.
Of course, if there’s any additional questions or a need to get this done, do feel free to reach out to us and we can help with building a solution that meets your needs.