In this article you will learn the fundamentals of publishing an Access 2010 database application to the Web by using Windows Server 2008 R2 and SharePoint Server 2010.

If you have deployed SharePoint Server 2010 with the Enterprise license to your organization, then you can purchase the Office Web Apps (OWA) add-on to provide your SharePoint portal users with browser-based editions of Word, Excel, PowerPoint, and OneNote.

In case you wondered, “But what about Access?” don’t fret. Access Services is included with the SharePoint 2010 Enterprise license. The coolest thing about Access Services and OWA is that you can host Microsoft Office documents in your SharePoint portal and your users don’t have to have the full-blown Office applications installed on their computers; the only requirements on the client side are (a) a Web browser; and (b) access to the SharePoint portal.

By the end of this brief article you will understand how to publish Access 2010 databases to your SharePoint portal with a minimum of muss, fuss, or greasy aftertaste.

Back-end setup ^

The first thing we need to do to get Access Services on its feet is to ensure that the Access Services service application is installed in SharePoint, and that the related server service is running.

Log into SharePoint Central Administration and click Manage service applications from the Application Management category.

Access Services Sharepoint - SharePoint 2010 Central Administration

SharePoint 2010 Central Administration

If you performed a default installation of SharePoint Server, then you should find a service application entry for Access Services. Ensure that the runtime status is listed as Started.

Access Services Sharepoint - Verifying the Access Services Service Application

Verifying the Access Services Service Application

If for some reason you don’t have Access Services installed, you can open the New menu and select Access Services from the service application list.

Access Services Sharepoint - Adding Access Services to SharePoint 2010

Adding Access Services to SharePoint 2010

Next, from the Central Administration home page click Manage services on server from the System Settings group (the link is shown in Figure 1). Find Access Database Service, and ensure that its status is Started.

Access Services Sharepoint - Verifying the running state of Access Services

Verifying the running state of Access Services

The final back-end configuration step involves logging into your production SharePoint portal site and verifying that the SharePoint Server Enterprise Site Features are running.

From the front page of your portal, open the Site Actions menu and select Site Settings.

Access Services Sharepoint - The SharePoint 2010 Site Actions menu

The SharePoint 2010 Site Actions menu

From the Site Settings page, click Manage site features from the Site Actions group.

Access Services Sharepoint - The SharePoint 2010 Site Settings page

The SharePoint 2010 Site Settings page

Finally, in the feature list, location SharePoint Server Enterprise Site features and ensure that its status is Active.

Access Services Sharepoint - Verifying the SharePoint enterprise feature set

Verifying the SharePoint enterprise feature set

Now that we have SharePoint Server prepared to host an Access 2010 database application, let’s fire up the Access 2010 client and get to publishing!

Publishing a Web Database ^

When you start Access 2010 and navigate to the Backstage View, you’ll notice two types of database templates available: standard databases and Web databases. The chief difference between the two template types is that the Web database templates are pre-set for compatibility with Access Services.

You’ll find that there exists a number of limitations and “gotchas” with respect to Access 2010 database compatibility with SharePoint. Thus, you may be best off my creating your database by using a built-in or downloaded Web database template, and then migrating your production Access database data into the Web database shell.

Access Services Sharepoint - The Backstage view in Access 2010

The Backstage view in Access 2010

In this example, we created a Web database by using the built-in Contacts template.

Access Services Sharepoint - The Contacts Web database

The Contacts Web database

The good news, regardless of whether you want to publish your existing Access database or one of the built-in Web databases to SharePoint, is that Access 2010 includes a compatibility checker utility. Simply open your target database, navigate to the Backstage View, select Publish to Access Services under File Types, and then click Run Compatibility Checker.

Access Services Sharepoint - Publishing a Web database to Access Services

Publishing a Web database to Access Services

If your database does not pass the compatibility check (a highly likely scenario if you use one of your existing, non-Web databases), then the interface turns red and you can click Web Compatibility Issues to view a report of problems found.

Access Services Sharepoint - Failing the Web Compatibility check

Failing the Web Compatibility check

In my opinion, Access does a great job of listing the compatibility problems in the Web Compatibility Issues table it generates. Work to resolve the problems and rerun the Compatibility Checker until the file passes.

Access Services Sharepoint - The Web Compatibility report

The Web Compatibility report

Once your database is “greenlighted” by the compatibility checker tool, you will see the The database is compatible with the Web message and you can complete the rest of the form.

Provide the server Uniform Resource Locator (URL) to your target SharePoint portal, and give a site name. Be sure not to include spaces in your site name; Access Services will host your database in a subsite of the given SharePoint portal.

Access Services Sharepoint - Publishing to SharePoint 2010

Publishing to SharePoint 2010

If all goes well during the publication process, you will see the Publish Succeeded dialog box, as shown in Figure 14. Click the indicated URL to visit the new Access Services subsite.

Access Services Sharepoint - Confirmation of Access Services publication

Figure 14: Confirmation of Access Services publication

Using the Access Database in SharePoint

The hard work you exerted earlier in the process in forcing your database to pass the compatibility checker should pay off handsomely when you observe how faithfully Access Services renders your database in a Web browser. It’s pretty impressive stuff, for sure.

Access Services Sharepoint - The Access Services Web site

The Access Services Web site

The Access Services site template functions a bit differently from most of the SharePoint 2010 site templates. Instead of a Site Actions menu, we have an Options menu that we can use to customize site behavior, edit the underlying source file, or navigate up to the parent site.

Access Services Sharepoint - Access Services Web site options

Access Services Web site options

Conclusion ^

At this point you have the fundamentals under your belt such that you should be able to verify the running state of Access Services in SharePoint 2010 and publish an Access 2010 database to your portal. Please be sure to leave your questions and/or observations in the comments area of this post; I’m happy to help.

For further study ^

