Using Sharepoint Lists as tables

[Dale_Fye via AccessMonster.com]

I've read many articles that claim that Access 2007 and MOSS 2007 are
"tightly integrated". Humbug!

There are any number of reasons not to do this, but my current IT nazis won't
let us use SQL Server or Terminal Server for our remote users. So, at the
moment, I'm stuck with using Sharepoint lists for access

If that is the case, then why is it that, when I export an Access table to a
SharePoint 2007 list, sometimes the process will convert my autonumber field
as the SharePoint autonumber field, and other times it will create the
SharePoint ID field as autonumber, and retain my autonumber field as a
numeric field.

As an example, today I exported two tables (tbl_Milestone_Status and
tbl_Milestones_Standard)to SharePoint.

tbl_Milestone_Status contained an autonumber field (Status_ID) that contained
continuous values from 1 to 7. When I exported this table to SharePoint, the
Status_ID field was hidden when viewed in Sharepoint (but not when viewed as
a linked table in Access). The Sharepoint "ID" field was relabeled as "_ID"
and I was able to see and delete this field from the list. For some reason
the export process accepted my Status_ID as the "ID" field in Sharepoint,
which means it retains its autonumber property.

However, tbl_Milestones_Standard also contained an autonumber field (Mile_ID).
This field did not contain continuous values, and when I exported it to
SharePoint, the SharePoint ID field remained hidden and displays as an
autonumber field when I view the linked list properties. However, the
Mile_ID field was converted to Long. So now, whenever I add a record to this
table, I have to generate my own Mile_ID value.

This is extremely annoying, and makes it difficult to migrate apps from an
Access backend to a Sharepoint List backend.

 

[Jeff Boyce]

Do your IT nazis have any suggestions...?

Have you tried posting to the Sharepoint newsgroups?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Have you CEO give your CIO and IT Nazis a new mission statement..

Like helping the company instead of impeding it.

 

[Dale_Fye via AccessMonster.com]

It isn't really that it cannot be done, it's the six months it takes to jump
through all the hoops (government security and info assurance issues). I
will eventually get this app running with a SQL Server as the backend, its
just the getting there that is the problem.

Actually, Sharepoint has worked relatively well as data store for accessing
my data from remote locations. It just has some significant drawbacks:

1. No referential integrity. Can be overcome, but requires lots of
additional coding.
2. Primary key (autonumber) is the ID field, unless when you migrate a table
from Access to SharePoint it miraculously converts your Primary key to the
SharePoint key (see OP). So if you want your own unique PK, you have to
generate it yourself.
3. This lack of PK also causes problems with some queries that should be
updateable, but are not.
4. I've run into some problems using Access 2003 against the SharePoint
lists, where when I Alias a field in a query, it refuses to return the alias
and continues to return the actual field name from the SharePoint list.

Dale

 

[Dale_Fye via AccessMonster.com]

Resolved this issue.

In order to get Access/Sharepoint to recognize the Autonumber field in a
table as Sharepoints Autonumber field, the field in the Access table must be
the PK.

Thanks for your suggestions.