Strategies for Managing Spreadmarts

Business users are empowered by knowledge—and knowledge comes, in part, from having access to accurate and timely information. It is generally up to the information technology (IT) department to supply this information. But it doesn’t always work out that way.

Definition of a Spreadmart. TDWI used the following definition of a spreadmart in the survey it conducted as part of this report:

  • spreadmart is a reporting or analysis system running on a desktop database (e.g., spreadsheet, Access database, or dashboard) that is created and maintained by an individual or group that performs all the tasks normally done by a data mart or data warehouse, such as extracting, transforming, and formatting data as well as defining metrics, submitting queries, and formatting and publishing reports to others. Also known as data shadow systems, human data warehouses, or IT shadow systems.

In organizations all over the world, business people bypass their IT groups to get data from spreadmarts. Spreadmarts are data shadow systems in which individuals collect and massage data on an ongoing basis to support their information requirements or those of their immediate workgroup. These shadow systems, which are usually built on spreadsheets, exist outside of approved, IT-managed corporate data repositories, such as data warehouses, data marts, or ERP systems, and contain data and logic that often conflict with corporate data. Once created, these systems spread throughout an organization like pernicious vines, strangling any chance for information consistency and reliability. You’ll find them in all industries, supporting all business functions. According to TDWI Research, more than 90% of all organizations have spreadmarts. (See Figure 1.)

Does your group have any spreadmarts?

Spreadmarts often lead to the phenomenon of duelingspreadsheets. Murray Trim, a management accountantwith Foodstuffs South Island Limited, described one suchsituation: “We have had the classic situation of two peoplepresenting ostensibly the same data at a board meeting withdifferent figures, which they got from different spreadmarts.”Donna Welch, a BI consultant at financial holding companyBB&T, talks about the issues of trust that arise from duelingspreadsheets: “We constantly hear our users talk aboutmanagement’s distrust of their reports because multiple peoplecame up with different answers.”

Who and Why. Spreadmarts are usually created by business analysts and power users who have been tasked to create custom reports, analyses, plans, benchmarks, budgets, or forecasts. Often, these analysts—especially those in the finance department and the financial services industry—have become proficient with Microsoft Excel or Microsoft Access and prefer to use those tools to create reports and analyses. As a result, most are reluctant to adopt a new corporate reporting “standard,” which they believe will limit their effectiveness. Change comes hard, especially when it means learning a new toolset and adapting to new definitions for key entities, calculations, or metrics. Executives perpetuate the problem because they don’t want to pay hundreds of thousands of dollars or more to build a robust data infrastructure and deploy enterprise reporting and analysis tools. Instead, spreadmarts proliferate.

Dangers of Spreadmarts

Inconsistent Views. The problem with spreadmarts is that their creators use different data sources, calculations, calendars, data conversions, naming conventions, and filters to generate reports and analyses based on their view of the business. The marketing department views customers and sales one way, while the finance department views them another way. The way the business operates in Germany is different from the way it operates in Brazil. Business units sell the same products with different names, packaging, pricing, and partner channels. When each group manages its own data and processes, it’s nearly impossible to deliver a consistent, enterprise view of customers, products, sales, profits, and so on. These parochial silos of data undermine cross-departmental and business unit synergies and economies of scale.

Excessive Time. In addition, business analysts spend two days a week—or almost half their time—creating spreadmarts, costing organizations $780,000 a year! Instead of analyzing data, these high-priced employees act like surrogate information systems professionals, gathering, massaging, and integrating data. Many executives have initiated BI projects simply to offload these time-consuming data management tasks from analysts.

Increased Risk. In addition, spreadmarts are precarious information systems. Because they are created by business users, not information management professionals, they often lack systems rigor. The problems are numerous:

  • Users often enter data into spreadmarts by hand, which leads to errors that often go undetected.
  • Few spreadmarts scale beyond a small workgroup.
  • Users may create poorly constructed queries, resulting in incorrect data.
  • Spreadmarts may generate system and data errors when they are linked to upstream systems or files that change without notice.
  • Users embed logic in complex macros and hidden worksheets that few people understand but nevertheless copy when creating new applications, potentially leading to unreliable data.
  • There is no audit trail that tracks who changed what data or when to ensure adequate control and compliance.

In short, spreadmarts expose organizations to significant risk. Business people may make decisions based on faulty data, establish plans using assumptions based on incorrect analyses, and increase the possibility of fraud and theft of key corporate data assets.

Not All Bad?

No Alternative. Despite these problems, there is often no acceptable alternative to spreadmarts. For example, the data that people need to do their jobs might not exist in a data warehouse or data mart, so individuals need to source, enter, and combine the data themselves to get the information. The organization’s BI tools may not support the types of complex analysis, forecasting, or modeling that business analysts need to perform, or they may not display data in the format that executives desire. Some organizations may not have an IT staff or a data management infrastructure, which leaves users to fend entirely for themselves with whatever tools are available.

