UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sharepoint To Access Advice, SharePoint Server 2010    
 
   
blackch
post May 8 2017, 12:56 PM
Post#1



Posts: 24
Joined: 3-December 07
From: Virginia, USA


Hi I am new to this forum and stuck on a basic SharePoint requirement. I did read any previous post that might apply.

I have a straightforward Access database that track office tasks. No relationships defied, just one table need to be linked between Access and SharePoint.

Starting in Access I: 'Export the table tblTaskDetail from Access to SP'.

A SP List is created 'tblTaskDetail'

I then Link the table from Access to SP (External data tab/ SharePoint List/Link a data source by creating a linked table) and SP creates a 'Link List' call tblTaskDetail1

This works fine and I update from SP to the 'Link List' in Access and from the Access 'Link List' to the SP list no problem with updates.

THE Problem. The main detail base table that was exported to SP is NOT updated. I need the main Access table to be updated not just the 'Linked List' in Access and the exported SP list.

Is my approach wrong? Note that from SP if I try to 'Open with Access' I get the message 'Export to database failed. to export a list you must have a Microsoft SharePoint foundation-compatible application'.

I am not certain if the Network administrators have disabled the ability of SharePoint to directly interface with Access.

Any hints or thoughts would be greatly appreciated. Thanks!

Clint

--------------------
Clinton Black
Go to the top of the page
 
GroverParkGeorge
post May 8 2017, 04:51 PM
Post#2


UA Admin
Posts: 29,445
Joined: 20-June 02
From: Newcastle, WA


Please clarify.

How many tables are there altogether?

"...just one table need to be linked between Access and SharePoint."

Does that mean you moved one table to SharePoint and kept other tables locally in Access?

"The main detail base table that was exported to SP is NOT updated."

Does this mean you exported this table to SharePoint as well?

Why would you link only one SharePoint List to Access?

Are there two different tables, named tblTaskDetail and tblTaskDetail1?

If that is the case, YOU have to do something to update both of them yourself. How you do that, of course, depends on the work process involved.

--------------------
Go to the top of the page
 
blackch
post May 9 2017, 09:17 AM
Post#3



Posts: 24
Joined: 3-December 07
From: Virginia, USA


Thanks for the reply there are 8 tables I am only interested in updating the tblTaskDetail table.
I only exported the main table to SharePoint.
The other tables are local lookup tables, I could export them but I do not need them them in SharePoint.
After I Link the Access table to SP I still have a access table called tblTaskDetail and I now also now have a 'Link List' object called tblTaskDetail1.
When I update the SP list tblTaskDetail1 the update is reflected on the Access side in the 'link list' object tblTaskDetail1 not in the Access object tblTaskDetail which is being used by an Access application for data entry and reporting.

Eventually, on the SP side I want to create some workflows which will update fields in the SP 'Link List' table simultaneously in the Access Table 'tblTaskDetail'.

That is where I am going however the communication/update seems to be between the SP list and Access 'Link List'. My application does not see the updates because the source of the data is the Access table tblTaskDetail

How can I make the table get the updates from the Access Link List/SP List?

Thanks again for any help.


--------------------
Clinton Black
Go to the top of the page
 
GroverParkGeorge
post May 9 2017, 09:23 AM
Post#4


UA Admin
Posts: 29,445
Joined: 20-June 02
From: Newcastle, WA


This sounds too complicated.

One SharePoint list can't update an Access table unless YOU write a query or use VBA to make that happen.

In other words, you have TWO separate tables, not one, and you can't expect either SharePoint or Access to "automatically" do the updates for you. It's sort of like having lights in two different rooms in your house go on or off when you flip the switch in only one room. Unless you have put in additional wiring to connect them, they don't synch.

So, why keep the local Access table at all?

Rename it to something like "tblTaskDetail_Local_Archived". Then rename the SP list to the original name, "tblTaskDetail". Use it instead of the local Access table, and no more problems with being out of synch.

Eventually, once things are functioning smoothly, you could delete the now-unused Local Access version.
This post has been edited by GroverParkGeorge: May 9 2017, 09:26 AM

--------------------
Go to the top of the page
 
blackch
post May 10 2017, 12:37 PM
Post#5



Posts: 24
Joined: 3-December 07
From: Virginia, USA


Thanks, I will try that 'rename' of the list to the access table name. I was just looking for a general idea and you gave it too me. Thanks again!

--------------------
Clinton Black
Go to the top of the page
 
GroverParkGeorge
post May 10 2017, 12:47 PM
Post#6


UA Admin
Posts: 29,445
Joined: 20-June 02
From: Newcastle, WA


Continued success with your project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th May 2017 - 06:31 AM