Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / productivity / Office

Connecting Microsoft Word 2007 to a backend database in an Office Business Application (OBA)

2.58/5 (8 votes)
17 Sep 2007CPOL8 min read 1   2.7K  
In this lab, you use drag and drop functionality to add controls to a Word 2007 template, build logic into those controls, protect the content of the controls from editing and deleting, and add simple data binding to a local database. Then, you’ll add a Fluent Ribbon to Word to browse the data.

Screenshot - wordtodb.jpg

Introduction

Office Business Applications (OBA) can be a powerful way to surface data for your users. Because nearly every worker uses Microsoft Office, your users are already familiar with the user interface. But how do you bring the data from your line of business (LOB) application into Word? In this lab, you will walk through the steps to bring data into a Word document and provide a Fluent Ribbon user interface to browse that data.

This lab is in two sequential parts. First, you will bind controls in a Word document to a data source. Next, you will customize the Ribbon with buttons to view the data.

Background

This demo assumes you have the following installed:

You can use other versions of Visual Studio with Visual Studio Tools for Office installed. But Word Content Controls are a feature of Microsoft Office 2007. This demo will also work on Windows Server 2003 and Windows Server 2008 with minor changes in how you start Visual Studio.

This article assumes you have a database in Access. But you can connect the Word Content Controls to SQL Server or to data objects or Web Services. You can use the database provided in the code or build your own.

While this lab uses C#, you can adapt this lab to Visual Basic .NET with minor changes.

Using the code

Most of this article is about how to drag and drop the controls and how a few lines of code will build a powerful application in Word.

Create a document with controls bound to a database

In this lab, you use the drag and drop functionality to add controls to a Word 2007 template and add simple data binding to a local database.

First, you will create a solution that includes a Visual Studio Tools for Office (VSTO) project. This template will allow your users to customize the Word template by selecting the content of predefined areas, based on the data in the database. While this demo is in C#, you can also use Visual Basic. The objective of this process is to open a Word template within Visual Studio.

  1. Click Start, type Visual Studio 2008 in the start search textbox, right-click Visual Studio 2008, click Run as administrator
  2. Click Continue in the User Account Control dialog
  3. Click the File --> New --> Project... menu
  4. In the New Project dialog Project types panel, expand Visual C#, expand Office, click 2007
  5. In the Templates panel, click Word Template
  6. Set the location to your Visual Studio 2008 project folder, such as C:\Users\bruce\Documents\Visual Studio 2008\Projects
  7. Type CCTemplate in the Name textbox
  8. Check Create directory for Solution
  9. Click OK
  10. In the Visual Studio Tools for Office Project Wizard, click OK
  11. A dialog box appears that asks if you want access to Visual Basic for Applications
  12. Click OK

The Visual Studio Toolbox contains Word Controls. You can now add controls to the document.

  1. If the toolbox is not displayed, click View --> Toolbox
  2. Expand Word Controls
  3. Click on the document
  4. Type Company Name followed by a space and drag a RichTextContentControl from the toolbox to the document; press Enter
  5. Type Company Address followed by a space and drag a RichTextContentControl from the toolbox to the document; press Enter
  6. Type Invitation Date followed by a space and drag a DatePickerControl from the toolbox to the document; press Enter
  7. Click the Save disk icon in the document pane

You can adjust the properties of the control.

  1. Click the RichTextContentControl that follows the Company Name text
  2. Press F5 to run your solution
  3. Click on each of the controls and type some data
  4. Close the document without saving

You have added controls that when bound to the data browse event handlers, will allow a user to browse a company database.

You can now reference a specific database and bind the controls to that database.

Create a database in Microsoft Office Access. Create a database named CompanyDatabase with a table named CompanyInfo with four columns:

  • ID AutoNumber
  • CompanyName Text
  • CompanyAddress Text
  • InvitationDate DateTime

Enter some data in the database. Save the database in a known location, not necessarily in the project.

Next, you can bind the Word Content Controls to a database, as you will do in this example, or you can bind them to a Web Service or an object.

  1. Click Data --> Add New Data Source...
  2. Click Database in the Data Source Configuration Wizard
  3. Click Next>
  4. In the Choose Your Connection panel, click New Connection...
  5. In the Add Connection dialog, set the items as follows:
    • Data Source: Microsoft Access Database File (OLE DB)
    • Database file name to the location of your Access database
  6. Click OK
  7. In the Choose Your Connection panel of the Data Source Configuration Wizard, click Next>
  8. When asked if you want to add the database to your solution, click Yes
  9. In the Save the Connection String to the Application Configuration File panel, check Yes, Save the connection as and keep the default connection string; click Next>
  10. In the Choose Your Database Objects, expand Tables, check CompanyInfo
  11. Click Finish