As such, spreadmarts often fill a business requirement for information that IT cannot support in a timely, cost-effective manner. Spreadmarts give business people a short-term fix for information that they need to close a deal, develop a new plan, monitor a key process, manage a budget, fulfill a customer requirement, and so on. Ultimately, spreadmarts are a palpable instantiation of a business requirement. IT needs to embrace what the business is communicating in practice, if not in words, and take the appropriate action. Thus, spreadmarts should not be an entirely pejorative term.

Cheap, Quick, Easy. Moreover, since spreadmarts are based on readily available desktop tools, they are cheap and quick to build. Within a day or two, a savvy business analyst can prototype, if not complete, an application that is 100% tailored to the task at hand. Although the spreadmart may not be pretty or “permitted,” it does the job. And it may be better than the alternative—waiting weeks or months for IT to develop an application that often doesn’t quite meet the need and that costs more than executives or managers want to pay.

Nevertheless, there is a high price to pay for these benefits in the long term. Many executives have recognized the dangers of spreadmarts and made significant investments to fix this problem. However, not all have succeeded. In fact, most struggle to deliver a robust data delivery environment that weans users and groups off spreadmarts and delivers a single version of truth.


Managed BI Environment. The problem with spreadmarts is not the technology used to create them. Spreadsheets and other desktop-oriented tools are an important part of any organization’s technology portfolio. The problem arises when individuals use these tools as data management systems to collect, transform, and house corporate data for decision making, planning and process integration, and monitoring. When this happens, spreadmarts proliferate, undermining data consistency and heightening risk.

The technical remedy for spreadmarts is to manage and store data and logic centrally in a uniform, consistent fashion and then let individuals access this data using their tools of choice. In other words, the presentation layer should be separated from the logic and data. When this is done, business users can still access and manipulate data for reporting and analysis purposes, but they do not create new data or logic for enterprise consumption. At TDWI, we call this a managed business intelligence environment. The goal is to transform spreadmarts into managed spreadsheets. This lets IT do what it does best—collect, integrate, and validate data and rules—and lets business analysts do what they do best—analyze data, identify trends, create plans, and recommend decisions.

BI vendors are starting to offer more robust integration between their platforms and Microsoft Office tools. Today, the best integration occurs between Excel and OLAP databases, where users get all the benefits of Excel without compromising data integrity or consistency, since data and logic are stored centrally. But more needs to be done.

Change Management. Applying the right mix of technology to address the spreadmart problem is the easy part. The hard part is changing habits, perceptions, behaviors, processes, and systems. People don’t change on their own, especially when they’ve been successful with a certain set of tools and processes for analyzing data and making decisions. Changing a spreadmart-dependent culture usually requires top executives to both communicate the importance of having unified, consistent, enterprise data, and to apply incentives and penalties to drive the right behaviors. Ultimately, change takes time, sometimes a generation or two, but the right organizational levers can speed up the process.

Aligning Business and IT. Another dynamic driving spreadmarts is the lack of communication and trust between business and IT. The business doesn’t adhere to the architectural standards and processes designed to support its long-term interests, while IT doesn’t move fast enough to meet business needs. To reverse this dynamic, both business and IT must recognize each other’s strengths and weaknesses and learn to work together for the common good. IT must learn to develop agile information systems that adapt quickly to changing business conditions and requirements. The business must recognize the importance of building sustainable, scalable solutions. IT must learn about the business and speak its language, while the business must not blame IT for failures when it continually underfunds, overrides, and hamstrings IT so that it cannot possibly serve business needs.

Recognizing that you have a spreadmart problem is the first step. Most of the people we surveyed know their organizations have spreadmarts, but they don’t know what to do about them.

The survey presented respondents with nine different approaches to addressing the spreadmart issue. (See Table 1.)

What strategies have you employed to remedy the problems caused by spreadmarts, and how effective were they?

Table 1. Respondents could select more than one response.

Ironically, the most common approach that organizations use is simply to leave the spreadmarts alone. But as with everything else in life, ignoring a problem does not make it go away, and often makes it worse. When asked how effective this approach was, a majority (58%) said “not very effective.”

Replace with BI Tools. The next most popular approach is to “provide a more robust BI/DW solution,” employed by almost two-thirds of respondents (63%). This approach was considered “very effective” by 24% of respondents. BI software has progressed from best-in-class niche products to BI platforms that provide integrated reporting, analysis, visualization, and dashboarding capabilities within a single, integrated architecture. In addition, many BI vendors now offer planning, budgeting, and consolidation applications to supplement their BI offerings.

We recommend caution with these BI replacement approaches. First, don’t assume that business users will find the BI tools easy to use. Second, don’t assume that business users will see the benefit of these systems if their spreadmarts are answering their business questions today. Get business users (not just power users) involved in the selection and implementation of BI tools, provide ongoing training, and market the benefits. “If it ain’t broke, don’t fix it”—if the business users are not committed to using the BI tools, walk away from the project and look for other spreadmarts the business perceives as a problem.

