Pushing Access data to the web: Integrating with Office Live Small Business
Recently our friends over in Office Live have had major announcements about both the Office Live Workspace and Office Live Small Business products. Office Live Workspace announced global availability last Tuesday. It allows you to store your files and documents online and share them with others. In February, Office Live Small Business announced a major upgrade. It allows small businesses to build and maintain a web-site, market via e-mail and search engines and manage projects online. Here's a great New York Times article about the announcement.
It's exciting to see such excellent work coming out of these teams. Over on the Office Live Community site (blogs and forums for the Workspace product), I've recently fielded a bunch of questions from folks asking how they can integrate Office Live Workspace with Access.
Unfortunately, integration between Access and Office Live Workspace is not possible today.
But there is a really nice integration between Access 2007 and Office Live Small Business product. You can create an Access 2007 application which stores all its data in linked tables that live on an Office Live Small Business site. These linked tables are available from any computer with an Internet connection. You don't have to worry about firewalls, and you don't have to worry about setting up your own server or paying a separate hosting company to do it for you. By the way, this is all free, too.
One quick caveat: This is not the service to try with your million-row application. If you've got an application with < 2000 rows, performance will be zippy. Much above that and you may run into slow-downs.
Hint: You can improve performance by selecting "Work Offline" from the menu in the lower-right hand corner of the Access window once you've published an application.
Without further ado, I'd like to provide some step-by-step instructions for integrating Office Live Small Business and Access. What we're going to do here is sign up for Office Live Small Business, create a custom "business application" to host the data, publish the data up to the custom business application, and publish the ACCDB database up to a document workspace so that all your forms, reports, queries and macros are also available from any computer connected to the Internet.
If you already have an Office Live Small Business account, skip step 1.
- Go to http://smallbusiness.officelive.com/ and click the "Sign Up". The sign up process takes only a minute or so and requires no credit card information.
- After signing up, you will be brought to a page that looks like this:
Click "Collaborate with customers and coworkers
- After setting up business applications, you will be brought to a page that looks like this:
Click "Add a new application".
- You will see this:
Choose "Your custom applications" | "Blank workspace" and click Ok
- Use this screen to assign a name and an URL (address bar title) to your business application.
- You have now created a custom business application. Its URL will look something like this: http://northwindrealtorsofficelivecom.officelive.com/mybizapp. Remember this URL. You'll need it in a second.
- Across the top of your screen, you'll see a couple of tabs. Click the "+ Add" tab and choose "Document Library". Give the document library a name. Your document library now has an URL that looks something like this: http://northwindrealtorsofficelivecom.officelive.com/mybizapp/mydocLibrary. Remember this URL as well.
- Start Access 2007 and open the database you want to publish. Go to the External Data tab of the ribbon and click "Move to SharePoint".
- You'll see this screen:
Put the URL you remembered in step 6 into the text box below "What SharePoint site do you want to use?" New lists will be created in this application, and all the data from your tables will be copied over into them.
Hit the "Browse ..." button. Type in the URL you remembered in step 7. Your ACCDB (or MDB) file will be published to this location.
- After you finish publishing, go back to your business application on Office Live and refresh the page. You will see that your Access Database is stored in your document library. And you will that all your tables are also new tabs in your business application. The ACCDB can be copied locally to any computer. When opened, it will automatically retrieve the latest data from the lists stored on Office Live Small Business. You now have an Access application that's fully available on the Internet.
To find out about a company that's integrating Office Live Small Business and Access 2007 into its standard offering to customers, see Clint's blog post about Cool As Ice.
Please send us feedback. Are you interested in using the Internet as a place to store your Access data? How would you, or your customers, find benefit in this scenario? How can we make it more useful?
Thanks, -- Steve
