Joining 2 tables (both linked to other sources)

[steve]

Hi, In Access 2007 I have 2 have 2 Tables
Table 1 is a linked table to our SQL server (2005)showing customer name
and customer id
Table 2 is a sharepoint list that is linked to our WSS3 site showing
customer name (no data yet , waiting to link to table 1).

I would like to use the datain table 1 to continually update table 2 with
our latest list of customers.
Thanks for any ideas.

 

[John W. Vinson]

Hi, In Access 2007 I have 2 have 2 Tables
Table 1 is a linked table to our SQL server (2005)showing customer name
and customer id
Table 2 is a sharepoint list that is linked to our WSS3 site showing
customer name (no data yet , waiting to link to table 1).

I would like to use the datain table 1 to continually update table 2 with
our latest list of customers.
Thanks for any ideas.

Joining the two tables will NOT automagically transfer data from one table to
another. A Relationship *prevents* the addition of invalid data; it does not
*add* data to any table. In any case, you cannot enforce RI between two remote
tables in any case; Access would have no way to prevent some other program
from affecting either table.

What you will need to do is periodically run an Append query, appending data
from the linked SQL database table to the linked Sharepoint list. You may need
a Delete query as well if you want customers who drop off the first list to
also be removed from Sharepoint.

 

[steve]

John
I probally used the wrong word ("Join"), perhaps mapping or Synchronizing
the 2 tables might have been better.

This table would be a 1 way update, from SQL to Access2007 to the Sharepoint
list.
1. I am using access 2007 as the middle ground as I know of no other way to
directly link SQL to sharepoint lists.(Any other ideas greatfully accepted)

2.Would you suggest deleting the whole table and recreating it on each
update or leaving the table and just updating changed contents?

3. I am guessing this access db will need to run somewhere in the background
via a scheduled task do its job updating the linked sharepoint tables (Table
2)say every 30 minutes, or is there a way to trigger access into updating it
to update via a change in Table 1(SQL Linked table)?


Thanks again for the help.