Visual Studio automatically creates a dataset, table adapter, and a binding source. You will use these elements to bind your controls to the data and to manipulate the records in the database.

Next, bind the data to the specific controls. Use the DataBinding property in the Properties pane. Click the Text property and select the specific database record you want to associate with the content controls. You can also drag the specific data elements from the DataSources panel and drop them on the control.

  1. Make the property pane visible by clicking the Properties tab
  2. Click the RichTextContentControl next to the Company Name text in the Properties panel
  3. Expand the (DataBindings) property
  4. Expand companyInfoBindingSource
  5. Click CompanyName
  6. If the Data Sources pane is not visible, click Data --> Show Data Sources
  7. In the Data Sources pane, expand CompanyDatabaseDataSet
  8. Expand CompanyInfo
  9. Click CompanyAddress and drag it to the RichTextContentControl next to the Company Address text in the document panel; the cursor will change to a plus and the control will be highlighted when you can drop the data source onto the control
  10. Click Build --> Build Solution

Create data browse buttons for the Word Ribbon

Next, customize the Ribbon in Word with two buttons, a Next and a Previous button, for your user to browse data. Then, you add event handlers to each of the buttons.

The Ribbon Designer includes a visual designer and an extensibility object model. Office callbacks are mapped to the events on the VSTO Ribbon objects, so developers write event handlers instead of callback methods.

  1. In Solution Explorer, right-click the CCTemplate project. Click Add --> New Item...
  2. In the Add New Item dialog, click Ribbon (Visual Designer) in the Templates panel
  3. Type CCRibbon for the Name
  4. Click Add

You set properties of the ribbon in the Properties pane and drag and drop ribbon controls from the Office Ribbon Controls tab of the toolbox onto the ribbon.

  1. Click the Properties tab to make the Properties pane visible
  2. Click TabAddIns (Built-In)
  3. In the Label property, type Company Info
  4. Click group1 in the CCRibbon.cs ribbon designer
  5. In the Label property, type Browse Data
  6. Expand Office Ribbon Controls in the Toolbox if it isn't already expanded
  7. Drag Button from the Toolbox to the Browse Data box
  8. In the (Name) property, type btnNext
  9. In the Label property, type Next
  10. Drag Button from the Toolbox to the Browse Data box
  11. In the (Name) property, type btnPrevious
  12. In the Label property, type Previous

You can add icons by creating an icon and adding each one to the Resources.resx file that is part of the solution. Then, set the Image property by importing images from the resource file. Set the ShowImage property to true.

When you double-click the button in the Ribbon Designer, the code view for the Ribbon opens and enables you to add the event handler code. Use the Globals object to access the document to access the binding source for the company database.

  1. Double-click the Next button.
  2. Insert this code into the method that is created.
  3. C#
    Globals.ThisDocument.companyInfoBindingSource.MoveNext();
  4. Double-click the Previous button.
  5. Insert this code into the method that is created.
  6. C#
    Globals.ThisDocument.companyInfoBindingSource.MovePrevious();

Now run the solution.

  1. Press F5
  2. Click the Company Data tab to view the Ribbon buttons that you created
  3. Click Next to view the next record
  4. Click Previous to view the previous record
  5. Close the document without saving

Points of interest

You can do a lot with Office Business Applications using very little code.

You have seen how you can browse data in a database or Web Service by binding Word 2007 Content Controls to a data source. You also added a panel in the Word Ribbon bar.

Next steps

Next, you'll want to deploy your document. For more information on the various ways for deployment, see How to: Deploy Office Solutions. You will probably choose the Publish Wizard. For more information on the Publish Wizard, see How to: Deploy Solution Files Using the Publish Wizard.

Conclusion

Special thanks to Steve Fox and Paul Stubbs for their article: Office UI: New VSTO features help you customize Word And Outlook.

Microsoft, Office, Fluent, Word, Access, Windows, Windows Vista, Visual Studio are Microsoft trademarks and registered trademarks.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)