Create a Standard Set of Reports. Almost as many companies (58%) assumed that creating a standard set of reports using their standard BI tools would eliminate the need for spreadmarts as those that implemented new BI tools (63%). Organizations assumed that these reports would become their systems of record for decision making. Only 18% found this approach very effective. The most likely reasons for the shortcoming were, first, that no set of reports will effectively cover every management decision, so there was a gap in what was provided. Second, since this approach burdened IT with a queue of reports to develop, the business faced two of the primary reasons spreadmarts were created initially: the IT group did not understand what the business needed, and the IT group was not responsive to business needs.

Excel Integration. The only approach respondents rated more effective than adopting BI tools was “providing BI tools that integrate with Excel/Office” (29%). For a spreadmart user, the next best thing to Excel is Excel that integrates with the corporate BI standard. This approach was used by slightly more than half of the respondents (53%). However, Office integration technology can also provide users more fuel to proliferate spreadmarts if it enables users to save data locally and disseminate the results to users. Some BI vendors—and ironically, Microsoft is one of them—now provide a thinclient Excel solution where administrators can deny users the ability to download or manipulate data.

Some experts claim that power users use BI tools mainly as a personalized extract tool to dump data into Excel, where they perform their real work. According to our survey, that’s not the case. Only a small percentage (7%) of spreadmarts obtain data this way. More than half of spreadmarts (51%) use manual data entry or manual data import. It follows that a major way to drain the life out of spreadmarts is to begin collecting the data they use in a data warehouse and create standard reports that run against that data. Of course, if there are no operational systems capturing this data, then a spreadmart is the only alternative.

Sometimes strong-arm tactics are effective in addressing spreadmarts. Reassigning the creators of spreadmarts to other activities is certainly effective, if an executive has the clout to carry this out and offers a suitable BI/DW replacement system. For example, the director of operations at a major national bank reassigned 58 people who were creating ad hoc performance reports with a set of standard reports created using a standard BI platform, saving $300 million a year and dramatically improving the bank’s quality and efficiency in industry benchmarks. This may be the dream of those who are hostile to spreadmarts, but the survey illustrates that this is a rare occurrence.

Gentler approaches are seldom very effective. New policies for the proper use of spreadsheets generally fall on deaf ears; they are very effective only 12% of the time. The problem isn’t that business people do not know how to use the spreadsheets, but that they think they have no alternative.

Multiple Solutions. Given the low percentage of respondents who can vouch for the effectiveness of any of the approaches listed in Table 1, it’s not surprising that managing the proliferation of spreadmarts is such a difficult task. It is more of a change management issue than a technological one. While it’s important to bring new technologies to bear, such as BI tools that integrate with Excel, it’s critical to figure out which levers to push and pull to change people’s habits and perceptions. No single approach is effective on its own; therefore, organizations must apply multiple approaches.


Spreadsheets are here to stay. Business users have them, are familiar with them, and will use them to do their jobs for years to come. Memo to IT: Deal with it! Our recommendation is to choose a solution that balances business and IT priorities and yields the greatest business value.

Building your own app without any coding

I am interested in trying out these IDE that are targeted towards non-programmers. I think that Microsoft Access is easily one of the most productive tools ever built.. And it was targeted towards non programmers.

I detest Access as a database… But as an IDE it really filled a nice role when Microsoft betrayed millions of Visual Basic programmers by inventing Y.A.C.L. – my acronym for Yet Another Crappy Language.. I would have LOVED to get into C# ten years ago.

I just don’t see the point of throwing everything away and learning a new language.. Without being able to use PolyMorphism.  C# has always seemed like a half-baked solution to me. It can’t write Macros in Office.. It couldn’t do batch files until just recently.. PowerShell to me is just another replacement for BATch files..  I always liked how VBS worked.. I don’t see the value in forklift upgrades I guess.

my favorite Windows API calls in VB

When a VBA solution falls short of your needs, there’s probably a Windows API function that can handle the job. Susan Harkins lists 10 handy APIs and explains how you can put them to work.

Office developers rely heavily on VBA. But even though it’s versatile and robust, it doesn’t do everything. Sometimes, a VBA solution is convoluted or difficult to implement. When that happens, turn to the Windows Application Programming Interface (API). You’ll find thousands of useful functions. Although Office developers should find the APIs in this article useful, they’re not just for Office. You can use them in most any Windows-based application. (All of these tips are specific to 32-bit systems.)

The VBA functions provided in this article aren’t real-world ready. They are simple calls to the API, so you can see how the pieces work together. However, a few could easily move into your code library as is. Execute the VBA procedures from the Immediate window, passing the necessary arguments, to see the results. Once you know how the VBA procedures call the API functions and what to expect in return, you can modify the procedures as necessary and use them in your own projects.

Note: This article is also available for download as a PDF, along with a module containing the code examples discussed here.

1: Sleep

The Sleep function suspends execution for a specified period. It places the running code into an inactive state for the number of milliseconds passed to the function. Simply declare the function and then call it as shown from a VBA procedure:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sleep milliseconds

2: GetUserName

