Change Local Table to Sharepoint table

[JamesN]

Hi,

I have a split database consisting of many forms and tables which is published on Sharepoint 2013. I need to add a new table to the Sharepoint which is to be updated by one of the forms.

I have tried exporting to the Sharepoint which has added it but i'm unsure how to create the link so it is no longer a local table and is on the Sharepoint. The linked table manager was my first thought however it is greyed out.

Thanks

James

 

[GohDiamond]

What version of Access are you using?
If it's Access 2007 or later,

1. Click on External Data on the Ribbon
2. In the Import & Link section select the dropdown Menu "MORE"
3. From the Menu Select "SharePoint List"
4. Make sure you have access to the SharePoint site where the data now lives
5. Select Link to the Data Source(Site) and click on NEXT
6. Select from the lists available in the windows by clicking on the checkbox
7. And click OK.

That will put the SharePoint list in your database like any other linked table but it will have a Yellow/Gold Icon in your Nav Pane at the bottom of the Tables list depending on how you have your category sorted.

Hope that helps.
Cheers!
Goh

 

[JamesN]

Thanks for the response Goh.

Will this also work for uploading/linking one single table?

What i found was that it was adding all existing tables in my database onto Sharepoint again and adding a 1 to the name when they already existed, this made the previous tables redundant and I didn't want these to be added/changed.

Ideally I just wanted the new table to be added and the others excluded.

James

 

[JamesN]

Think its clicked, do you open the table you want to be linked and then complete the above process? Tried it with a few different tables and it appears to be uploading the selected table only?

 

[GohDiamond]

What do you mean by Upload? Publishing to SharePoint or Linking Access to SharePoint Lists?

After you publish all of your tables in the database to SharePoint then you should use Access as a FRONT END only (No Tables should remain in Access - BUT MAKE A COPY OF THE DB BEFORE YOU DO ANYTHING DRASTIC)

Then link all of the published tables back to the Access Front End with the method I described. That will eliminate all of the tablename1 automatic naming convention that Access uses when a table from another source which has the same name as an existing LOCAL table is linked.

As long as the Lists on SharePoint have regular Text, Number, and Date fields (No Attachment Field, No Multivalue field, No SharePoint fields populated by a workflow) You should be able to READ and WRITE to the lists just like any other linked table.

There is a workaround that involves some SharePoint workflow manipulation if you use Dropdown menu's in SharePoint to edit/populate any of the fields that you are sharing with Access.

You get to select the published tables that you want to Link back to your Access FrontEnd by clicking in the checkboxes and selecting OK. If you uncheck a Box it will UNLINK that site table from the Access Front End.

As far as Publishing your Database tables to SharePoint, I believe it's an all or nothing process, so drop that table by itself into a test DB and publish it to SharePoint if you only need to publish 1 table.

Let us know how it goes,
Cheers!
Goh