Questions about Sharepoint Lists created by the Access data migration wizard (location, deletion, updates)

user_17423552 0
Viewed 42

I have years of Access programming under my belt, with either Access or SQL back ends. I'm volunteering my time to help a small nonprofit build a volunteer database. We are forced to use SharePoint Lists as the data backend.

Our first migration using the Access wizard worked but messed up the data because I had not created all the required relationships prior to upsizing. I did my homework, and we tried again. But even though we created a folder for all those files, the Access wizard put the lists at the root of the SharePoint file, and now they are mixed with the first round of tables, as well as other folk's stuff. So, my questions are:

  1. Can we specify where those SharePoint Lists are stored beyond the root directory? (I worry that others may inadvertently edit those lists...)
  2. How do I delete the lists? It says we cannot because relationships are set
  3. How do I in the future alter the lists (like altering tables in SQL?) I know I'll need to add columns eventually.

I've searched MS and here, and MS solutions are crazy simplified, so they don't answer my questions. TIA!

ms-access sharepoint
1 Answers

Can we specify where those SharePoint Lists are stored beyond the root directory? (I worry that others may inadvertently edit those lists...)

Yes, go to the SharePoint site. From ONLY the Team site, create a sub site. (regular sites don't work - MUST BE A CHILD of TeamSite).

You can up-size the access tables to that site.

So, just like creating a folder, or in this case a site?

Say you create a new teamsite (MUST be subsite - so crete a subsite to teamsite) called Customers. Then you can specify that target for the database. As noted, since the access table features are "special", only sites created as sub sites to TeamSite(s) will work.

So, it stands to reason that each database should get its own site (perhaps better term is sub-site). Then you can up-load/up-size a database to that one team site you just created - and all tables are thus "grouped" or part of that "one" site or URL.

So say we have a database called customers - then we create a (sub) site called customers. Your url would thus be "something" like this:

https://myCompanyName.sharepoint.com/TeamSite/Customers

How do I delete the lists? It says we cannot because relationships

Well, actually, even in Access regular tables - you as a general rule can't delete a table that is part of a related set of tables. This suggests then you have to delete the relationships first.

Display the site contents,

right click on the child table that has the related column (choose settings).

You now are in the "settings" for that one table/list.

You can click on the column that has the relationship.

You see this:

enter image description here

So, you can remove the relationship. (scroll down to bottom).

And if those tables are LINKED from access?

You can use the table view in Access - and change/add columns - you do NOT necessary have to do this from the web interface.

How do I in the future alter the lists (like altering tables in SQL?) I know I'll need to add columns eventually.

You can do this from Access or SharePoint - Access side is better choice.

if the table is linked from Access, then use the table view. You not be able to jump into table design mode - but if you open a table, you see this:

enter image description here

And while in above I am on the last (add new) column, if I wanted to say change or add a index to say FirstName column? I could do this on SharePoint, but you ALSO can just click on any column - note the ribbon now:

enter image description here

So, all this is quite much basic SharePoint stuff.

But, open the linked list - you can add new columns, or remove columns - the above ribbon options show the options you have (such as index, etc.). And like always, to delete a column, in that table view from Access, right click - you have this option:

enter image description here

So, you add, or delete fields BOTH from the SharePoint site, or from the above table view in Access.

user_105270
Related