If you need to know who’s logged into an Access database, use GetUserName. There are other ways to do this without calling an API, but this API is so simple, why would you bother writing your own code? GetUserName retrieves the name of the current system or the current user logged into the network. Declare it and call it, passing the appropriate information, as follows:

Private Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function apicGetUserName() As String
  'Call to apiGetUserName returns current user.
  Dim lngResponse As Long
  Dim strUserName As String * 32
  lngResponse = GetUserName(strUserName, 32)
  apicGetUserName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function

3: GetComputerName

This next function, GetComputerName, is similar to GetUserName except it retrieves the system’s name. Declare it and call it, as follows:

Private Declare Function GetComputerName Lib "kernel32" Alias _
 "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function apicGetComputerName() As String
  'Call to apiGetUserName returns current user.
  Dim lngResponse As Long
  Dim strUserName As String * 32
  lngResponse = GetComputerName(strUserName, 32)
  apicGetComputerName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function

4: BringWindowToTop

This API function brings the specified window to the top. If the window is a top-level window, the function activates it. If the window is a child window, the function activates the associated top-level parent window. Simply pass the appropriate window handle. If the function fails, it returns 0; if it’s successful, it will return a nonzero value. Use the following declaration:

Private Declare Function BringWindowToTop Lib "user32" _
 (ByVal lngHWnd As Long) As Long

When calling the function, pass the window handle value as a Long variable.

5: FindWindow

BringWindowToTop requires a handle value. You’ll need FindWindow, another API, for that. This one can be a bit frustrating because it requires specialized information, and if you don’t get it just right, the function won’t work. Specifically, you need the window’s class or name, as shown in the simple call below. This function returns the handle for the Word document, December2010.docx.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Function apicFindWindow(strClassName As String, strWindowName As String)
  'Get window handle.
  Dim lngWnd As Long
  apicFindWindow = FindWindow(strClassName, strWindowName)
End Function

The class names for the three main Office applications are as follows:

  • Microsoft Access — OMain
  • Microsoft Excel — XLMAIN
  • Microsoft Word — OpusApp

The window name (lpWindowName) is usually the window’s caption; see the window’s title bar. If you’re lucky, passing the object in the following form will work:


For instance, you might try the following statement to return the handle of an Excel userform named ufrmEmployees:

FindWindow(vbNullString, ufrmEmployees.Caption)

Unfortunately, it doesn’t always work out that way. If the function returns 0, it didn’t work — a window handle is never 0. If you omit one of the parameters (both aren’t necessary), pass vbNullString.

If you have trouble getting the lpWindowName property, grab a copy of AutoIt. This tool returns the exact window name for open windows. Thanks to Stuart McLachlan of Lexacorp for recommending this neat tool!

6: FindExecutable

Have you ever needed to know what’s installed before running a specific Office application? FindExecutable can retrieve that information. This function requires the name of an existing data file and a working directory. The function returns the name and path of the application that Windows would launch if you double-clicked the data file. If the function fails, it returns a value of 32 or less. Declare it and use the following VBA function to call FindExecutable:

Private Declare Function FindExecutable Lib "shell32.dll" Alias _
 "FindExecutableA" (ByVal lpFile As String, ByVallpDirectory As String, _
 ByVal lpResult As String) As Long

Function apicFindExecutable(strDataFile As String, strDir As String) As String
  'Resturns exectuable for passed data file.
  Dim lgnApp As Long
  Dim strApp As String
  strApp = Space(260)
  lngApp = FindExecutable(strDataFile, strDir, strApp)
  If lngApp > 32 Then
    apicFindExecutable = strApp
    apicFindExecutable = "No matching application."
  End If
End Function

You must pass the entire path via strDataFile, not just the name of the data file. The function will return the path to the executable needed to run the data file.

7: GetActiveWindow

When working with more than one Office application, you’ll probably need to consider windows. Fortunately, there are plenty of API functions for working with them. GetActiveWindow retrieves the window handle for the currently active window — the new window you last clicked. If there is no active window associated with the thread, the return value is NULL. Declare it and call it as follows:

Private Declare Function GetActiveWindow Lib "user32" () As Long

Function apicGetActiveWindow()
  'Return window handle of active window.
  Dim lngWindow As Long
  apicGetActiveWindow = GetActiveWindow()
End Function

You can quickly learn whether a specific form is still the active window using the following expression if the host application supports a handle property (Access does):

GetActiveWindow() = | <> form.hWND

8: GetTempPath

