Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Introduction to Access Services

From: SharePoint 2010 New Features

Video: Introduction to Access Services

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.

Introduction to Access Services

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 am going to create a blank web database. I'm going to call it Purchases, and Create. It takes me into Access into my first table. So I am 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, and 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 am just going to leave this simple form as it is. I don't need that first ID section here, and of course, I have the usual layout tools within Access for messing around with this. I am going to save this as well, just save that as the 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 and this is really going to be the home page of the web site that we are 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 and 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 am going to save this as the Navigation Form. This won't automatically be the homepage of our new web site because it's considered just another form at this point. So I am 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, andI am 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 have 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. The deal is I want to take this database and push it up onto 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 am 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 website. First, I do have a button here called Compatibility Checker. It will tell me to close all of 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, and that 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 have just typed in is the address of the operation's team site. I do have full control over that site, so I do have the permission to create new sub-sites underneath it, and 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, and we are 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, the record is updated.

We have a little bit of JavaScript popping up at the top. Again, the idea is I don't have to have Access installed in order to be able to do this. This would work in IE and Firefox and Safari. I click on the Report tab and it takes us to the Access report that's being generated here, giving us our correct totaling with the new information that I just put in. Now, if I want to, I can go back into Access and actually open that up and it is considered as being synchronized to that data, so the actual Purchases table here will be updated with what I just entered in on the web site.

If I wanted to make any changes and 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. And 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 Project 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.

Show transcript

This video is part of

Image for SharePoint 2010 New Features
SharePoint 2010 New Features

25 video lessons · 10123 viewers

Simon Allardice
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed SharePoint 2010 New Features.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.