Click here to Skip to main content
Click here to Skip to main content
Go to top

Update TFS WorkItem from Microsoft Access using VBA Code

, 18 Apr 2011
Rate this:
Please Sign up or sign in to vote.
This document is to access TFS from VBA code, since we can't reference Team Foundation Server DLLs from VBS directly.

Introduction

Many IT users are not familiar with C# code or other languages to create small applications. They build their applications using Microsoft Access/Excel because it does not need any programming experience. They can add forms/reports without writing a single line of code. In my organization, some departments like release management and IT testing have small applications developed in Microsoft Access or Microsoft Excel to track their own work, but when the change management implements Team foundation Server and it uses TFS workitem as a repository for work like Task, Bug, etc. workitem, in most cases, it needs a lot of manual work to integrate the data between Access or Excel and TFS and there is no integration between these applications.

Step 1

Create a new C# class library project:

Add System.Runtime.InteropServices references to expose the class as COM to use this class from VBA.

Add these TFS DLL references to use them when managing WorkItems.

  • Microsoft.TeamFoundation.Client
  • Microsoft.TeamFoundation.WorkItemTracking.Client

When you open the calls, the code adds this:

[ComVisible(true), 
ClassInterface(ClassInterfaceType.AutoDual)] //this command to expose the class
public class WorkItemTraking
{

}    

Add the connection to TFS using the below method:

private void TFSconnect()
{
    try
    {
        System.Net.NetworkCredential _credential = 
	(System.Net.NetworkCredential)System.Net.CredentialCache.DefaultCredentials;
        Uri _path = new Uri("http://devprosrv07:8080/tfs/CoreES");
        TfsTeamProjectCollection server = new TfsTeamProjectCollection(_path);

        server.Authenticate();
        store = server.GetService(typeof(WorkItemStore)) as WorkItemStore;

        if (false == server.HasAuthenticated)
        {
            throw new Exception("Could not authenticate while connecting to tfs");
        }
    }
    catch (Exception _exp)
    {
        throw;
    }
}

In this example, I will write two methods. First, read the workitem database on WIQ (workitem Query), the user will pass the query as string and the result is returned as XML.

So I will serialize the datatable to XML:

[ComVisible(true), Description("Get workitem")]
public string GetWorkItem(string WIQuery)
{
    TFSconnect(); //Connect to TFS collection
    DataTable _returnResult;
    try
    {
        WorkItemCollection _workitemcol;
        _workitemcol = store.Query(WIQuery); 	// pass the query and return the result 
					// in collection
        _returnResult = CreateDatatable(_workitemcol); // parse the query to 
						// return it as datatable

        //Serialize DataTable to XML
        StringWriter _streamData = new StringWriter();
        _returnResult.WriteXml(_streamData);

        return _streamData.ToString();
    }
    catch (Exception _exp)
    {
        throw new Exception(_exp.ToString());
    }
}

The second method is to change the workitem fields:

[ComVisible(true), Description("Set workitem")]
public Boolean SetWorkItem(int WIID, string WIField, string Value)
{
    TFSconnect();
    try
    {
        WorkItem _workitem = store.GetWorkItem(WIID); // read WorkItem ID
        _workitem.Fields[WIField].Value = Value;
        _workitem.Save();
        return true;
    }
    catch (Exception _exp)
    {
        throw new Exception(_exp.ToString());
    }
}

The next step is to create and sign an assembly with a strong name using the Assembly Linker. From command prompt, use this command to generate snk file:

sn -k sgKey.snk

From your application, right click on project and select signing tab and browse the generated file:

Save your application and make sure it builds successfully.

Step 2

After we finish building the DLL application that accesses TFS, we need to connect from Access using VBA code, but first we should generate TLB file to reference it from VBA using this command to generate tlb: 

C:\Program Files\Microsoft Visual Studio 9.0\VC> regasm [Source DLL] OutputDLL.tlb /codebase

Now open a new Access project and create a new form and open the Build event.

Select Tools-> References -> add TLB file.

Then, add this code to call the methods in the workitem class:

Public Sub CallTFS()

Dim objectServer As New WorkItemTraking.WorkItemTraking
Dim returnXML As String
Dim returnValue As Boolean
'this method changes the workitem filed value
returnValue = objectServer.SetWorkItem(112, "Impact", "Low")
MsgBox (returnValue)

'this method changes the workitem filed value
returnXML = objectServer.GetWorkItem("SELECT [System.Id], [System.Title] _
FROM WorkItems WHERE [System.Id] in (47492, 47512)
ORDER BY [System.Id]")

MsgBox (returnXML)

End Sub

History

  • 18th April, 2011: Initial post

License

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

Share

About the Author

Ayman Zaza
Systems Engineer NBK
Kuwait Kuwait
Ayman Zaza is a Certified Team foundation Server and Microsoft Test Manager expert, currently is working in National Bank of Kuwait as Process Specialist and ALM/TFS consultant. Also doing TFS and Application life cycle management consultation for other teams in many countries
Follow on   Twitter

Comments and Discussions

 
QuestionAny example of access mdb in winforms? Pinmemberleiyang-ge26-Apr-13 21:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 18 Apr 2011
Article Copyright 2011 by Ayman Zaza
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid