SharePoint User Group UK

Hi
before sharepoint, my company worked with office access tables to data entry. now with sharepoint, i want they can data entry to sharepoint list, hence, i was force to import access tables (with all relationships) into sharepoint lists, for example for each table, create a list.
my question is that does sharepoint support this task (is there any way to import my tables with relationships into sharepoint automatically)?
can any body have an idea ?
Thanks

Check out this guidance;

http://office.microsoft.com/en-us/access/HA012303131033.aspx

---

Jamie McAllister
Consultant
Northridge Solutions Ltd

http://www.jamiemcallister.com

Thanks jamiemc

but it explains import data from sharepoint to access wheras i want vice versa!

i have a access 2007 database with relationships, now i want to users can data entry via sharepoint list. for this, i open my access db and then click Move to SharePoint and follow some steps to convert entire of access db, it has done but many features could not converted (like unique indexes, short datetime, refrencial integrity and ...).

my problem is that how to manage/recreate relationships in sharepoint (when i move access db into sharepoint) ?

is there any way to export linked table from access db to sharepoint list ?

Thanks

As you have discovered it's easy to move Access tables to SharePoint but you should bear in mind that SharePoint lists can't easily be used to reproduce a relational schema.

If you really do want to reproduce this in SharePoint you could take a look at something like Bamboo List Integrity and there may well be other 3rd party offerings out there with similar functionality.

---

Derek
http://workerthread.wordpress.com

Thanks Derek

but i think i don't need Bamboo List Integrity , if i can change column type in sharepoint list (from text to lookup), my problem almost solve.

can u help me about this issue (changing column type)?

Thanks

If you have Access 2007 and you use "Move to SharePoint" related tables will move across as a lookup if they are linked by an autonumber ID.  For example let's say you have Contact and Company tables:

• If a contact record is linked to company by companyID and this is an AutoNumber column on the company table, then moving tables to SharePoint lists will create a lookup column for you.
• But if the contact record is linked to company by company name (not good practice in Access but acceptable in SharePoint) then when you move tables from Access to SharePoint the lookup data type won't be retained.

If you have a problem once you have moved records across it might be possible (I'm speculating here) to create a new lookup column, then connect to the SharePoint lists in Access and update the new lookup column with correct values with an update query - after that you could delete the old text column.

 

---

Derek
http://workerthread.wordpress.com

Thanks Derek

you told :

my problem is that :

i have some tables which have relationships together via Non-Auto Generated Number PK.

for this i must do your suggesstion :

If you have a problem once you have moved records across it might be possible (I'm speculating here) to create a new lookup column, then connect to the SharePoint lists in Access and update the new lookup column with correct values with an update query - after that you could delete the old text column

 

can u more explain or give me a practical article about this issue ?

Thanks a lot.

I'm suggesting you try to link both lists in an Access database and create an Update query to update your lookup values.  If you don't already know how to use Update queries you will find more information here on updating one table from another.

---

Derek
http://workerthread.wordpress.com

OK, I've created a post about how this would work here.

---

Derek
http://workerthread.wordpress.com

Very Thanks Derek

I'm sure it very help to me.

i'll try it.

Many Thanks again.

Enter connection string, enter select statement, choose primary key(s) - thats it.

Only changed data are tranferred in case of update in background, so you can setup workflows or change notofications per email to the list.

http://www.layer2.de/en/products/pages/sharepoint-business-data-list-connector.aspx