Migrating to SharePoint Advice

[MajP]

I have a split db that has a pretty involved front end. I migrated it to Sharepoint and went fine. I had a few autonumber PKs and these got converted to Number. Because of that, I believe a lot of queries became read only. Or at least that is my first guess. How is this best handled? I plan to use access as the FE for the majority of things. I am trying to avoid lookup tables since this seems very limiting. I have not done anything with SP for years.

1. Can you convert an existing PK field to autonumber?
2. Do you roll your own PK on all tables.
3. I know there is an ID field you can add. Do add that, delete my other, and rename the ID field?

Any best practices appreciated.

 

[GPGeorge]

I have a split db that has a pretty involved front end. I migrated it to Sharepoint and went fine. I had a few autonumber PKs and these got converted to Number. Because of that, I believe a lot of queries became read only. Or at least that is my first guess. How is this best handled? I plan to use access as the FE for the majority of things. I am trying to avoid lookup tables since this seems very limiting. I have not done anything with SP for years.

1. Can you convert an existing PK field to autonumber?
2. Do you roll your own PK on all tables.
3. I know there is an ID field you can add. Do add that, delete my other, and rename the ID field?

Any best practices appreciated.

It's been an age and a day since I migrated any Access tables to SharePoint.

However, this YouTube video is the presentation Albert Kallal gave to my Access User Group last year. It's very thorough. It should answer most of your questions.

After you watch it, and if you still have questions, we can do some more research, including this long ago blog post on the subject. Actually there are three or four posts all related to each other.

 

[MajP]

Great thanks. It actually looks as if the autonumber fields still work for some of the tables. But I am clueless if I look at Access and see the visible columns, compared to what is in design view, compared to what is in the Sharepoint design (edit column) view. All three are completely different.

 

[theDBguy]

Just curious, how exactly did you migrate the tables into SharePoint? I haven't done it in a while either, but I wonder if you can import the tables one at a time in SP, would the ID column get converted into the SP ID column? If I remember correctly, it would just create a separate number column for it and assign its own ID column.

 

[Pat Hartman]

I am trying to avoid lookup tables since this seems very limiting.

I'm pretty sure you can't avoid these since they are the only way to define a relationship.

I'm sorry you have to use SharePoint. I only tried it back in 2007 and then refused to go there, it was so bad. Do you have any other options. People who have used Azure say that although it is slower than a standard LAN, it can be tolerable for some applications.

 

[GPGeorge]

Great thanks. It actually looks as if the autonumber fields still work for some of the tables. But I am clueless if I look at Access and see the visible columns, compared to what is in design view, compared to what is in the Sharepoint design (edit column) view. All three are completely different.

A lot of what SharePoint does is add meta data columns, which for all intents and purposes you can safely ignore on the Access side.

Pat is right about lookup fields. They are the only way to identify a relationship and enforce Referential Integrity. It is very helpful to have them in place in Access before the migration.

 

[MajP]

So far what I am seeing is SP is now orders of magnitude better and it is almost a fully relational solution. Still not sure of my issue since the autonumbers are working. The migration is seemless. Just hit export and pick a URL.

They are the only way to identify a relationship and enforce Referential Integrity.

As far as I can tell that is no longer correct. As of 2010 this works like a relational db to include cascade deletes. Pick up at 6 minutes in to see how this is significantly different starting at 2010.

 

[Pat Hartman]

SharePoint is an Entity-Attribute schema. That is one of the reasons it is so hokey. The infrastructure is there to make it look sort of like an actual relational database. Each table is essentially four columns. So, if there are 30 columns defined in the table. It will take 30 rows to hold one field per row. Although, the rows are probably sparse so if 10 columns are null, there will be only 20 rows to hold the populated columns.

UniqueID
TableName
FieldName
FieldValue

I'm also pretty sure that FieldValue is text so that there is always conversion that goes on to render the text values as numeric. I suppose it is possible to have x FieldValue columns so text and numbers can be separated to make math easier for queries.

I don't know what a row count limit is these days. It's probably more than 3,000 these days but it is no where near what an actual table would support.

 

[MajP]

It's probably more than 3,000 these days but it is no where near what an actual table would support

a little bigger now as far as I can tell.

You can store up to 30 million items or files in a list or library. Filtered views of large lists have a similar experience to other lists. However, when a list view shows more than 5000 items, you may run into a list view threshold error.