HI! Does anyone have any idea how to s开发者_高级运维ynchronize MS Access data to SharePoint portal. I would like to link both of this together and trigger a workflow upon receiving a new data entry.

Hope you could advice on it. Thanks!

---

In Access you can have so called "linked tables". You can have access linked tables with a Sharepoint installation. In Sharepoint this linked table manifests itself as a regular list, so you can have a workflow trigger on each new item created.

There is a nice tutorial here: How to Link SharePoint Server 2007 Lists with Microsoft Access 2007 and for Sharepoint 2010 and Acccess 2010 Microsoft hast written something nice: Synchronize a SharePoint 2010 list with Access 2010.

Additionally let me show you some Screenshots from Access. When you go to External Data > Export > Sharepoint List (see next screen)

When you click on that button you get the following dialog where you can select where to export your table to:

The same dialog you get when you Right click on a table, go to Export > Sharepoint List.

Now if you want to synchronize your data, you need to link that created Sharepoint list to your Access DB. So you click on External Data > Import > From Sharepoint List and you get the following dialog:

When you have a linked table it is synchronized with Sharepoint.

---

I think you are looking for this article. http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010100908.aspx

Basically, you need to create a new Data Connection Library (pick from the Library list in the "Create" page). And then follow the steps depending on the connection method (i.e. Single Sign-On, custom connection string, or user id/password).

---

The process involves migrating your access tables to SharePoint. So if you moved or migrate a table in Access to SharePoint AND THEN choose to link the table, then it is a live connection to SharePoint. This is a bi-direction synnc of that table. So any change in Access (any update to that linked table) will see changes sent to the same table (list) in SharePoint. This is a VERY different process then trying to sync a whole file. So power-point files, or even a Access file does not work well at the FILE level, and you don't get any kind of multi-user setup.

However, if you migrate the tables to SharePoint lists, then any sql query you build in Access against that linked table will work just fine. And that includes append query, update query, or just a select for data.

And you can even work against these linked tables in off-line mode. This means that you can continue to update the tables even without network connection. When you do finally get wi-fi or some internet connection, then the data will auto-sync (both ways) and tables will update missing or changed data.

Note that you can even migrate and maintain related data, and SharePoint now supports relations and maintains those relations for you (referential integrity between tables is supported when using Access 2010 (or later) and SharePoint 2010 (or later).

VERY careful steps are required to migrate related tables. Access will migrate and send related tables of data to SharePoint (and maintain the relations for you). You simple have to ensure that your relations are of a type that SharePoint supports.

This means your PK has to be a autonumber ID type, and the foreign key has to be standard long data type. Since this setup is the vast majority of Access relationships, then these types of related tables move rather well to SharePoint. However, if you PK/FK setup is say a string, then such relationships don't work in SharePoint.

The other issue is to keep in mind that performance issues can arise when the row count exceeds 5000 rows when using Office 365 or hosted SharePoint. If your SharePoint system in on-premises then you can turn off some of these limits.

Migration of related data tables is easy, but you need to ensure you setup the relationships using the table lookup wizard in most cases. Sometimes you can use the relationship window, but in most cases, you need to re-build the relationship in a correct way BEFORE you migrate the tables to SharePoint. If your don't have related data, and just a few tables, then you don't really have to do much of anything in Access before you migrate such tables.

As noted, once migrated to SharePoint, then any sql query you execute against such linked tables will work fine. So no need to update the table local, or on SharePoint - the whole process is fully automatic and synced for you.

This video shows how to migrate related data tables to SharePoint if that is your requirement:

https://www.youtube.com/watch?v=3wdjYIby_b0&t=0s&list=PL27E956A1537FE1C5&index=3

Edit

Given that the tags are for SharePoint 2007, then you will find that access 2010 (or later) and SharePoint 2010 (or later) is required for referential integrity to work. And pre Access 2010, you find performance of linked lists to be rather slow, and table row counts needs to be keep small. (under 10,000 rows).