Sunday, April 2, 2017

Access 2010 Web Databases: Other Compatibility Errors

Unfortunately, as of Access 2019, Access Web Apps have been discontinued.  From the March 2017 announcement from Microsoft:

“We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.”

In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?). As with any database project, I started with the data.

When I attempted to upload my existing tables into SharePoint, I got a number of errors (Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). I discussed errors in creating relationships in a web application (Creating Relationships). I talked about loading the tables into SharePoint (Uploading To SharePoint). Last time, I showed how to view the tables in SharePoint (Viewing In SharePoint).

In this final post discussing moving legacy tables from Access to SharePoint, I'll look as some other common errors that keep your tables from uploading to SharePoint.

Other Compatibility Errors

  1. Property value contains one or more characters that are incompatible with the Web.
    Table, field, and control names cannot contain characters that the Access client will accept. They cannot be longer than 64 characters and may not contain any of the following: / \ : * ? "" < > | # <TAB> { } % ~ & period (.), exclamation point (!), square brackets ([]), leading space, or begin with an equal sign (=).
  2. Only one of the Allow Zero Length property and Required property should be set to 'true' to be compatible with the Web.
    As the error says, you can't set both the Allow Zero Length and Required property of a field to "yes".
  3. Property value should to be empty or set to 'auto' to be compatible with the Web.This error refers to subdatasheets, which are not allowed in SharePoint lists. To correct it, set the Subdatasheet Name property of the table to [None] or [Auto]. You'll also get this message if you have values in the Link Child Fields and Link Master Fields properties. These properties must be empty.
  4. Calculated Column expression has an input field of a type incompatible with the Web, such as AutoNumber, Lookup, Memo, Hyperlink or Binary.
    Calculated columns are new to Access 2010, so they won't be in legacy systems. However, if you do use them, you can only use the following data types: Text, Number, Currency, Yes/No, Date/Time.
  5. Calculated Column result data type is incompatible with the Web.
    Likewise, the result of a calculated column can only be of the following data types: Text, Number, Currency, Yes/No, Date/Time.
  6. Column data type for value list lookups must be 'Text' to be compatible with the web.
    If you use a Value List for a lookup field (which I strongly advise against, anyway), it has to be filled with Text values. You cannot use numbers. A better solution, however, would be to create reference lists that you can query to fill the list.
There are others. I won't try to list them all here. In the Web Compatibility Issues table created by the Compatibility Check, there will be a link referencing each error.

Then, of course, there are errors (many, many of them) associated with uploading your application (forms, reports, etc.) to the web. I'll deal with those in a later series.

No comments: