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?
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.
Hi There!
Thank you so much for this helpful information on how sharepoint and access work together. I have been helping build an access database for my team ( I work for a community college) for the past couple years and have learned everything about how to build a database through youtube videos and helpful articles like this one. We have Access 2019 and our database lives in a shared O:drive that holds all of the information for our whole school district. Since we are working from home (and have to use VPN) our access to the DB is now dependent on if the VPN server is working that day, which has limited our team’s access to full using our database. As a result, we have been looking to see if we can move it to Sharepoint, so that everyone can access it without dealing with the VPN issues. I have been struggling to find a solution and every video I have come across (and tried) did not lead to a successful link when I went to our Sharepoint Page. I am trying my best to resolve this issue without having to get our IT department involved since they are pretty stretched thin at the moment. If you could offer any help or support, myself and my team would really appreciate it!
Hi,
I’d really appreciate some advice if possible please, I’m an avid “learner” but far from a programmer so bear with me, I’ve been picking up techniques and tips in this learning process for the last few yrs since first using access, I’m from an industrial automation background, but databases are beginning to draw me in! their fantastic.
I’ve been developing a database to track and manage changes to control systems at the many sites owned by the company I work for. I have been developing it in my own time for over a year, primarily to help me with my own workload but it is also planned to be used by my colleagues and possibly the site managers. It is at the stage where I could share with my colleagues and get the whole thing up and running, however, the inevitable move from shared network drives to sharepoint has landed at the company and has forced me to re-think a few functions I’ve built into the database…….sods law I know!
I’ve created copies of the database and saved to sharepoint lists etc, that all works fine and looks very slick, especially the re-synching etc
The functions I’m struggling to see the best route for are as follows.
1, I have a continuous form showing an overview of all the change requests in the system, I have a function where I can drag an email from outlook and drop it on one of the fields associated with the change request, I’m using the “On Dirty” event to create a directory for the change request on a network drive, and then save the email to this location. This can be done a number of times as new correspondence comes in, the emails are saved with the .msg file extension and are numbered in the order they are attached to the change. I then use the double click event of the same field on the continuous form to give the option of opening whichever email has been saved against the change. That’s probably a poor explanation but I’d basically like to know how I could save the emails to a sharepoint location instead of the network drive.
2. Something similar to 1. where on the same continuous form I have another field for the change request where on double clicking I’m opening a template word doc, populating a number of form fields and saving to another folder on the network shared drive, naming the folder with the change request ID and allowing the doc to be opened again from access. So again I’m struggling to understand how to replicate this function but save to sharepoint instead of network drive.
It’s all working grand with the network drive and was feeling quite happy with the work I’d done, then somewhat deflated to realise I’m going to have to re-develop some of key function.
If you can advise my best way forward it would be much appreciated.
Best Regards
Murray
I am using access 2016 and want to share my database with multiple users. What will be the chipper way to deploy my database in web.
Hi, I’m so happy to join this big team outhere. Acess is my favorite software, but now I have a problem because I want to switch a desktop database to a platform where multiples users are able to use this databases online at the same time entering datas and use queries ect…. In my I keep wonderwing wht is the best tool to use to do so. I’m thinking about Sharepoint and access services but I don’t have a web dateba.. So I want ti know what is the best platform to user and how
Access is more than fantastic
You must be really disappointed MS are getting rid of online databases. I know I am 🙁
Access database is more than fanatstic
I have Access 2010 desktop database on shared network folder. Database setting allows multiple users to open this DB at same time. (Shared is enabled). When I link a list from Sharepoint (2010) in this Access DB, this DB automatically goes in Exclusive mode (in current database option it still shows “Shared”) and only user can open this DB.
Any help would be highly appreciated. Thanks
Hi,
Is it possible to upload the runtime Access to Sharepoint?
MS Access should only be locally installed on 1 PC, but you can open it via Sharepoint/in the cloud?
Thanks,
Thomas
I created databases in Access 2013 with linked tables that reside in Sharepoint 3.0 so I cannot modify them in Access. Some of the fields in these tables have several separate entries (Maybe they are called Multivalue fields). The problem I have is that the Access pulls out only the last entry, but none of the previous ones. How can I have Access show all of the entries in those fields and not just the last one? I researched online extensively and cannot find the answer. Please help. Thank you.
i have access database 2007\2010 linked to SharePoint 2010. It works well.But when try to opening my access database in access 2013 and linked to SharePoint 2010 not opening,no respond , freeze. is it compatibility issue ? is work access 2013 link to SharePoint 2010.and also i have anther access database 2007\2010 linked to SQL server 2005. i can open in access 2013. why is not opening access database 2007\20010 in access 2013? please help me , THANK YOU
WUDIE
Wudie,
I’m not sure we can answer this properly in a blog comment – I would recommend that you reach out to us via phone or email and see if we can arrange something to help you in depth.
Ben, we are using Access 2013 & SharePoint 2010. i have a user that has created a Local Access 2013 database & is wanting to move it to SharePoint 2010. from everything I have seen this will not work.
If i am understanding everything i have found online, this would need to be created in access 2010 as an access web database & then it would work correct? SharePoint 2010 will not support an Access 2013 Access Web Apps correct?
Thanks for any input
We need to clarify exactly what we are discussing here.
When you say “move to SharePoint 2010” there are two ways to do this – by creating a 2010-style web database or by importing data into SharePoint site and then linking to them.
2010-style web database are no longer creatable in an Access 2013 (though you can still open and design in 2013; you just can’t create them using Access 2013).
But if all you want to do is store your data in SharePoint, you do not need a web database for that. You can use External Tools -> Export -> SharePoint to move your Access data into a SharePoint site and it will then create a linked tables to new SharePoint lists. This is supported in both 2010 and 2013 and this will work even on a SharePoint 2010 site.
Correct, to use 2013 web app, you need SharePoint 2013 + SQL Server 2012+. But that might not be necessary in your case. Furthermore, note that 2013 web app does not store data in SharePoint as 2010-style web database did.
I published Access db to sharepoint because I need offline editing. I have encountered several problems.
1. No OLE objects
2. Why doesn’t my filter work?
3. When you open the front end normally it says connected to sharepoint but the only way to choose work offline is by opening while holding shift (I don’t want the users in the developmnet part)
I would really appreciate any answers you can provide. By the way we are stuck with Access 2007 and Sharepoint 2010.
Bobby
Bobby,
I need more information on #1 and #2 to properly answer.
For #3, I think there’s a misconception here. That yellow bar “Connected to SharePoint” has nothing to do with working offline, nor will holding shift key down cause you to work offline. That yellow bar only indicates that the file you are working was published to a SharePoint library and if you make any design change, you would use the “Save to SharePoint” to update the master file saved there. But you don’t want to pester your users with this. Therefore, you need to clear the PublishURL property using VBA:
CurrentDb.Properties(“PublishURL”) = vbNullString
then that yellow bar will no longer appear.
But to actually work offline in Access 2007, you must manually toggle the “Work Offline” button on Database Tool tab of the ribbon.
We have done this backwards!
We have lists set up on SharePoint 2010 which form the basis of our data. Is it possible for me to use/import these lists into Access to get functionality of relational database?
Thank you
Ali, I’m not sure that’s a cause for worry. Relational integrity is also enforced at SharePoint even if you created the lists originally at the SharePoint. Now, mind, it’s easier in Access but if you already have data, it might make more sense to first create a blank list, set up it with the relational integrity you need, then import the data from original links missing relational integrity into the new lists.
You can use Access to do the work or you can do it with SharePoint Designer. I think Access is easier to do; probably just import all lists into Access local tables, then rebuild the relationships, then import it back to a new SharePoint site.
i am working on a database currently in access 2010, what the user wants is the ability to have the frontend on sharepoint and the backend on a server so users can not view each others data is this possible?
i am currently doing it the other way around. the users have a frontend application on access and use sharepoint lists to hold the table data that is linked to the application front end.
also how would i create a button click command to link to a picture online. or store a picture in the table directly.
thanks in advanced
SharePoint has a built-in feature where you can configure a list to restrict access to only data that they created which has the same effect. This can be used on an Access front-end that is linked to the SharePoint. I think this is most simplest and effective way to achieve the user partitioning you need. Note: those with “Manage List” permissions can always see everyone’s data, so ensure that you set up your sharepoint site’s membership accordingly.
If a picture is online, I would probably use a web browser to point to the image. But if it’s on a network table, you might not realize that since Access 2010, the Image control now accepts a path, not necessarily a picture stored in the table.
if I have an Access accde front end and shapepoint lists as the back end data source, from a security point, what is to prevent a user from accessing and potentially modifying sharepoint lists directly?
bob
You need to go to the Sharepoint site, and adjust the permissions so that they are given rights to insert/update/delete the list items only. That will prevent them from modifying the structure of the list. This is typically done by creating custom group with appropriate permission level and making sure everybody else is in that group and not one of default group that might have too much permssions.
In case where you need to ensure that edits are done via Access exclusively, there is nothing you can do to prevent that. You can at least hide the SharePoint lists/subsite so it’s not easy to find via navigation but if they know the URL, they still can go there because they have to have the permissions to edit the list items.
Hello, thanks alot your energy providing this free consulting. I work for a non profit conservation organisation in Cameroon and I will like to create a database in access then link this with sharepoint so my colleagues can search and and get access to important documents of the organisation without spending lots of time emailing and calling and expecting a coleague in another office to email this document to them. ANY idea about the best tools or platform to resolve such a problem will be grateful
Buh, you might want to look into using document library on SharePoint. This is best suited for managing documents and you can create workflows so that when a document is reviewed, it is automatically forwarded to the next responsible party.
You might be able to supplement this with Access using linked SharePoint list (yes, it can link to a document library just like a list) to aid in searching for documents. Do note, though, Access doesn’t make it easy to add documents; you have to do that in via SharePoint.
Hi, newbie here, and hoping for some help. I work for a large school district in the parent involvement department. I am working on creating an MS Access database for our whole team to use next year to track adult attendance, participant hours, etc. The problem is that half of our staff is located at school sites, so they are on a different server. Wondering what the best way would be to share the front-end database file with them that would link to my back-end file? (Basically, I would want them to enter the information simply through a form or simple interface), and our central office would be the ones to be able to change the info or design, run reports, etc. with the information the staff has input through the forms.) My understanding is that you put the back end on a shared network drive and the front ends should go on each user’s local drive, but not sure since we aren’t all on a shared network drive. I am thinking maybe SharePoint but I don’t believe it allows for split databases?? We are choosing to use Access based on people’s familiarity with the program already and the fact all of them already have MS Access on their machines.
I am still a database beginner… just getting an opportunity to finally work with it on a daily basis, so sorry if it’s a dumb question! Thanks in advance!
Deb – if your organization does in fact have Sharepoint, then that is probably your best approach. The concept of split database doesn’t really apply here when we talk of using SharePoint to hold our data because by definition, an Access file that links to SharePoint lists is already “split” — the Access file has no data in it – it all comes from SharePoint.
Splitting makes sense when we talk about data being stored in Access tables because Access tables can be in the same Access file with the forms and reports but as you already know, this isn’t very conductive to sharing data among people so we have to split an Access database. With SharePoint, you are now putting data on SharePoint server, so it’ll be split from day one.
I think if your users can access a SharePoint site dedicated to collecting and managing the data, this would be very good solution without getting too involved with the IT to provide support.
If you want to share an Access database with multiply yours, I would have your IT setup the database on a Server you and others can remote into with a common shared area. Then all users would just open the same MDB or ACCDB file. This should be simpler and less expensive than using sharepoint.
You can also follow this step-by-step to connect and sync Microsoft Access data queries with SharePoint, Office 365, Exchange and others codeless: http://www.layer2solutions.com/en/community/FAQs/cloud-connector/Pages/microsoft-access-sharepoint-integration.aspx
I am developing an Access DB 2007. I have to create forms and develop vba code to achieve some complex requirements. The App will have around 30 users and may grow to 50, the plan is to split the DB to front end and back-end. The back-end will sit on LAN share path.
My question here is, Is there an option where I can have my back-end on share point and have it connected to the front-end on user machines.
Does this improve the performance, or do you have a better suggestion.
we have Ms access 2007, share point 2010.
Hello Sirini
Your best bet with that many users is to use SQL Server to store your backend.
Kind regards
Juan
Is there a way to add an synchronize button to my Access database form 2007 (I’m using sharepoint 2007 and access 2007)
Would like to add a button on the form (access database) that will refresh/synchronize the chances with the connected list on sharepoint?
Hi Access Experts,
I am trying to create a support knowledge base (problems, solutions, faq’s, etc) using Sharepoint2007 and Access db. How do I design the tables in Access (2003 or 2007?) and publish onto SharePoint 2007 and is searchable and updatable. Please provide some tips as this is very urgent.
Paul
Paul,
If I understand your question correctly, you would actually want to create SharePoint Lists, then link those into your Access. I also encourage you to at least use Access 2007. Access 2003’s support is very very limited and 2007 is somehow better though 2010 is far better due to improvement in caching.
Access allows you to link to SharePoint lists and therefore use them like regular Access tables which would let you use data directly in your applicatiaon while having the same data available to SharePoint for searching.
If you need further assistance, contact us via our Contact Us.
My company is currently using Dropbox, and I d like to convince them to use sharepoint in the future, but in the interim, if I make a desktop database now, can I later convert it to a web app with sharepoint? If so, is this a simple process?
Kendall —
Basically, the only thing that you can move to Access 2013 web apps are the data. You would need to recreate your forms to show on the web. However, you can link your original Access client to the SQL Server and thus continue to use your client where it’s not necessary to use a web browser. The import process is quite simple.
For Access 2010 web databases, I basically recommend against creating a brand new one at this point, especially now that Access 2013 web apps is out and I think you’ll find it much easier to use Access 2013 web apps than Access 2010 web databases. Even so, the principles I described for 2013 still applies; you can have your client database link to the SharePoint hosting the data for your Access 2010 web database but you still have to basically create web forms if you want to use them in a web browser.
I hope that helps.
Please let me know how to automatically pass SharePoint password in Access table linked with SharePoint.
I believe that is handled by my corporate infrasture mostly behind the scenes. I think they use “Active Directory”. The end-user does have to enter credentials the first time and then occassionally afterwards, but mostly only when their password has been changed. Hope that helps.
Ben: Where have you “ben” all my life (sorry for the pun .. but that’s truly what I felt when I came across your articles)!
Such great information! I’m one of those people who started with the basics and have grown into developing and using some rather mature SP and Access integrations … but I’m having some performance issues. I wonder if the features of Access 2010 can be leveraged to help me?
As briefly as I can put it, I use SP lists to “gather” data from ~ 70 users, spread across 3 continents. Specially I have users operating within a single corporate infrastructure but residing in North America, China, and the UK.
Currently I simply “link” my 4 SP lists via Access. (one “primary” table, and 3 related tables.) Users utilize MS Access for the system interface, not SharePoint.
In MS Access I run local copies of each table, and use VBA coding to copy data from the local tables into the SP lists, one “primary” record at a time, along with its related records from the other 3 tables. This “export” happens at a certain predictable point in the work process, eseentially when the user is wrapping up this ‘record’.
Then I centrally “download” all the data daily, by doing the reverse. I do this to drive overall reporting of the data collected from around the globe.
98% of the time this works extremely well. But when its bad… its really really bad. To help me diagnose the problem, I’ve installed a ‘test connection’ routine on a few users’ machines. The average test time so far (to run a standard query) is < 10 seconds. However I get blips as high 100 or up to 2100 seconds. The same user who had the 2100 result also had "normal" results just moments before and after.
Any suggestions would be greatly appreciated!
Paul
|
I want to get a table created in a Word document (or created from an Access form) to synch into a SharePoint calendar.
Currently people type a weekly activity report (WAR) in Word. Part of that report contains information for people who are on travel. The report is posted to SharePoint and someone has to open each report, pull the travel info, and rekey into a PowerPoint slide for presentation at a weekly meeting. We want to automate the process so that once a person types the report the travel portion will automatically populate an excel spreadsheet in SharePoint that can then be used in the weekly staff meeting.
Is that possible? How do I do it? Then, I’d like to be able to put that info on a PowerPoint slide atomatically if possible.
Hi
I am working on a tool that requires user inputs in Excel GUI and using VBA these inputs are saved in Access database (using ADO connection). This requires the User to download the database on their machine. Is there a way that this database can be updated directly on SharePoint through Excel VBA or any other method. I don’t want User to download Access database on their systems.
Thanks !
Vineet Ahuja
I have an Access Database that has been published to Sharepoint. Everything looks great and it was EASY. My problem is that I need to sync my tasks to Outlook. My database application is using a task table that will not sync (button is greyed out) but I see a task1 table in my Sharepoint site that will sync. Is there any workaround for getting my database tasks to sync – maybe by getting them into the task1 table? Thanks for the website – GREAT information!
Cindy, it sounds like Tasks table is one of default site objects that is used by SharePoint for other things. You’re correct that you probably want to move them into Task1 (perhaps renaming it to make it clearer what it is supposed to contains in contrast to the default Tasks). An easy way to do this is to link to both Tasks and Tasks1 in your Access database then copy’n’paste rows from Tasks to Tasks1 table.
Hope that helps!
Thanks Ben
Going through your article, I was able to create the Access 2010 database linked to the Sharepoint List.
I have created the VB application FORM accessing the local database. It works fine.. After sometime, If the FBA authentication session of the sharepoint portal expires, If the FORM tires to update the database through VB coding. It automatically launch the FBA form to re authentication. But, after submitting the credentials.. It completely hangs.. No other option other than killing the session.. Any help..
Regards
Venkat
Venkat, just so I understand – you’re using a VB app to access Access database that contains linked SharePoint lists? If so, I have to ask – why not access SharePoint lists directly using web services in your VB app? The other thing I would look at is instantiating Access.Application object as it’s possible that the authentication happens within Access object model, rather than in DAO/ADO object model.
Thanks Ben
I have tried to create instance of the Access application in Excel VBA, And authentication works fine.. It fails to reconnect when it gets disconnected to the sharepoint list due to connection interruption.
When the Access Database linked to Sharepoint list disconnects, Only way I was able to resync is opening the Access Application and reconnect the Tables. So there any way to handle this in the VBA program.
Thanks
Venkat
Sorry, Venkat but there is no direct programmatic approach to force re-sync. You see, when 2010 loses connection, it will automatically go into disconnected mode. When it later detects the connection has been restored, it determines whether there has been any changes made to the SharePoint lists. If there are none, then it will automatically re-connect without prompting. But if it finds that there were any changes, even if it’s only one row, it will not reconnect and wait for user to click Synchronize.
You have two possible approaches:
1) If you have access to Access web services and can publish a web database, you could use DoCmd.Runcommand acCmdSyncWebApplication which has the side effect of prompting synchronization and thus restoring the connection. However this method also ‘close’ and ‘open’ your database.
2) Instead of using 2010 caching, you can set your Access to use 2007-style caching. 2007-style caching allow you to manually disconnect / connect via another RunCommand (off the head, I think it’s acCmdToggleOfflineMode but not sure). However, you lose the performance benefit with 2010 caching. Furthermore, it cannot handle dropped connection as gracefully – you must manually toggle offline then do your work then toggle online to move the whole thing in a single batch rather than stay connected the whole time.
It’s possible that it may be better to use a temporary Access table for all your editing, then when your user is ready to commit, copy the content into the SharePoint List so it can be worked as single transaction, minimizing problems with connectivity. I hope that helps.
Thanks Ben
I can understand the limitations in the technology as of today.. As you suggested to use the local table and commit to the SharePoint list at later point. Does that means, you commit directly to the sharepoint list accessing the Portal or to the linked SharePoint list in Access Database.
Is there anyway to handle the SharePoint portal list in VBA.
Regards
venkat
Vendat, well, you could do either.
I was actually originally committing to linked table that links to your SharePoint List in your Access table but if you want to go directly, you could use web service. But if you’re using VBA for this, I have to warn that VBA isn’t really the best language to work with SharePoint web service; it can get very involved quickly. If you are comfortable doing, you could considering building an Excel Add-In using Visual Studio where you can use VB.NET or C# to connect to your SharePoint list using web service references and it may be more easier to manage the authenication in this context.
I hope that helps.
Someone may have asked this already, I didn’t get through all the comments.
All of the information I have read mentions linking Sharepoint to Access, what if I want to link Access to a Sharepoint. I would like to create a Sharepoint form, and have it populate into an Access database.
Is this possible?
BTW, thanks for the laymans explanations.
I think it’s easier to just use a SharePoint list as your data source for your Sharepoint form, then have your Access database link to the same SharePoint list to get the data.
how can i create an executable for an access 2012 data base.
You can’t. What you can create is what we call accde. If you do a search online you will find plenty of help on the subject.
Thanks
Juan
Hi, interesting and well written article. We currently use Access 2007 in school and I have developed a couple of databases which are used successfully by a handful of users across our LAN.
I now have a new project to create a database to be used by users from several schools in the area. Each of them will be adding data for their school to the database and must not be able to see or use the data entered by other schools.
Do you think this will be possible using Access 2007 and Sharepoint lists serving as the backend? I have seen a technique for limiting the display of list items to only the user that created that item but in some schools several users from the same establishment will require access to their schools data.
Any input you could provide would be much appreciated!
Adam, good questions!
Yes, you certainly can provide additional filtering.
First, when we’re discussing security, it’s helpful to remember that the more complex your security is, the more work is required to read/write the data because adding one more unique permission means there is more computation for all users, whether they actually participate in this permission or not to pull that the data. So we want to keep this simple as possible.
You already spoke of showing only items to users that created that. I would prefer that route and I want to add that this applies only to regular users. Those who are granted Manage Lists can bypass this restriction so for users where you want to let them to read across and maybe even edit others data, you can assign them to a group where they are allowed to Manage Lists and therefore step outside that boundary. That of course implies high level of trust with those users.
The other alternative is to distribute an ACCDE file that differs among users by using Views. Views are filtered at server side and therefore you can have an ACCDE that links to different Views representing filters that are appropriate to their roles/levels. This is quite efficient way to segregate data. The downside, though, is that View are not a security construct so you’d be merely hiding the data so once again that implies a high level of trust with your users.
The last alternative where we actually secure is to use folders on the list. You would need to manually code your data entry form so that on the insertion, you insert into one of SharePoint defined field that describe which folder the list item should be placed into. You can then use SharePoint permissions to secure the folder and you can assign groups access (or deny them access) to each folders. Each users would be need to be member of appropriate group before they can access the folder. You then need to code your form to filter appropriate (you’d probably still want to use Views) and account for errors when users try to access restricted items.
So that’s your 3 major approaches you can pursue in segregating and possibly securing the data. I hope that helps.
Great blog…
Can you recommend any books that go in-depth with Access/Sharepoint development?
Hi Mark –
Thank you for the kind words about the blog!
As for books on Access + SharePoint; I’m aware of only one book that focuses on Access/SharePoint exclusively and that would be the book that I co-authored with George Hepworth and Tim Runcie. The book can be bought here:
http://store.advisicon.com/Microsoft-Access-in-a-SharePoint-World-p62.html
The other excellent source of information though not exclusively about Access & SharePoint is Access 2010 Inside & Out, by Jeff Conrad and John Viescas.
http://www.amazon.com/Microsoft-Access-2010-Inside-Out/dp/0735626855
I hope that helps.
I am not a programmer, and have taken a couple of access courses, and created a couple of 2007 databases, that apparently were impressive, and user friendly. Therefore, I have been asked to create a membership database, for a non-profit organization. However, I very much consider myself at a beginner level as far as being an access expert is concerned. They have one laptop which is used to track members and events, with MS Office 2010. They have not purchased Sharepoint. My questions are below, any help would be much appreciated…
1) I were to create a web database, would each user/person accessing the web database also need to have sharepoint?
2) I am aware that with an access sharepoint web database some features are not the same. One of my major concerns is that the membership coordinator wants a certificate emailed to the member when they check in for the conference event…so I would create a macro that sends the form from access upon clicking the check in box. Is this same thing possible with the web database?
3) I was thinking of trying to find a way to put the database in the cloud by putting it in a free site, like digital dropbox and encrypting it with a password. What are your thoughts?
Thanks a bunch! :0)
Thank you for coming here!
1) Not exactly. You only need single SharePoint instance for your users to access. Depending on how you are getting SharePoint (e.g. buying a on-premise license or signing up for a hosted account), you may also need to consider how your users can factor into it. Some providers may let you share a single login with multiple users whereas in other cases, you may buy a login per user. Two popular hosting options are AccessHosting.com and Office365.
Access Hosting (only company that specialize in Access web database hosting)
http://accesshosting.com/pricing.asp#sharept
Office 365 (you need to start at plan P1 or higher):
http://www.microsoft.com/en-us/office365/compare-plans.aspx#fbid=qLEptzY7Iex
2) There is indeed capability to send email but last time I looked, you can’t attach a file to the email; it’d be text-based email only. Different approaches may be needed here.
3) I’d suggest that setting up a SQL Server Express (free) is your best bet both in terms of stability and simplicity balanced. While dropbox perhaps may work for one user, it simply is nonstarter if you need to let more than one user use it at same time and you’d have to program something to check if someone is using their file already. A SQL Server backend, OTOH, only need to have a modest workstation, a good network connection and understanding of networking (so you can open a firewall port to allow the traffic though) but otherwise continue to use your Access database as it is. It’s very easy to upsize and requires no changes to your application’s funcitonality, though it is common after upsizing that optimization need to be made to work better with a distant data source. If you don’t want to administer a SQL Server, you can also opt to sign up for a SQL Server hosting. We offer a hosting service:
http://accessexperts.net/services/sql-server-hosting-for-access/
When you choose this route, you can keep your original Access database and simply distribute it to your users once it’s upsized.
I hope those links help you find what you’re looking for. Let us know if you have any more questions.
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.
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
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!
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!
Shannon, U can simply have your data in an Access file, while this file is stored on a SharePoint server, simultaneously you can use an other Access file, that has linked tables into the primary Access file. This way, the “DATA” of the primary Access file is updated through the interface of the secondary Access file (in which tables are linked.)
Just notice that the primary file could be reachable via a path similar to \SharaePointServerADocLibPrimary.accdb
Shannon,
you could simply have your data in an Access file, while this file is stored on a SharePoint server, simultaneously you can use an other Access file, that has linked tables into the primary Access file. This way, the “DATA” of the primary Access file is updated through the interface of the secondary Access file in which, the tables are linked.
Just make sure that the primary file is reachable via a path similar to \SharaePointServerADocLibPrimary.accdb
and also avoid storing data into the secondary (let’s say interface-only) Access database.
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.
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.
In order to build an access sharepoint webdatabase, do you have to have the Enterprise edition of sharepoint 2010? My company says that it has the standard edition but yet i was able to create the database and publish and it was working fine. Now it is not showing my lists, but it still shows the form that i built as a main tabbed menu. (I used the template Issues management dbase as a template.)
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
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