Wednesday, June 16, 2010

Access 2010 Web Databases: Other Compatibility Errors

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)


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.
.

14 comments:

M. Ortgage said...

Here discussing about how to convert an existing Access application to a web application. This will help in future.

M Simms said...

This Access-to-Web concept is interesting and I can see a huge demand for it. However, Microsoft really needs to develop the technical migration guidelines including details as posted above.
It's ridiculous that all developers are going to be tripping-up on the same issues.

Roger's Access Blog said...

@ M Simms

I agree, but we've been assured this technology is in its infancy, and Microsoft is committed to improving it in future versions. What direction that takes, remains to be seen.

Anonymous said...

Excellent information. Many thanks for posting this series of blogs.

As has been mentioned, it's a shame Microsoft don't make easily available this sort of valuable information.

Anonymous said...

how do i post access database on the web if I do not have sharepoint

Roger's Access Blog said...

@Anonymous,

Unfortuneately, you can't. Sharepoint is required for Access Web databases.

Anonymous said...

I would just like to say these blogs are awesome! Most helpful info I have found regarding the migration of an Access 2007 database to SharePoint 2010!

Steve Stickland said...

Thanks for this article Roger. I've been designing dbases in Access for years, and eventually (2006) got away from the evil lookup fields in tables....only to discover that, from what you have stated, Microsoft have now made them "mandatory" if you want to publish to the web using Microsofts Sharepoint......I think I'll switch to Oracle.

Admin said...

Your blog post were excellent in importing my database to sharepoint 2010. I did have an issue with publishing because my server was on a local connection, i was using an IP to publish. that created the an error occurred while initializing the access services database. for now, i modified the HOST file on my win7 to point to a named dns.

hope that helps those who are stuck!

Roger's Access Blog said...

@Steve

Moving to Oracle may be premature. I'd wait until the next version of Access.

Roger's Access Blog said...

@ Admin

Thanks for the addtional information. I'm sure it will be useful to someone.

Cindy said...

I'm creating a simple small database that need to have SharePoint accessibility. I'm getting a Web Compatibility issue ACCWeb107019 stating Property value should be set to 'Yes' to be compatible with the Web.
I got the error by creating a subform to link two tables (each has an AutoNumber ID) the issue is from the subform [NAME_ID] linked to the main form [ID].

Bret said...

Roger, I'm new to putting Access in SharePoint... I've read through your blog and I'm still confused as to how users in SharePoint actually utilize the database. My forms were useless as you stated when they came across as all the relationships were gone even though I linked them through a lookup. The entire client form was gone! Now I'm getting a recordset error when I link them... sometimes the records actually link and sometimes they don't... weird... can you help out?

Roger Carlson said...

Honestly, I can't. As I stated in the link at the top of the article, 2010 web database technology is a dead end. I haven't worked with it in years and I don't recommend starting a new project with it. However, there is a very active board for Access web apps at www.utteraccess.com