10 Comments
  1. Rob 10 years ago

    Thanks! You have been the closest to get me to the working point so far. I have had so many errors from other sources and my own attempts, but this time I see it go most of the way through the process, only to give me an error at the end.

    "An error occurred while initializing the Access Service database."

    Not finding anything online about the error. Any suggestions? So close to getting this to work!

  2. Rob 10 years ago

    Oh, in addition:

    The Sites and Workspaces shows the DB name, but it returns a 404.

  3. Tim Warner 10 years ago

    You are welcome, Rob! I trust you validated the Access database before adding it to SharePoint? Have you tried accessing the Access site by using HTTP and not HTTPS? Are relevant service applications configured properly and running? -Tim

  4. bob 10 years ago

    Hi all.
    For your interest:
    I've just spent the last 2 weeks investigating methods to put a database online for use by offices in multiple countries. I don't have time to write something in PHP, can't find a decent page builder, and accounts that allow access from client programs into MySQL are few and far between. Using MS Access seemed an attractive idea. However, my experience is not favourable. Let me explain. As I see it, the job of MS Access in this context is simply to build a set of HTML/javascript pages as a front end to SQL Server. This is the equivalent of a set of PHP pages looking at a MySQL database. However, this is very far from what we've got here. To set up a development environment, I've had to:
    1) Install Windows Server 2008 R2 (R1 being even more awkward)
    2) Install SQL Server 2008 R2 (anything less requires a lot of guesswork with service packs, lengthening the process considerably when you have to reinstall everything multiple times, which you will almost certainly have to do)
    3) Configure the machine as a domain server
    4) Install Sharepoint
    5) install Office (trivial)

    Now, ordinarily, the above would be donkeywork. In this case, getting all this to work together has to be done very carefully, because when Access won't publish, the error thrown says specifically that Access services is not running properly, or the server cannot be found. After checking the server is alive, chasing down the cause is a waste of time, because this error message is used for (as far as I can tell) every time somthing goes wrong. (In my case, the request was sent out through the router when the machine name was used, overriding my hosts redirect.
    6) Most cryptically, there's a specific place in sharepoint that needs your external URL to be explicitly typed in, without which a browser will throw a 404 or a special error that says "An unexpected error has occurred." and then gives you a the main Sharepoint help index page. That, by the way is my favourite piece of useless stupidity in this whole sorry game.

    Then, after you think it's all working (which it won't be for long) you find that the one thing Access is supposed to do for you, and the reason you've been through all this rubbish, it can't do. It's web form design is pitiful. In theory, it should be able to do everything the client can do, except VBA. After all, it's talking to a bunch of tables, and it only needs to put pixels on a screen and react to mouse clicks. Any existing Access form that steers clear of VBA should be directly translatable. But no. You end up building awful, rudimentary screens again, from scratch.

    So this is what it takes if an individual wants to put his little database on the internet. I think Microsoft knows this is a joke. The biggest consumer/SME software company in the world can't arrange to make it possible to perform the most fundamental of data productivity tasks in a straight-forward way? Isn't it obvious that we want to make this kind of system? What we're trying to do is simple enough to describe. I want to login to my company database over the internet and be able to make changes. Instead we have all this rediculous heavyweight crap to go through, only to find the results are cripplingly limited. No I see why they developed Lightswitch (looks promising, but, like all version 1.0 systems, faulty to its core, and Silverlight-only).

    BTW, the above software wasn't cheap, so now I'm locked-in, so I'll have to persist.

    Reaining things that bother me:
    Why do users have to log in with domain accounts? They're remote users, so when they're logged in, they're not domain users. Rediculous.
    Why can't Sharepoint work reliably on simple port-forwarding into a VM on a domestic-level router? It can't be that hard - even Skype can do that, and the comms in Skype is probably much harder to implement.
    Why is there no coherent help for the simple task of making your Sharepoint Access Services database available remotely. With all the smily paperclip wizards that MS has inflicted on us over the years, surely we're owed a couple of useful ones by now.

    Final thought: A friend of mine was once falsely accused of fraud, and came to me quite worried. After some thought, I was able to simply tell him that his defence was simple - 'follow the money' A similar thing occurs to me now. Those in charge of IT technology make a lot more money while it stays difficult. I've been out of the business (C++ and networking) for a while now, and I'm more conscious that, in the end, IT systems are not some tricky game for IT developers and engineers to get rich off. In the end they have to get some real job done. My complaint here is that the real solution is somehow being kept from us. The task of getting databaes on-line really should be easier and better than this.

    Thoughts??

  5. Rob 10 years ago

    @Tim: Thanks for the reply back. I think a lot of my issues were caused from using Windows Server 8. I have since went back down to 2008, and am not having any issues.

    It's sad, because I was really enjoying Server 8 too!

  6. Ed Wolf 10 years ago

    Is there a way to hide the menu options? I have administrative forms that other users use directly via URL to populate tables that other users then fill out the remaining portions. When they can open options and settings and then open other things it is a gap in security. Thank you for any advice you may have.

  7. Tim Warner 10 years ago

    Ed, because the Access database is its own subsite, you should be able to customize site permissions such that those admin controls are not visible to end-users. -Tim

  8. Arnel Tolentino 10 years ago

    Hi Tim,
    We are also having this error “An error occurred while initializing the Access Service database.” same as Rob. As I read the other post here, I read that this case is seems to be solve by backing down to Windows Server 2008 but we are already on Windows Server 2008 R2 and still having this issue. Also, we have just recently followed your helpful post here on how to do this properly but things doesn’t change a bit. Any idea what could be the reason for this? If you need more details about our environment settings, please let me know.
    Thanks,

  9. Peter Hollo 8 years ago

    “An error occurred while initializing the Access Service database.”

    Symptoms:

    New Access website is created, but not synchronized with the content.
    Even the Windows Application log on the server contains an event of the successful site creation.
    Then the Access sends the message above and the site doesn't exist on the given URL where the publishing was tried.

    While monitored in network traffic, it is visible that the creation is successful but the synchronization is not, then the site is deleted in result of this falure (all done in SOAP calls for lists.asmx of the site)

    Resolution:

    Microsoft Sync Framework is not installed on the Sharepoint Server that holds the Access Service.

    Solution:

    Download and install the needed version of the Microsoft Synchronization Framework, and do an IISRESET.

  10. Pamela 5 years ago

    Timothy,
    I have a question for you. I have a published database but recently my company had a major reorganization and the URL of my published database had changed. Is there a way to update the URL to point to the new site?
    URL changed from https://xxxxxxx.com/sites/AA/ABC/SiteName - to - https://xxxxxxx.com/sites/ABC/SiteName
    I haven't found a solution for it and I really don't want to redo the configuration as some queries are very complicated.
    I would really appreciate any help in solving this issue.
    Thank you

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2021

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account