Monday, May 10, 2010

Access 2010 Web Databases: How Can I Put My Access Database On The Web?

(Note: The information below is applicable to Access 2010 web databases. It may not be correct for Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)


How Can I Put My Access Database On The Web?

One of the perennial questions in the Access newsgroups and web forums is "How can I put my Access database up on the web?" Until recently, the only answer was "Learn ASP.NET or PHP and re-program it."

With Access 2007, it became possible to upload your data to a SharePoint list in the "cloud", that is, a server somewhere up on the web. However, the actual application still had to be run in the Access client, either in Access itself or the Access Runtime.

But with Access 2010 and Access Services in SharePoint 2010, it is possible to also publish your application, (ie, forms, reports, queries, and macros) up to the web as well as the data. From there, the application itself can be run in a browser. So the new answer is: "Go to the File menu, choose 'Save and Publish' and then 'Publish to Access Services'."

Can it really be that simple? Well, no. Not really. The chances that an existing application of yours will simply upload to the web are relatively small. SharePoint Access Services has specific requirements both for your data and application design. Some of these requirements may be at odds with your existing development paradigm.

For instance, a SharePoint back-end does not have relationships per se, it cannot have a composite primary key, and cannot have multiple field indexes. Web database applications cannot have any VBA code, only macros (although the new macros in Access 2010 are more robust than previous versions), and forms must be designed in Layout View rather than Design View. This is not an exhaustive list, just a few examples. I'll discuss them in future posts where I can go into more depth (and as I discover them).

Of course, I needed a project to use, so I decided to take an existing application and make it a web application. I have a sample on my website (Books.mdb) which is fairly simple (just a home book inventory database) but also has all the elements of a robust application. As an application, it's not horribly complex, but it should be enough to give me a good workout. If you're interested, you can download the sample and see what I'm up against.

One last thing before I get started. If I am to create a web database application, I need a server running SharePoint 2010 with Access Services. Hosting services for the samples on this blog are provided free of charge by accesshosting.com.

So let's get started.

The place to start (as with any database application) is with the data. That's what I'll do next time in Access 2010 Web Databases:Web Compatibility.
.

2 comments:

Nero M. Agustin said...

Hi Roger,
When I’m trying to publish an access web database to my SharePoint Server 2010 I always got this error. “An error occurred while initializing the Access Services database.” I make sure that Access Services is running on my SharePoint Server 2010 and I configured “SharePoint Server Enterprise Site Features” on my site. As additional information, we are using single server setup (standalone version) of SharePoint. Is there anything else I need to configure aside from these? I will appreciate any help from you.
Thanks

Roger's Access Blog said...

Nero,

Unfortunately, the Sharepoint side of this is a black box to me. I suggest asking your question on www.utteraccess.com. There are two forums that should help:
http://www.utteraccess.com/forum/Microsoft-SharePoint-f66.html
and
http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html.

There are a lot of knowledgeable people there and someone should be able to help.

Good luck to you.