



Lets Link to the SharePoint List/Table

In all my time using Access, there is one thing that has always been valuable and that is linked tables. Now you will be pleased to know that linked tables with read/write access to SharePoint lists are fully featured in Access 2007. This means that you can integrate your online SharePoint Lists into your own Access database forms, queries and reports.





So let’s find out how to link to the Issue Tracking List we just setup in SharePoint. Firstly, you need to choose the SharePoint List button on the External Data tab in the Ribbon as shown in Figure 5. This brings up a list of all of the SharePoint sites that you have been connected to in the past. If the project isn't in the list, copy and paste the HyperlinkAddress in your browser Address bar for the SharePoint project that your List is stored in. Figure 6 shows you a list of all of the Lists in the SharePoint project that you can link to.





At the end of the Linking Wizard, which will involve username and password and site location questions when you first run it, you will end up with a linked table as shown in Figure 7. This process, from start to finish, should take a few minutes once you understand where things are entered.









A different way of working, that you may find really handy with SharePoint, is exporting a table* that you would normally store in your database into a SharePoint project List. You will find that as SharePoint grows to be more popular, this approach will become popular too because you can solicit data entry/management from anyone who has a browser and uses the SharePoint site. This audience can be much bigger than the Access audience.



NOTE: It is my view that anyone running a large Access database will not want to put all of the database into SharePoint. Rather I believe it will be far more prudent to put just a few tables into the SharePoint site.



To export a table to SharePoint, choose the External Data tab in the Ribbon and select the table that you want to export. Now identify the SharePoint project that you want to export into and the table will be created in SharePoint. Once you have checked that the table is OK in SharePoint, delete** the table in Access and relink to SharePoint as per the previous instructions. You can see a table that I have exported from Access in Figure 8.



Lets Export a Table to a SharePoint ListA different way of working, that you may find really handy with SharePoint, is exporting a table* that you would normally store in your database into a SharePoint project List. You will find that as SharePoint grows to be more popular, this approach will become popular too because you can solicit data entry/management from anyone who has a browser and uses the SharePoint site. This audience can be much bigger than the Access audience.NOTE: It is my view that anyone running a large Access database will not want to put all of the database into SharePoint. Rather I believe it will be far more prudent to put just a few tables into the SharePoint site.To export a table to SharePoint, choose the External Data tab in the Ribbon and select the table that you want to export. Now identify the SharePoint project that you want to export into and the table will be created in SharePoint. Once you have checked that the table is OK in SharePoint, delete** the table in Access and relink to SharePoint as per the previous instructions. You can see a table that I have exported from Access in Figure 8.

The first way that an Access specialist can work with SharePoint is to value add the many templates that come built into SharePoint. If you look at Figure 1, you will see some of the many templates that can be used.Now to illustrate what happens when you select a template, let’s choose the Issue Tracking template. This creates a SharePoint list. In Figure 2, you can see that I have added the first record using a web browser a few minutes after creating the template. To add a record, choose the New button (shown in a green circle in Figure 2) and you will be presented with what looks like a standard Access form (Figure 3). You also have the option, on computers running newer browsers, to enter the data using a DataSheet form (Figure 4). It is important to note that both Figure 3 and Figure 4 are SharePoint forms that work straight out of the box. It is also good to know that under the hood of the forms and lists are many properties that can be modified to come up with a better custom solution. Access developers will feel right at home when they dig into the properties.