Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
With Access Services in SharePoint 2010, you can create a database in Microsoft Access 2010 and publish it up into SharePoint. It will take your Access tables and turn them into SharePoint lists. It will take your Access macros and turn them into workflows. The best way to do it is to start off with a new Access database called a blank web database. If you have an existing Access database, you still can publish that up into SharePoint, but you might have to remove a few things that are incompatible.
So, I'm going to create a blank web database. I am going to call it Purchases and Create. It takes me into Access into my first table. So, I'm going to define a few entries here. I'll say the first one is a text field called Product Name. The next one is Currency for Price. The next one I'll say is a text field for Serial Number, and the next one is a Yes/No for whether it's activated.
I'll save this and just save it as Purchases. Well, I could directly enter information into the table. We usually have a form to work with, with Access. So, I'll hit my Create Ribbon and create a form based on this. I'm just going to leave this simple form as it is. I don't need that first ID section here. Of course, I have the usual Layout Tools within Access for messing around with this. I'm going to save this as well. Just save that as Purchases form. At this point, I'm not going to create macros, but I will create an example report, and just let it do the totaling that it would do, by default.
I'll save that one. Now, the one thing that I do have to do is create what's called a navigation form. This is really going to be the homepage of the website that we're going to make. Because we have to give the users ways of navigating between the form and the report, for example, we need a way that they can do that. They obviously won't get the usual Access pane to open up. So, we create a navigation form, and then we just simply drag-and-drop the elements that we want onto the form, in this case, the Purchases and the Report, which I'll just rename on the tab.
I'm going to save this as the Navigation Form. This won't automatically be the homepage of our new website, because it's considered just another form at this point. So, I'm going to go to my File menu and come down to my Options where I can nominate in my current database that the Web Display Form should be Navigation Form. It just means what's the first thing that we see when we open this up. I'm going to save this. Well, right now, this is a pretty typical Access database, so I'm going to open up one of these forms.
I'll open it up in Form View, just so we can enter in some example products. Let's say we purchased a PDF Maker for $199, and the serial number was ABC123, and it was activated. Fairly conventional Access stuff. I'm now on my second record if I want to do it. The deal is I want to take this database and push it up on to the web, because I want potentially dozens or hundreds of people to look at it without worrying about uploading my Access database to a shared network drive and do the people that I want to use this have the right version of Access, all of that kind of stuff.
I don't have to worry about that. I'm going to go to my File menu where in either the Info section or the Save & Publish section, I have an option here to Publish to Access Services. This is that part of SharePoint 2010 that will allow me to take this database and make it available as a website. First, I do have a button here called Compatibility Checker. It will tell me to close all the objects. Yes, that's fine. The database is compatible with the web. Now, if you had an existing Access database, you might run that Compatibility Checker, and it would tell you things were wrong.
For example, some of the column names that you had might be incompatible with SharePoint. It would give you some hints about what you can change. There are some rather obtuse error codes that you'll get. You just have to live with it unfortunately. I do have to give it the address of an existing SharePoint site. Because you can only create Access web databases as SharePoint sites as sub-sites. So, they do have to be under an existing site in an existing site collection.
So, the server URL that I've just typed in is the address of the Operations team site. I do have full control over that site, so I do have the permission to create new sub-sites underneath it. I'll call this new site Software Purchases, and then click Publish to Access Services. It will take a moment to do the conversion, taking our Access tables and converting them into SharePoint lists, and taking on macros if we had any, making them workflows, and taking off forms and turning them into web forms.
If it was a complex database, it might take a little while to do this full process, but I'm going to select this link that says it's successful. We're seeing the data is immediately there for PDF Maker on this first form here. I'm going to click the New Record button and put in something else. Let's call this Product X. It was $199 and the serial number was DEF432, and that was activated. Save that entry.
So, the actual Purchases table here will be updated with what I just entered in on the website. If I wanted to make any changes, add some new forms, change my navigation form, I can do that. If I go back to the File tab, I'll see that I have a rather large Sync All button that will allow me to push these changes from Access up to the server. While, obviously, your Access databases can get a whole lot more complex than this simple example, the process of moving them up to the web is pretty much the same. Also, understand that when you're creating a new sub-site from a regular SharePoint site, any of the site templates that you see that end in the words Web Database, like the Assets Web Database, Charitable Contributions, Contacts, Issues and Projects Web Database, are pretty much the same thing that we've just seen.
These are Access web databases. These are just five examples that are provided out-of-the-box by Microsoft. Making Access databases available to multiple people within an organization has always been a challenge, and this is a terrific way to do it with your own Access databases.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.