Microsoft Office
Integrating Access Databases with SharePoint
Peter Serzo
At a Glance:
- Benefits of integrating Access and SharePoint
- Migrating Access data to SharePoint
- Extending Access functionality with SharePoint
When I talk to end users about Microsoft Office SharePoint Server (MOSS) 2007, I always ask them to show me the tools they use and how they use them. Without fail, someone somewhere in the
organization is using Microsoft® Office AccessTM to address essential business needs. Access is still a viable tool and an important asset at many companies.
MOSS 2007 provides a way to leverage applications built on Access and integrate them into SharePoint® to create a new solution with far greater reach. The focus of this article is to show how and where MOSS integrates with Access and how an end user can configure these two products to build a solution.
The underlying technology that MOSS brings to Access incorporates workflow, a common repository (SQL Server®), and a Web-based interface (the browser). SharePoint features include document management, workspaces, and the ability to set up custom lists and to utilize built-in workflows and out-of-the-box Web parts that cover everything from advertising events, to building a contacts list or an issues list, to managing user-created forms. Moreover, the ease of use and familiar Office technologies will enhance the end user's feeling of empowerment. As you will see, MOSS does not threaten the current solutions that are in place. Instead, MOSS facilitates efficiency since you do not have to rewrite applications, allowing end users to focus on resolving business issues rather than technical ones.
It's not only the end user who benefits. IT departments and administrators struggle with how to assist their end users in maintaining all the custom databases that spring up around a company. Many do not even know that Access is being used to solve mission-critical business problems until certain issues come up, such as storage limits, multiuser access, security, repairing corrupt databases, Web usage, incompatible Access versions, and deployment of changes across multiple desktops. SharePoint has an answer for each of these issues.
While Access has its limits, it is powerful and friendly, especially for those who don't write code for a living. In one spot you get configurable relational tables, simple queries (and complex ones), a drag-and-drop front-end form creator, reports based on the data, and the ability to configure multiple users. Building solutions based on Access and SharePoint makes sense both for organizations looking to pool resources and talent, and for individuals who want to take advantage of applications and skills built up over the years.
Getting Started
Access 2007 provides several points for integrating with MOSS 2007. As you can see in Figure 1, these include: importing data from a SharePoint List, publishing data to a SharePoint site, creating a SharePoint List, using SharePoint data as an external source, migrating an Access database to a new or existing SharePoint site, and offline accessing of SharePoint data.
Figure 1 Access 2007 provides easy entry points for integrating with SharePoint (Click the image for a larger view)
Creating a SharePoint list from an Access table is a good place to begin. A SharePoint list is very similar to a table in Access. It is saved in the SQL Server content database, contains certain field attributes, and supports lookups.
Some limitations to keep in mind when creating a SharePoint list are: Microsoft recommends a limit of 2,000 items per view to obtain optimum performance; SharePoint doesn't enforce referential integrity; OLE Objects are not exported into SharePoint; and validation of data is limited.
To create a SharePoint list from an Access table, first select the External Data tab, then click on the enabled SharePoint List button under the Export group. Now you must specify the SharePoint site where this list will be active by filling out the Export - SharePoint Site dialog shown in Figure 2, or by right-clicking on your table and choosing Export to a SharePoint List. Note that if the table was a child in a relationship, all the parent tables would also be exported.
Figure 2 Access data will be exported to the SharePoint site you specify (Click the image for a larger view)
When the export process is finished, you are given the option of saving the export steps so you do not have to repeat them. At this point, you can go to the SharePoint site and see the data that was uploaded. This simple operation is useful for moving data into SharePoint and making it accessible to users. The benefits are increased security, as data is now being saved in a SQL Server database, and easier access, because information is available via the browser.
However, another operation—linking lists in SharePoint as tables in Access—turns out to be a much more effective way to integrate Access and SharePoint. To do this, click on the External Data tab in Access and choose SharePoint List under the Import group. This brings up the dialog box shown in Figure 3.
Figure 3 Linking Access to SharePoint (Click the image for a larger view)
Choose the site that contains the SharePoint list that will be used as a linked data source, then select the list you want to link. When this operation is finished, the list will show up as a linked table in Access, and at the bottom right-hand corner of the application you'll see an "Online with SharePoint" indicator. There is now two-way synchronization between Access and SharePoint, supporting add, change, and delete operations from either SharePoint or Access. As you'll see, code can be written against the table to supply extra functionality.
When you link to SharePoint lists in Access, you'll find that another table, the Windows® SharePoint Services (WSS) Users Information List, is also imported and linked in by default. And an issues table is created called "Move to SharePoint Site Issues." Here you can review any conflicts with the data.
For more information on migrating data and how data types are handled during conversion, see "How Access Converts between Access and SharePoint Data Types" (office.microsoft.com/en-us/access/HP010477131033.aspx) and the section called Limitations of migration in "Move the Tables in an Access Database to a SharePoint Site and Link to Them" (office.microsoft.com/en-us/access/HA101314681033.aspx).
A third way to integrate tables and lists is initiated through SharePoint. Choose a list and, under the Actions tab, click on the "Open with Access" choice. A dialog box pops up that asks for the name of the database (new or existing). After entering the name of the database, you choose whether to simply link to the data on the SharePoint site or export the data outright into Access (see Figure 4).
Figure 4 Making SharePoint data reachable through Access (Click the image for a larger view)
Moving a Database to SharePoint
So far we have looked at importing a SharePoint list as a linked table and exporting tables to SharePoint as lists, but what if you need access to the whole database? Many organizations have mission-critical applications running in Access and need those applications to be available remotely so that individuals can run reports and queries and enter data. In such situations, it is not always feasible—economically or based on available skills—to rewrite the application. SharePoint and Access have this covered with the "Move to SharePoint" option, found under the Access SharePoint Lists tab. This is similar to the option to move to SQL Server using the Upsizing Wizard, which first appeared with Access 97.
The benefits of moving an entire Access database to SharePoint are that all the tables are converted to SharePoint lists; a backup is made of the Access database; lists automatically become linked tables in Access; you can move the whole database, not just tables; and the database works much as it did prior to the move.
When you click the Move to SharePoint button, the dialog box in Figure 5 appears. After specifying the site, you need to decide whether to choose the option to save the database. If you do, the whole database will be saved into a selected document library. This means that the entire database (including forms, reports, queries, and macros) is saved into SharePoint and is thus available on the server. When a user wants to access the database, he simply navigates to the library where it is, clicks on it, and it is automatically deployed to his desktop.
Figure 5 Specify the SharePoint site to which you want to move your database (Click the image for a larger view)
When you choose to move the whole database, a Publish Changes message bar appears below the ribbon. This allows you to keep the local changes to the design and data in sync with the copy on the server. If you don't select this option, only the tables will be converted into lists and linked into Access. In this case, the whole database is not available to the user community and is not backed up as an object in SharePoint.
After choosing one of these options, the database and its associated information will be available through the SharePoint site you chose. Again, a table is automatically created called "Move to SharePoint Site Issues." This is where any conflicts due to the migration are documented. You can review any issues that Access encountered while uploading the table on the SharePoint site.
Views, Forms, and Reports
After the database is moved, you'll want to test its functionality. Make sure the "Online with SharePoint" indicator is visible before starting. Note that Access queries become SharePoint views; a SharePoint view is very similar to an Access query.
When you move the whole database, the existing queries are copied into SharePoint as views. Access users understand that writing queries enables them to see a subset of data. Learning how to do this in a SharePoint View helps you understand how data is organized in a SharePoint list.
One of the most useful views you can create is an Access View. To do this, navigate to the list under which you will create the view, choose Views, then Create View. There you'll see the Access View option that lets you create forms and reports based on a list. When you click on this option and choose the Access database, a dialog like the one in Figure 6 pops up. As you can see, you now define the format (type of form, PivotChart, PivotTable, or Report), and when the process completes, there will be a linked form or report in the Access database.
Figure 6 Creating an Access View (Click the image for a larger view)
Offline Access
In the real world, extending the reach of a mission-critical application translates into return on investment. One way to extend the reach of an integrated Access/SharePoint application is to realize that you do not have to be connected to SharePoint in order to use it. To facilitate such flexibility, Access 2007 introduced the ability to work offline. Information is cached in the local copy of the database until it is attached to the server. In order to take a database offline, click the Work Offline button on the External Data|SharePoint Lists group on the ribbon.
When you are ready to reconnect to SharePoint, you can choose to either Work Online or to Synchronize. Both options upload the data and present conflicts for review; with Synchronize, however, the data is uploaded but the application remains offline.
One other item to be aware of in the SharePoint Lists group is Discard Changes, which has two options. The first discards all changes but doesn't update the SharePoint data in Access. The second option discards all changes and updates the SharePoint data (linked lists).
Access Templates
Access 2007 has several prebuilt templates to assist users with rapid creation of business solutions, as you can see by clicking the SharePoint Lists button under the Create tab in the Tables group (see Figure 7). Choosing one of these automatically defines the schema for a table that is linked into SharePoint. Currently only Contacts, Tasks, Issues, and Events are supported. What this means is that if you export a table that contains one of these types of information, the data will end up as corresponding WSS content type (for example, Issues to Issues).
Figure 7 Choosing the type of list
You can also create custom templates that can be used for SharePoint lists or create SharePoint Site templates with self-linking Access 2007 databases. To do this requires the Access 2007 Developer Extensions and setting the table's WSSTemplateID. This property tracks which tables have a SharePoint list template equivalent.
Access and Metadata
Businesses with entrenched Access applications might not always be able or willing to move all of these applications to SharePoint. In such cases, sometimes it makes sense to write code in Access to update SharePoint. With code, you can glue SharePoint and Access together.
A simple example is the case of the Northwind Trader's database, which stores information about products, suppliers, and customer orders. Each product typically has an associated product sheet supplied by the vendor as a .pdf file. The Access database tracks all the information contained on the product sheet, such as geographic location, product number, revision history, and company department code. This information needs to be available to suppliers and vendors via the SharePoint extranet.
A SharePoint site is set up and all the documents get moved to a linked document library, but the metadata fields are empty (see Figure 8) because the data is tracked in Access (and the person who monitors this is very comfortable with Access and not keen on changing).
Figure 8 Northwind document viewed in SharePoint, with metadata missing (Click the image for a larger view)
Happily, as Figure 9 shows, you can add Visual Basic® for Applications (VBA) code to update the metadata via the Access application. The application doesn't have to be rewritten. The employee gets to continue using a tool that is comfortable while branching out onto the Web, and the vendors can access the extranet based on their permissions (so they see only what they should see), and there are no concurrency or database size issues.
Figure 9 Updating metadata using code (Click the image for a larger view)
Built-In Security
One of the greatest benefits of moving Access applications into SharePoint is being able to take advantage of the SharePoint security model, which has Active Directory® authentication built-in. That means no more worrying how to implement security in Access applications by creating schemas and tables and then adding users. With SharePoint lists, you can make use of security trimming, where users see only what they are allowed to see; this is innate in SharePoint. In addition, if there are components of the database you don't want to move onto SharePoint, you don't have to move them.
SharePoint provides a granular and role-based security model. When determining how and where to move a database, you should design the site and security before migration. It will be easier to set up and test permissions. Also, there will be fewer migration issues and the Access database will perform more efficiently.
Referential Integrity
I would be negligent if I didn't at least broach the subject of referential integrity. The fact is, SharePoint does not enforce referential integrity as Access does. Many installations are normalized at some level. This has to be accounted for in order for these mission-critical Access databases to work. Fortunately, there are a few workarounds that can actually be construed as enhancements to the existing system.
Earlier, I noted that when you move a database to SharePoint, if you moved child tables, the parent tables also move. In addition, lookup fields are used in SharePoint when it recognizes that a field choice is based on another table. For example, suppose a customer is chosen and an order is placed in the Northwind Traders database. If these tables were moved to SharePoint, the customer field would be a lookup in the Order list.
What about cascading updates/deletes and more intricate operations? This is where SharePoint and its workflows should be evaluated as a potential solution. Returning to the Northwind Traders database, suppose a customer calls and cancels an existing order, which then triggers several actions: inventory must be put back, suppliers need to be notified, the employee attached to the order must follow up with the customer, shipping must be canceled, and accounting needs to be updated. That's a lot of moving parts and a lot that must be coordinated.
Utilizing SharePoint Designer, a workflow can be created in SharePoint that accomplishes all of the above as soon as the status of the order changes. There are 22 out-of-the-box workflows that can be mixed and matched into hundreds of actions. SharePoint Designer is a great tool both for branding your site and creating workflows.
The best part is that using SharePoint Designer to create workflows involves skills that are familiar to users who know how to "program" Access. This means that users creating Access applications can transition and extend their scope of skill into workflow creation without too much effort. There is no code writing when it comes to utilizing SharePoint Designer for workflows, and deployment of these workflows is simple.
Peter Serzo is a Senior Engineer for CDW Berbee. He focuses on architecting SharePoint Enterprise solutions for companies around the globe.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.