GetTempPath returns the path to the system’s temporary folder. It requires two parameters: the length of a string to contain the pathname and the string itself. This function returns the length of the pathname measured in bytes or 0 if the function fails. Declare it and call it as follows:

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
 (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Public Function apicGetTempPath() As String
  'Returns path to system's temporary folder.
  Dim strPath As String * 512
  Dim lgnPath As Long
  lgnPath = GetTempPath(512, strPath)
  apicGetTempPath = Left(strPath, InStr(1, strPath, vbNullChar))
End Function

9: GetTempFileName

This function often works as a companion to GetTempPath. You might not use it often, but when you need it, you’ll find it handy. GetTempFileName creates a name for a temporary file. This function has four parameters: a string for the path for the file, a string prefix used to begin a unique filename, a unique number to construct the temporary name, and a string used to return the filename. Both the path and prefix strings are required and can’t be empty. The function returns the unique number used to create the file name or 0, if there’s an error. Declare it and call it as follows:

Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA"

(ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As

Long, ByVal lpTempFileName As String) As Long

Public Function apicGetTempFileName(str As String) As String
  'Retrun a temporary file name.
  Dim strPath As String * 512
  Dim strName As String * 576
  Dim lngRet As Long

  lngRet = GetTempPath(512, strPath)
  If (lngRet > 0 And lngRet < 512) Then
     lngRet = GetTempFileName(strPath, str, 0, strName)
     If lngRet <> 0 Then
        apicGetTempFileName = Left(strName, _
           InStr(strName, vbNullChar))
     End If
  End If
End Function

The above function combines GetTempPath and GetTempFileName to return a temporary filename. Simply pass a prefix and the function generates a unique name using the prefix.

10: GetDesktopWindow

This function retrieves a handle to the desktop window, which covers the entire screen. All other windows are drawn on top of the desktop window. This is one of the easier functions to implement, as there are no parameters. You’ll seldom use it alone; rather, you’ll combine it with other API functions. For instance, you might combine it with others so you can temporarily drop files onto the desktop or enumerate through all the open windows on the desktop. Declare it and call it as follows:

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Public Function apicGetDesktopWindow()
  'Retrieves the handle for the desktop.
  apicGetDesktopWindow = GetDesktopWindow
End Function

11: ShowWindow

After retrieving a window’s handle using FindWindow, you might want to manipulate the same window using ShowWindow. There are usually native methods you should use instead, but the capability exists — and sometimes, the native solutions just don’t provide what you want. Declare it and call it as follows:

Private Declare Function ShowWindow Lib "user32" _
 (ByVal hwnd As Long, ByVal nCmdSHow As Long) As Long

Function apicShowWindow(strClassName As String, strWindowName As String, lngState As Long)
  'Get window handle.
  Dim lngWnd As Long
  Dim intRet As Integer

  lngWnd = FindWindow(strClassName, strWindowName)
  apicShowWindow = ShowWindow(lngWnd, lngState)
End Function

The VBA function calls both FindWindow and ShowWindow. The third argument, lngState passes one of the constants shown inTable A.

Table A












































































































































Integer Value





SW_FORCEMINIMIZE 11 Minimizes a window.
SW_HIDE 0 Hides the window and activates another window.
SW_MAXIMIZE 3 Maximizes a window.
SW_MINIMIZE 6 Minimizes the specified window and activates the next top-level window.
SW_RESTORE 9 Activates and displays the window.
SW_SHOW 5 Activates the window.
SW_SHOWMAXIMIZED 3 Activates the window and displays it as a maximized window.
SW_SHOWMINIMIZED 2 Activates the window and displays it as a minimized window.
SW_SHOWMINNOACTIVE 7 Displays the window as a minimized window (without activating the window).
SW_SHOWNA 8 Displays the window in its current size and position (without activating the window).
SW_SHOWNOACTIVATE 4 Displays a window in its most recent size and position (without activating the window).
SW_SHOWNORMAL 1 Activates and displays a window.

VSTO Add-ins for Access?

I still don’t understand why this isn’t fully supported. Is Visio and Project _REALLY_ more popular than MS Access? I don’t think so!?!?!


Well here’s a work around, looking forward to trying this out!


Armed with this information, you can see it would be pretty easy to create a VSTO add-in for say Access. I could start off with an add-in for any of the supported hosts, and then make a few minor adjustments to turn it into an add-in for an unsupported host. Detailed steps follow…

1. To start building an Access add-in, I could create a Word (or InfoPath, PowerPoint, Project or Visio) add-in (Excel or Outlook would also work, but they have additional redundant host-specific code).

2. Then, I’ll add a reference to the Microsoft Access Object Library, on the COM tab (this also pulls in references to ADODB and DAO). It also pulls in Microsoft.Office.Core.dll, which duplicates the Office.dll already referenced by default – so I’ll delete one of these two duplicates.

3. In Solution Explorer, I can select the project and click the “show all files” button. This makes it easier to open the ThisAddIn.Designer.cs file – here I can change the declaration and initialization of the Application field from M.O.I.Word.Application to M.O.I.Access.Application. Note that this step changes a file that is auto-generated: the file is not normally re-generated, but can be if I corrupt the project (the point being that my manual changes will be lost if the file is re-generated):

//internal Microsoft.Office.Interop.Word.Application Application;

internal Microsoft.Office.Interop.Access.Application Application;

//this.Application = this.GetHostItem<Microsoft.Office.Interop.Word.Application>

(typeof(Microsoft.Office.Interop.Word.Application), “Application”);

this.Application = this.GetHostItem<Microsoft.Office.Interop.Access.Application>

(typeof(Microsoft.Office.Interop.Access.Application), “Application”);

4. That’s all the code changes. Now for the project changes. There are two ways to do these changes – through the IDE in a way that overrides or counters the default settings; or by manually editing the .csproj file directly, to replace the default settings. Let’s look at both approaches: first through the IDE, then manually.

5. First, I’ll change the Project Properties | Debug | Start action, to “Start external program”, and specify the path to Access, for example:

C:Program Files (x86)Microsoft OfficeOffice12MSACCESS.EXE

6. Then, I’ll create a .reg file with the same name as my add-in solution, and put it in the solution folder. This reg file is used to register the add-in for Access (and unregister it for Word). The example reg file listed below is simply a dump of what the standard VSTO build task does for each add-in type, with an additional line. The additional line (the first reg entry below) simply removes the entry that the build task puts in for Word. The remaining entries are identical for Word and Access, with the only changing being to replace “Word” with “Access”:

Windows Registry Editor Version 5.00







7. In Project Properties | Build Events, I add a Post-build event commandline to merge the .reg file into the registry:

regedit /s “$(SolutionDir)$(SolutionName).reg”

8. That’s it. I can now press F5 to build the solution: this will register the add-in for Access, and run Access for debugging with the add-in loaded.

9. Note that instead of setting the Debug property to an external program (step 4 above), I could modify the .csproj file directly, to set the <ProjectProperties DebugInfoExeName> from Word to Access. For example, change this:

<ProjectProperties HostName=”Word” HostPackage=”{D2B20FF5-A6E5-47E1-90E8-463C6860CB05}” OfficeVersion=”12.0″ VstxVersion=”3.0″ ApplicationType=”Word” Language=”cs” TemplatesPath=”” DebugInfoExeName=”#SoftwareMicrosoftOffice12.0WordInstallRootPath#WINWORD.EXE” AddItemTemplatesGuid=”{147FB6A7-F239-4523-AE65-B6A4E49B361F}” />

… to this:

<ProjectProperties HostName=”Access” HostPackage=”{D2B20FF5-A6E5-47E1-90E8-463C6860CB05}” OfficeVersion=”12.0″ VstxVersion=”3.0″ ApplicationType=”Access” Language=”cs” TemplatesPath=”” DebugInfoExeName=”#SoftwareMicrosoftOffice12.0AccessInstallRootPath#MSACCESS.EXE” AddItemTemplatesGuid=”{147FB6A7-F239-4523-AE65-B6A4E49B361F}” />

Note that changing the <ProjectProperties HostName> value, as show above, changes the icons used in the Solution Explorer .

10. I could also change the <Host> element’s Name value to change the name of the parent node for the ThisAddIn.cs in the Solution Explorer. Change this:

<Host Name=”Word” GeneratedCodeNamespace=”MyAddIn” IconIndex=”0″>

<HostItem Name=”ThisAddIn” Code=”ThisAddIn.cs” CanonicalName=”AddIn” CanActivate=”false” IconIndex=”1″ Blueprint=”ThisAddIn.Designer.xml” GeneratedCode=”ThisAddIn.Designer.cs” />


… to this:

<Host Name=”Access” GeneratedCodeNamespace=”MyAddIn” IconIndex=”0″>

<HostItem Name=”ThisAddIn” Code=”ThisAddIn.cs” CanonicalName=”AddIn” CanActivate=”false” IconIndex=”1″ Blueprint=”ThisAddIn.Designer.xml” GeneratedCode=”ThisAddIn.Designer.cs” />


11. Also, registration is determined by the <OfficeApplication> element value. So, instead of setting up a .reg file as a post-build task (steps 5-6 above), I could edit the .csproj directly to change this:


…to this:


It should be obvious from all this that we designed the AddIn base class and the add-in projects to be almost completely host-agnostic. This is what allowed us to add support for so many Office hosts in the very rapid VSTO 2005 SE release, and to add support for Project in the VS 2008 release at the same time as adding a whole raft of new functionality. From a development perspective, each additional Office host application is a minor amount of work. So, why didn’t we simply add add-in projects for all Office hosts?

There are two main reasons. First, there is a lot less demand for Access, FrontPage, Publisher and SharePoint Designer add-ins compared to the demand for add-ins for the hosts that we do support. Second, while the dev cost is small for each host, the test cost is huge. If you consider the permutations of test (all the Office 2003 apps, all the Office 2007 apps, running on XP, on Windows 2003, on Vista, with varying levels of SP, both x86 and 64-bit, with all the other variants such as other managed/unmanaged add-ins loaded or not, VSTO doc-level customizations also loaded or not, different load sequences, with/without custom task panes, with/without ribbon customization, etc etc), you can see the matrix rapidly balloons to unmanageable proportions. This trade-off always applies when releasing general-purpose software: somewhere you make a compromise between the features you can build and the features you can support (that is, the features you have the time+resources to test thoroughly enough to support them).

In this post, I’ve explored the largely host-agnostic nature
of VSTO add-in projects, by converting a Word add-in into an Access add-in. Note, however, that I’m not encouraging people to use this approach in production – we have not tested this behavior, and it is expressly not supported in any way. What I’ve done is to explore how VSTO is designed to be optimally host-agnostic, so that the add-in model is as flexible as possible – without going to the extreme of loose typing offered by the old “shared” add-in model.

I detest Oracle, it is a halfwit database for morons. My first job outta college, I had six Oracle dev/dba at this HP funded startup, and they couldn’t do simple ecommerce. I just think that Microsoft olap owns the market, and any company not using olap is stupid. I just wish that Microsoft would include vertipaq and analysis services with ms access.. office web components, a piece of microsoft access are easily better ten years ago than anything that Google spreadsheets does today.

Microsoft Access 2012 Wish-List

I ran into this on the internet.. I think that it’s funny.. because I think that Access Data Projects include all 5 of these features already:

Q: New Feature 1 – Recording Macros
A: I use SQL Profiler for this!!! It works great, especially on large Access conversions!

Q: New Feature 2 – One, better macro language
A: I use TSQL for this! IF WHEN THEN BEGIN WHILE, that’s all done on the SQL Server side, buddy!

Q: New Feature 3 – Get rid of the bugs
A: I stopped using Jet 10 years ago because it’s not stable, it’s not possible to stabilize.

Q: New Feature 4 – Include an SQL Editor
A: Access Data Projects -IS- a SQL Editor!

Q: New Feature 5 – Deter Casual Users
A: Access Data Projects take about an extra week to teach developers to be productive. BFD, It’s worth it.

Microsoft Access 2012 Wish-List – Five Things We’d Like to See in the Next Version of MS Access
By Andy Brown
Dec 21, 2010
Sometimes it’s not easy to see the database wood for the trees. This article attempts to stand back and consider 5 features which Microsoft could include in the next version of Access (2011? 2012?).

New Feature 1 – Recording Macros

Microsoft, the time has come! You can record Visual Basic macros in Word and Excel, and you used to be able to record in PowerPoint too until recently. We recognise that it wouldn’t be an easy feature to include, but it would make writing VBA macros so much easier!

New Feature 2 – One, better macro language

You keep trying to improve the Access macro language, adding better comments and much more in Access 2010. Ultimately, however, it’s Visual Basic which provides the power that most developers need.

We’d like to see Microsoft announce that they will no longer invest in the Access macro language, but will work to improve the VBA language. Specifically, please can we get rid of DoCmd and RunCommand, and also abandon the forms![formname]![controlname] convention? Word and Excel VBA has converged: it’s time to bring Access into the fold.

New Feature 3 – Get rid of the bugs

The worst feature of Access, by far, is that it isn’t reliable. Every time that we use it to develop a system, we swear that we will never do it again (and then forget this promise the next time!).

We’d like to see the Compact and Repair feature removed from Microsoft’s next Access version – because it wasn’t needed.

New Feature 4 – Include an SQL Editor

For those who know SQL Server but – understandably – prefer the Access user-interface, a SQL editing window would be a godsend. If we could have it with colour-coding, indentation, comments and autocompletion of commands, so much the better. Basically, couldn’t Microsoft include the SQL Server Management Studio 2008 window within Access?

New Feature 5 – Deter Casual Users

We’re not entirely serious about this last new feature, but …

We’ve seen far too many companies building Access “databases” which consist of a single table, and which would have been better created in Excel. So, how about a feature which displays a warning message when you create a single-table database (or a multiple-table database with no relationships) saying something like “This database might be better created in Excel!”.

If anyone from Microsoft would like to comment on this article, they’d be very welcome!
About the Author
Andy Brown has been building Access databases and running Microsoft Access training courses for Wise Owl Business Solutions for many years. You can see all of the courses that Wise Owl run at

Don't rewrite old code 'just becase it's old'

            Joel on Software

Things You Should Never Do, Part I

by Joel Spolsky
Thursday, April 06, 2000

Netscape 6.0 is finally going into its first public beta. There never was a version 5.0. The last major release, version 4.0, was released almost three years ago. Three years is an awfully long time in the Internet world. During this time, Netscape sat by, helplessly, as their market share plummeted.

It’s a bit smarmy of me to criticize them for waiting so long between releases. They didn’t do it on purpose, now, did they?

Well, yes. They did. They did it by making the single worst strategic mistake that any software company can make:

They decided to rewrite the code from scratch.

Netscape wasn’t the first company to make this mistake. Borland made the same mistake when they bought Arago and tried to make it into dBase for Windows, a doomed project that took so long that Microsoft Access ate their lunch, then they made it again in rewriting Quattro Pro from scratch and astonishing people with how few features it had. Microsoft almost made the same mistake, trying to rewrite Word for Windows from scratch in a doomed project called Pyramid which was shut down, thrown away, and swept under the rug. Lucky for Microsoft, they had never stopped working on the old code base, so they had something to ship, making it merely a financial disaster, not a strategic one.

We’re programmers. Programmers are, in their hearts, architects, and the first thing they want to do when they get to a site is to bulldoze the place flat and build something grand. We’re not excited by incremental renovation: tinkering, improving, planting flower beds.

There’s a subtle reason that programmers always want to throw away the code and start over. The reason is that they think the old code is a mess. And here is the interesting observation: they are probably wrong. The reason that they think the old code is a mess is because of a cardinal, fundamental law of programming:

It’s harder to read code than to write it.

This is why code reuse is so hard. This is why everybody on your team has a different function they like to use for splitting strings into arrays of strings. They write their own function because it’s easier and more fun than figuring out how the old function works.

As a corollary of this axiom, you can ask almost any programmer today about the code they are working on. “It’s a big hairy mess,” they will tell you. “I’d like nothing better than to throw it out and start over.”

Why is it a mess?

“Well,” they say, “look at this function. It is two pages long! None of this stuff belongs in there! I don’t know what half of these API calls are for.”

Before Borland’s new spreadsheet for Windows shipped, Philippe Kahn, the colorful founder of Borland, was quoted a lot in the press bragging about how Quattro Pro would be much better than Microsoft Excel, because it was written from scratch. All new source code! As if source code rusted.


The idea that new code is better than old is patently absurd. Old code has been used. It has been tested. Lots of bugs have been found, and they’ve been fixed. There’s nothing wrong with it. It doesn’t acquire bugs just by sitting around on your hard drive. Au contraire, baby! Is software supposed to be like an old Dodge Dart, that rusts just sitting in the garage? Is software like a teddy bear that’s kind of gross if it’s not made out of all new material?


Back to that two page function. Yes, I know, it’s just a simple function to display a window, but it has grown little hairs and stuff on it and nobody knows why. Well, I’ll tell you why: those are bug fixes. One of them fixes that bug that Nancy had when she tried to install the thing on a computer that didn’t have Internet Explorer. Another one fixes that bug that occurs in low memory conditions. Another one fixes that bug that occurred when the file is on a floppy disk and the user yanks out the disk in the middle. That LoadLibrary call is ugly but it makes the code work on old versions of Windows 95.

Each of these bugs took weeks of real-world usage before they were found. The programmer might have spent a couple of days reproducing the bug in the lab and fixing it. If it’s like a lot of bugs, the fix might be one line of code, or it might even be a couple of characters, but a lot of work and time went into those two characters.

When you throw away code and start from scratch, you are throwing away all that knowledge. All those collected bug fixes. Years of programming work.

You are throwing away your market leadership. You are giving a gift of two or three years to your competitors, and believe me, that is a long time in software years.

You are putting yourself in an extremely dangerous position where you will be shipping an old version of the code for several years, completely unable to make any strategic changes or react to new features that the market demands, because you don’t have shippable code. You might as well just close for business for the duration.

You are wasting an outlandish amount of money writing code that already exists.

Is there an alternative? The consensus seems to be that the old Netscape code base was really bad. Well, it might have been bad, but, you know what? It worked pretty darn well on an awful lot of real world computer systems.

When programmers say that their code is a holy mess (as they always do), there are three kinds of things that are wrong with it.

First, there are architectural problems. The code is not factored correctly. The networking code is popping up its own dialog boxes from the middle of nowhere; this should have been handled in the UI code. These problems can be solved, one at a time, by carefully moving code, refactoring, changing interfaces. They can be done by one programmer working carefully and checking in his changes all at once, so that nobody else is disrupted. Even fairly major architectural changes can be done without throwing away the code. On the Juno project we spent several months rearchitecting at one point: just moving things around, cleaning them up, creating base classes that made sense, and creating sharp interfaces between the modules. But we did it carefully, with our existing code base, and we didn’t introduce new bugs or throw away working code.

A second reason programmers think that their code is a mess is that it is inefficient. The rendering code in Netscape was rumored to be slow. But this only affects a small part of the project, which you can optimize or even rewrite. You don’t have to rewrite the whole thing. When optimizing for speed, 1% of the work gets you 99% of the bang.

Third, the code may be doggone ugly. One project I worked on actually had a data type called a FuckedString. Another project had started out using the convention of starting member variables with an underscore, but later switched to the more standard “m_”. So half the functions started with “_” and half with “m_”, which looked ugly. Frankly, this is the kind of thing you solve in five minutes with a macro in Emacs, not by starting from scratch.

It’s important to remember that when you start from scratch there is absolutely no reason to believe that you are going to do a better job than you did the first time. First of all, you probably don’t even have the same programming team that worked on version one, so you don’t actually have “more experience”. You’re just going to make most of the old mistakes again, and introduce some new problems that weren’t in the original version.

The old mantra build one to throw away is dangerous when applied to large scale commercial applications. If you are writing code experimentally, you may want to rip up the function you wrote last week when you think of a better algorithm. That’s fine. You may want to refactor a class to make it easier to use. That’s fine, too. But throwing away the whole program is a dangerous folly, and if Netscape actually had some adult supervision with software industry experience, they might not have shot themselves in the foot so badly.