How to Create a Web Database from Excel

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 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 entry was posted in Subfeature, Tips & Tricks, Video Tutorial. Bookmark the permalink.

Leave a Reply