Tips to migrate legacy databases to SharePoint and the Web

Today’s guest writer is Ela Yildizer Genc, a developer on the team who recently worked on Web Services, Backstage and Ribbon.

I have been recently migrated some legacy applications to the Web. I expect many Access users will want to migrate their existing applications to the Web, I thought it would be helpful share my experience and give you some useful resources.

There are some schema restrictions on web databases and if you want to bring your existing application to the Web, first you have to make your application web compatible. Here are the steps you can follow before you can publish your application to Access Services:

1. Save your application in default database format (.accdb)
2. Run Compatibility Checker to see if your application’s schema is web compatible.
3. Open the Web Compatibility Issues Table. The errors in table are explanatory with which table, which control you have and there is also a link for each error where you can also get info about what to do.
4. Fix the errors in Web Compatibility Issues Table and run Compatibility Checker until your application is Web compatible.

Now your data is web compatible and you can publish it to Access Services. However, you can’t open your objects (Forms/Reports/Reports/Modules) other than tables in the browser since they are still client objects. Existing forms and reports will publish to the server and round trip to other Access clients that open the application but they will not render in the browser. You have to create new web objects if you want to open these objects in the browser. You will also need to set the Web Display Form which will be displayed in the first place when you open your database in the browser. To set your Web Display form go to the Backstage | Options | Current Database page and select a form from the Web Display Form dropdown menu.  Typically, you will want this form to be the main navigation form—here is a blog post that will get you started creating intuitive navigation UI.

Tips and Tricks

There is a pretty good white paper that talks about integration of Access Applications with Access Services. Before starting your schema changes, take a look at the “Migrating Legacy Data to Web Tables” section to see a list of common compatibility issues and their solutions. Knowing the schema changes you are expecting will definitely help you make right design changes in your application.

Errors in Web Compatibility Issues Table are self-explanatory. While fixing an error, pay attention to element type, element name, control type, control name, property name in in Web Compatibility Issues Table. This will tell you where exactly your error is and the error description will tell you what is expected/incompatible with the web. There is also a link for each error where you can also get info about the solution. You can also take a look at Access 2010 Web compatibility checker to get more info about Web Compatibility Checker and more tricks about resolving issues.

Modules are published to the server and won’t affect the migration of the application to the web. You will need to recompile after saving your application in .accdb format.

Most common errors I faced and solutions:

1. Composite indexes are not compatible with web databases. If you have a database with composite indexes, you have to create a new primary index with AutoNumber type and create a data macro to preserve the uniqueness of your fields making up the composite key. Here is blog post about how you can write data macros to work around the issue.
2. Relationships that are not associated with a Web-compatible lookup are incompatible with the Web. You have to delete all relationships not based on lookups and create them using lookups.
3. Some Number/Currency/Date&Time formats are not supported in Access Services and below are the supported formats for these fields.

• If you have a Number field, make sure it is formatted as General Number/Standard/Percent.
• If you have a Currency field, make sure it is formatted as Currency/Euro.
• If you have a Date/Time field, make sure it is formatted as General Date/Short Date.

Depending on the complexity of your database, it is possible in a couple of hours you can make your database web compatible and start creating your Web forms and reports.

Enjoy!