Linking to an existing SharePoint list
If your SharePoint site already has lists - usually created by a SharePoint administrator or developer - that you want to get information from and make changes to, then you want to link to those SharePoint lists. To link to an existing SharePoint list, follow these steps:
- Click the Ribbon's Create tab, click the SharePoint Lists command in the Tables group and then choose the Existing SharePoint List option.
- When the Get External Data - SharePoint Site Wizard loads, enter or choose a SharePoint site at the top of the first screen.
As with linking to other external data sources, you're prompted to either import the data to you database or link the data source in a linked table. If you import the data, you'll get a snapshot of the list at the time you performed the import, and any changes you make won't be reflected on the SharePoint server. If you choose to link to the data source, you'll see the data that other users enter and modify - and they'll see your changes, too.
- After choosing to link or import, click Next to display the list of SharePoint lists to link to or import.
- Simply click the check box to the left of each list that you want to link to, and then click OK in the bottom-right portion of the screen.
The SharePoint lists you choose to link to appear in the Access Navigation pane as linked tables. If you chose to import the lists, the data will be stored in local Access tables and will appear with the standard Access table icon - without an arrow - next to the table name.
You may notice that when you create a new list or link to an existing list, some other tables appear in the Access Navigation pane. If these lists are linked in SharePoint, they also need to be a part of the Access database that's manipulating them. So if you see tables that you didn't create or select, don't be alarmed. Access is smart enough to know you need them and just brings these lists along for the ride.
Moving an existing database to SharePoint
If you've already taken the time to build an Access database with tables, queries, forms, and reports that all work together and are automated with modules and macros, you don't have to reinvent the wheel on SharePoint. Access provides a tool that moves your existing tables to SharePoint and links them to the Access database.
To move your database to a SharePoint site, follow these steps:
- Click the Ribbon's Database Tools tab and click the SharePoint command in the Move Data group.
This starts the Export Tables to SharePoint Wizard.
- Enter the SharePoint site where you'll place these tables as SharePoint lists.
If you've used your SharePoint site before, it appears in the list of available sites. If you're moving these tables to a site that's not in the list, type the site information in the provided text box.
- Click Next, and you're prompted for the login information for the SharePoint site.
- Enter your username and password, and click OK.
Access creates the SharePoint lists, moves the data from Access to these lists, and then links these lists to the Access database. If everything goes smoothly, you're shown a message that the tables have been successfully shared.
- On the confirmation screen, click the Show Details check box to show the summary of what Access did.
You'll see that it created new lists on the specified SharePoint site and made a backup copy of the original database.
- Click Finish, and your database opens, displaying the linked tables in addition to your other database objects.
If something goes wrong and Access can't move the tables to the SharePoint site, or other issues occur during the process, the wizard creates a table called Move to SharePoint Site Issues that lists the issues that prevented the tables from being moved. Just open this table in Datasheet view to review and correct these issues as they're found.
By linking tables to a SharePoint site, you can create front-ends to an existing SharePoint site or give users access to data from your database in SharePoint. And these users don't even have to have Microsoft Office Access installed to edit the data in a SharePoint list; they just need access to the list on the SharePoint site.