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

Sharing an Access database with Access Services

From: SharePoint 2010 Essential Training

Video: Sharing an Access database with 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.

Sharing an Access database with 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'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.

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 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.

Show transcript

This video is part of

Image for SharePoint 2010 Essential Training
SharePoint 2010 Essential Training

70 video lessons · 50123 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 1m 16s
    1. Welcome
      1m 16s
  2. 16m 34s
    1. What is SharePoint?
      8m 9s
    2. SharePoint roles
      2m 5s
    3. Accessing SharePoint
      4m 48s
    4. The SharePoint product line
      1m 32s
  3. 44m 55s
    1. What is a team site?
      2m 43s
    2. Navigating a team site
      9m 41s
    3. Using team site lists and libraries
      11m 38s
    4. Editing the home page
      9m 31s
    5. Adding a Web Part
      6m 19s
    6. Deleting a Web Part
      5m 3s
  4. 10m 53s
    1. What is a Document Workspace?
      4m 2s
    2. Creating a Document Workspace
      4m 3s
    3. Deleting a Document Workspace
      2m 48s
  5. 6m 3s
    1. What is a Meeting Workspace?
      2m 7s
    2. Creating a Meeting Workspace
      2m 40s
    3. Deleting a Meeting Workspace
      1m 16s
  6. 36m 3s
    1. Exploring the available lists
      5m 30s
    2. Creating a custom list
      8m 44s
    3. Creating a custom view
      6m 43s
    4. Working with libraries
      6m 18s
    5. Using versioning and Check In/Check Out
      8m 48s
  7. 45m 55s
    1. SharePoint and Word
      6m 6s
    2. SharePoint and Outlook
      7m 38s
    3. SharePoint and Excel
      3m 54s
    4. SharePoint and Access
      2m 58s
    5. SharePoint and InfoPath
      11m 42s
    6. SharePoint and PowerPoint
      3m 46s
    7. SharePoint and Visio
      6m 20s
    8. Using SharePoint Workspace
      3m 31s
  8. 32m 8s
    1. What is a site collection?
      3m 56s
    2. Creating a site collection
      6m 35s
    3. Creating a new site
      6m 29s
    4. Customizing a site
      7m 47s
    5. Creating a site template
      7m 21s
  9. 13m 53s
    1. Understanding permissions
      3m 33s
    2. Adding a user to a site
      5m 14s
    3. Deleting a user from a site
      1m 39s
    4. Creating a new security group
      3m 27s
  10. 31m 54s
    1. Using out-of-the-box workflows
      11m 1s
    2. Creating your own workflows with SharePoint Designer
      15m 20s
    3. Creating your own workflows with Visio
      5m 33s
  11. 40m 36s
    1. Using site templates
      5m 49s
    2. Using the web content management features
      10m 40s
    3. Using master pages
      3m 37s
    4. Creating an Enterprise Wiki
      7m 14s
    5. Sharing an Access database with Access Services
      7m 19s
    6. Working with rich media
      5m 57s
  12. 53m 9s
    1. Managing documents and records
      3m 0s
    2. What are content types?
      4m 22s
    3. Creating a content type
      11m 30s
    4. What are document sets?
      2m 12s
    5. Creating document sets
      7m 49s
    6. Creating a Document Center
      4m 37s
    7. Creating a Record Center
      8m 25s
    8. Defining information management policy
      11m 14s
  13. 15m 42s
    1. Using personal and social features
      7m 28s
    2. Creating a SharePoint blog
      2m 48s
    3. Personalizing SharePoint with tags and notes
      5m 26s
  14. 21m 22s
    1. Searching in SharePoint
      4m 26s
    2. Creating a Search Center
      8m 4s
    3. Customizing Search with keywords
      3m 30s
    4. Customizing Search with scopes
      5m 22s
  15. 47m 18s
    1. Using Excel Services
      10m 12s
    2. Creating a Business Intelligence Center
      3m 5s
    3. Using PerformancePoint Services
      12m 3s
    4. Using status indicators
      8m 10s
    5. Using the Chart Web Parts
      6m 33s
    6. Using Business Connectivity Services (BCS)
      7m 15s
  16. 1m 3s
    1. Goodbye
      1m 3s

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 Essential Training.

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 preferences from 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.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

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.