Lately we have gotten a lot of questions about how businesses can take their old access databases or excel spreadsheets and get them on the web with Access Hosting. The tutorial video above will hopefully demonstrate some of the issues with web compatibility and how to get your database ready for the web, but here’s a quick step-by-step breakdown on how to create a new web database from data that you have in Excel.  If you have your data already in an Access 2010 table, you can skip to STEP 10, but to get better conversion results you should check out www.access2010converter.com since it will preserve your forms, queries, etc.  This tutorial focuses purely on getting your data into a web compatible format so that you can upload it to Access Hosting and start enjoying our great service.

1. Create a New Web Database in Access 2010
2. Open up your Excel File that has your data
3. Copy the data in Excel
4. Switch over to Access
5. Right Click in the left sidebar and select paste. This should paste over your MS Excel data into Access 2010
6. Now before a Database is ready to be published to Access Hosting, it has to pass Access 2010’s Web Compatibility Checker
7. Goto File -> Save & Publish -> Publish to Access Services; Click the Web Compatibility Check Button.
8. View any Web Compatibility Issues and Fix them.  In the video above, my fields contained illegal characters such as ‘Pts/G’.  Changing these headings to ‘Points per Game’ fixed nearly all of the web issues.
9. Run the Web Compatibility Again.  You should only get 1 error for the unique identifier ‘ID field’ requirement.  SharePoint requires that each record have a numerical ID associated with it.
10. Highlight and Copy all the data in your table
11. Create a new Web Table.  This should create an ID field that will automatically assign a numerical ID to each record.
12. Right Click your table and select ‘Paste as Fields’
13. Close and Delete the other non-web compatible table
14. Run the Web compatibility checker – there should be zero web issues
15. Publish to Access Hosting!

This short video will show you how to distribute your Access 2010 Web Application to other Access users by creating and ACCDW link. The ACCDW file is a small shortcut file that allows users to connect to your web database and completely hydrate a local, connected copy of the web application on their desktop. This is a very easy way to take your Access 2010 application from its development stage into production across a set of widely dispersed users.

One quick and easy way to create a new table in your Access Web Database is to use the clipboard and copy/paste from Excel or any other tabular data source.

We recently published a short video tutorial on using data macros to trigger an email alert in Access 2010 (see below).  Since this behavior assumes that you are running on SharePoint 2010 Enterprise Edition and relying on SharePoint for email delivery it is worth taking a closer look at the best practices we have established for making sure emails are delivered as expected from the SharePoint 2010 Server. If you have published your application to our shared hosting farm we run a standalone instance of the SMTP service with verbose logging enabled. The net effect of this configuration is that we get a detailed report of the conversations between SharePoint, the SMTP Mail Delivery Service, and the destination email server. This information can be invaluable in determining why an email generated successfully from Access does not make it to the intended inbox. Common mail delivery issues include lack of a reverse DNS record, SPAM quarantine, and typos in the users email address. Many of these issues can be properly diagnosed and corrected by using an SMTP instance dedicated to Access Services. Disclaimer: There are instances where the email message is marked as SPAM and non-delivered without any notification back to the originating server as to why the message was rejected. We won’t mention any names but the worst offender of this is spelled Y-A-H-O-O. That problem is easily corrected by finding an alternative free email provider.