Sharepoint 2007 lookup field showing up as memo in Access 2003

[Geoff Murray]

I tried posting this message once, and got an error, so forgive me if this is
a duplicate...

We've had an MS Access 2003 application for a few years that is linked to
some sharepoint lists. Last year we upgraded to Sharepoint 2007, and the
application continued to work fine.

Just in the last couple of weeks, the application has stopped working. While
debugging, I figured out that the field definition in MS Access 2003 for a
sharepoint lookup field has changed. Prior to a couple of weeks ago, it used
to show up in Access as described in this page:
http://office.microsoft.com/en-us/access/HP010477131033.aspx, that is, it
showed up as a number, and we could properly use it for querying, joining,
updating, etc.

Now, it the lookup fields show up as MS Acess memo fields, and remove most
of the functionality.

We did not change the app. No idea what patches we received on the server or
the desktop (who can really tell what's in a patch anyway) recently.

We have done the following:

- Looked far and wide on the web for any refernence to this
- Tried re-synching the linked Sharepoint tables
- Tried creating an empty d/b, and link to those same sharepoint tables
- Made sure our sharepoint lists didn't have history enabled
- Tried linking to other sharepoint lists on both our servers

Really stumped. Has anyone else noticed this recent change in behaviour, or
have any idea how to workaround? It's very destructive.

 

[Geoff Murray]

Double checked - the memo behaviour is happening with one of our sharepoint
servers, not the other. So, that probably rules out Access itself, but just
hoping someone might have seen this that has a clue what to look for.

 

[Geoff Murray]

OK - we've narrowed it down, and have a workaround. No idea of root cause.
Thought I would pass along the information in case someone else runs into the
same problem. Since MS Acess 2003 no longer officially supported, not
expecting a fix.

When using the Windows Sharepoint Services for linking tables, the behaviour
for MS Access 2003 has changed recently. When you go in, and select multiple
Sharepoint lists to link into your d/b, the first sharepoint list to get
linked in brings across Lookup fields (and the hidden ones like Created By,
Modified By) as numbers correctly in Access. The second and subsequent
sharepoint lists get linked in, but the lookup fields are all defined as
"Memo" fields.

The only workaround we could come up with is to delete the linked table
references from the Tables view, re-link, but do one table at a time. It was
a little challenging because of some of our tables and cross-linking, but we
managed to muddle through.

MS Access 2003 (11.8166.8221) SP3

 

[De Jager]

I tried posting this message once, and got an error, so forgive me if this
is
a duplicate...

We've had an MS Access 2003 application for a few years that is linked to
some sharepoint lists. Last year we upgraded to Sharepoint 2007, and the
application continued to work fine.

Just in the last couple of weeks, the application has stopped working.
While
debugging, I figured out that the field definition in MS Access 2003 for a
sharepoint lookup field has changed. Prior to a couple of weeks ago, it
used
to show up in Access as described in this page:
http://office.microsoft.com/en-us/access/HP010477131033.aspx, that is, it
showed up as a number, and we could properly use it for querying, joining,
updating, etc.

Now, it the lookup fields show up as MS Acess memo fields, and remove most
of the functionality.

We did not change the app. No idea what patches we received on the server
or
the desktop (who can really tell what's in a patch anyway) recently.

We have done the following:

- Looked far and wide on the web for any refernence to this
- Tried re-synching the linked Sharepoint tables
- Tried creating an empty d/b, and link to those same sharepoint tables
- Made sure our sharepoint lists didn't have history enabled
- Tried linking to other sharepoint lists on both our servers

Really stumped. Has anyone else noticed this recent change in behaviour,
or
have any idea how to workaround? It's very destructive.