Rich Newman

August 25, 2007

A Beginner’s Guide to calling a .NET Library from Access

Filed under: .net, Access, beginners guide, c#, com interop, introduction, Microsoft Access, technology — richnewman @ 1:04 am

Introduction

In an earlier blog article I described how to call a .NET Library from Excel. I have subsequently received several requests for a similar article dealing with calling .NET from Microsoft Access. This article addresses those requests.

In fact the process and issues are almost identical, which means the two articles overlap heavily. Rather than continually referring to the earlier article, however, I have here included sections from that article verbatim. If you’ve worked through the earlier article you really don’t need to work through this one as well. However, if you are interested in Access and not Excel, this is the place to start.

As with Excel, it’s actually very easy to call a .NET library directly from Access, particularly if you are using Visual Studio 2005, and you don’t need Visual Studio Tools for Office. This article explains how to do this.

A Basic Walk Through

We’ll start by walking through a very basic example. We’ll get Access to call a .NET method that takes a string as input (for example “ World”) and returns “Hello” concatenated with that input string (so, for example, “Hello World”).

1. Create a C# Windows class library project in Visual Studio 2005 called ‘DotNetLibrary’. It doesn’t matter which folder this is in for the purposes of this example.

2. To call a method in a class in our library from Access we simply need a class containing any methods we want to call. For this walk through just copy and paste the following code into our default class file:

using System;
using System.Collections.Generic;
using System.Text;
 
namespace DotNetLibrary
{
    public class DotNetClass
    {
        public string DotNetMethod(string input)
        {
            return "Hello " + input;
        }
    }
}

That’s it: if you look at existing articles on the web, or read the MSDN help, you might think you need to use interfaces, or to decorate your class with attributes and GUIDs. However, for a basic interop scenario you don’t need to do this.

3. Access is going to communicate with our library using COM. For Access to use a COM library there need to be appropriate entries in the registry. Visual Studio can generate those entries for us.

To do this bring up the project properties (double-click ‘Properties’ in Solution Explorer). Then:
i) On the ‘Application’ tab click the ‘Assembly Information…’ button. In the resulting dialog check the ‘Make assembly COM-visible’ checkbox. Click ‘OK’.
ii) On the ‘Build’ tab check the ‘Register for COM interop’ checkbox (towards the bottom: you may need to scroll down).

4. Build the library.

5. Start Access and create a new blank Access database. Call it anything you like. Open the VBA code editor. To do this in Access 2007 go to the Database Tools tab on the ribbon, and then click ‘Visual Basic’ at the left end. In earlier versions of Access go to Tools/Macro/Visual Basic Editor.

6. We now need to include a reference to our new library. Select ‘References’ on the Visual Basic Editor’s ‘Tools’ menu. If you scroll down in the resulting dialog you should find that ‘DotNetLibrary’ is in the list. Check the checkbox alongside it and click ‘OK’.

7. Now add a new code module. You can do this with the Insert/Module command on the menu. Paste the VBA code below into the code window for the module:

Private Sub TestDotNetCall()
Dim testClass As New DotNetClass
MsgBox testClass.DotNetMethod(“World”)
End Sub

8. Click anywhere in the code you’ve just pasted in and hit ‘F5’ to run the code. You should get a ‘Hello World’ message box.

Getting Intellisense Working in Access

Whilst the VBA code above compiles and executes, you will discover that intellisense is not working in the code editor. This is because by default our library is built with a late binding (run-time binding) interface only. The code editor therefore doesn’t know about the types in the library at design time.

There are good reasons for only using a late-bound interface by default: with COM versioning libraries can become difficult with early-bound interfaces. In particular, if you change the early-bound interface by adding, for example, a method in between two existing methods you are likely to break existing clients as they are binding based on the order of the methods in the interface.

For similar reasons you are heavily encouraged to code your interface separately as a C# interface and then implement it on your class, rather than using the default public interface of the class as here. You then should not change that interface: you would implement a new one if it needed to change.

For more on this see:

http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.classinterfaceattribute(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.classinterfacetype(VS.80).aspx

However, we can build our library to use early bound interfaces, which means intellisense will be available. To do this we need to add an attribute from the System.Runtime.InteropServices namespace as below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
 
namespace DotNetLibrary
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DotNetClass
    {
        public DotNetClass()
        {
        }
        public string DotNetMethod(string input)
        {
            return "Hello " + input;
        }
    }
}

If you change your code as above it will expose an ‘AutoDual’ interface to COM. This means it is still exposing the late-bound interface as before, but now also exposes an early-bound interface. This means intellisense will work.

To get this working:

1. Come out of Microsoft Access. Access will lock the DotNetLibrary dll and prevent Visual Studio from rebuilding it unless you close it. Remember to save your new code module.

2. Go back into Visual Studio, change the DotNetClass as shown above, and rebuild the library.

3. Re-open your Access database. Once again if you are using Access 2007 there is an extra step: you need to explicitly enable macros. A warning bar will appear beneath the ribbon saying ‘Certain content in the database has been disabled’. Click the ‘Options’ button next to this, select ‘Enable this content’, and click OK.

4. Access can get confused about the interface changes unless you re-reference the library. To do this go to Tools/References. The DotNetLibrary reference should be near the top of the list now. Uncheck it and close the window. Now open the window again, find the library in the list, and re-check it (trust me, you need to do this).

5. Now run the code and it should still work (put a breakpoint in the routine and hit F5).

6. Enter a new line in the routine after the ‘MsgBox’ line, and type ‘testClass.’. When you hit the ‘.’ you should get an intellisense dropdown which shows that DotNetMethod is available. See below.

Intellisense in Access

Let me re-iterate that this works and is fine for development, but for release code you are better off using the default late binding interfaces unless you understand the full versioning implications. That is, you should remove the ClassInterface attribute from your code when you do a release.

Deployment

In the example here we are using Visual Studio to register our .NET assembly on the workstation so that Access can find it via COM interop. However, if we try to deploy this application to client machines we’re not going to want to use Visual Studio.

Microsoft have provided a command-line tool, regasm.exe, which can be used to register .NET assemblies for COM interop on client workstations. It can also be used to generate a COM type library (.tlb) separate from the main library (.dll), which is considered good practice in general.

As usual with .NET assemblies you have the choice of strong-naming your assembly and installing it in the GAC, or of not strong-naming it and including it in a local path. If you have strong-named your assembly and installed it in the GAC all you need to do is bring up a Visual Studio 2005 command prompt and run:

regasm DotNetLibrary.dll

If you have not strong-named your assembly you need to tell regasm.exe where it is so that it can find it to register it. To do this you need to run the command below, where c:\AccessDotNet is the path where DotNetLibrary.dll can be found. This works fine, although it will warn you that you should really strong-name your assembly:

regasm /codebase c:\AccessDotNet\DotNetLibrary.dll

Note that you can unregister an assembly with the /u option of regasm.

For more detail on this see http://msdn2.microsoft.com/en-us/library/tzat5yw6(vs.80).aspx

Debugging into .NET from Access
You may want to debug from Access into your class library. To do this:

1. Using Visual Studio 2005 bring up the Properties window for the class library.

2. Go to the Debug tab and select the ‘Start external program’ option under ‘Start Action’. In the textbox alongside enter the full path including file name to MSAccess.exe for the version of Access you are using (usually in Program Files/Microsoft Office/Office or similar).

3. On the same Debug tab under ‘Command line arguments’ enter the full path including file name to your test database (the .mdb file, or .accdb if you are using Access 2007). Once you’re done it should something like below:

Project Properties for Access

4. Now put a breakpoint in the code (in our example the sensible place is in method DotNetMethod) and hit F5 in the .NET project. The .NET code should compile and Access should start with your database opened. If you now run the VBA code to call the .NET library again, as above, you should find that the code will break at the breakpoint you set in the .NET code.

Conclusion

The original Excel article is currently the most popular article on this blog. If anyone has any feedback on further COM interop topics they would like to see covered please post a comment. Possible topics include marshalling, interface types, or going the other way (calling Excel or Access from .NET).

References:

Index page from MSDN
http://msdn2.microsoft.com/en-us/library/zsfww439.aspx

More on COM Interop from COM clients into .NET:
http://www.codeproject.com/vb/net/MusaExposingCOM.asp

A COM Class Wizard for C#
http://www.codeproject.com/csharp/cscomtemplate.asp

Guidelines for COM Interoperability from .NET
http://blogs.gotdotnet.com/heaths/archive/2005/03/09/391358.aspx

In Defense of regasm /codebase
http://weblogs.asp.net/savanness/archive/2003/05/29/7749.aspx

Excel/.NET versioning problems
http://krgreenlee.blogspot.com/2006/01/software-running-excel-with-net-11.html

About these ads

12 Comments »

  1. Thanks for your article, Rich!

    Comment by Sergio Torres — May 1, 2008 @ 10:23 am

  2. I can’t get the COM to show up in my Access references list.
    If I browse it says can’t load this type
    I tried to regasm into a .tlb that didn’t work either
    I’m stuck way at the beginning – Access doesn’t allow me to reference it
    help! :) thx

    Comment by Thomas Lu — July 28, 2008 @ 10:57 am

  3. For this note:

    ii) On the ‘Build’ tab check the ‘Register for COM interop’ checkbox (towards the bottom: you may need to scroll down).

    I am using Visual Studio 2008 and I can’t find the Build tab in properties, nor the setting for Register for COM Interop…any help?

    Comment by Matt — February 6, 2009 @ 4:06 pm

  4. I found it! It’s on the Compile tab for VS 2008.

    Comment by Matt — February 6, 2009 @ 4:07 pm

  5. I am able to do use the class Lib in Both in Vba and Access. But when i try to return an array from C# class Lib to Vba It fails and throws me an exception on the VBA side.
    here my code
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Runtime.InteropServices;
    using System.Reflection;

    [assembly: ComVisible(true)]
    [assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
    [assembly: AssemblyVersion("1.0.0.0")]
    [assembly: AssemblyFileVersion("1.0.0.0")]

    namespace ExcelFunc
    {
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class Functions
    {
    public double[] Add(double a, double b)
    {
    double[] ar = new double[1];
    ar[0] = a + b;
    return ar;
    }
    }
    }

    Function add_num()
    Dim f As New Functions
    Dim x() As Double
    x = f.Add(1, 1)
    End Function

    Comment by Rakesh — April 24, 2009 @ 4:28 pm

  6. Terrific article! Best I’ve seen covering this. Exactly what I needed. Thanks for puting it together.

    Comment by Rick — May 7, 2009 @ 10:21 pm

  7. I would like to add two things if it helps others in this endeavor. First, my static functions were not accessible to Access. I had to change them to non static before they were usable.

    Secondly, I have a class library that contains two classes. One class is marked external and was originally above my public class. In order for Access to see the public class, I had to move my internal class below the public class.

    I’m not sure why these changes worked but they did.

    Thank You

    Comment by Brian Thomas — May 26, 2009 @ 3:42 pm

  8. I’ve been doing some more digging on this. I’m concerned about all the chatter regarding the ‘AutoDual’ option and the fact that Microsoft does not recommend its use. Unfortunately, using any other option will not work. Any suggestions?

    Comment by Rick — June 18, 2009 @ 4:40 pm

  9. Thanks for your article,
    However I’ve encountered an issue with the regasm.exe
    I’ve tried both signed and non-signed assemblies without success.

    For signed result is – RegAsm : warning RA0000 : No types were registered

    For non-signed the result is (as expected) – RegAsm : warning RA0000 : Registering an unsigned assembly with /codebase can cause your assembly to interfere with othe
    r applications that may be installed on the same computer. The /codebase switch is intended to be used only with signed
    assemblies. Please give your assembly a strong name and re-register it.
    Types registered successfully

    However the assembly doesn’t appear in the ‘References’ in VB editor.

    Please advise, Thanks

    Comment by glik66 — July 15, 2010 @ 5:03 pm

  10. Every COM enabled class must have a public constructor.

    public sub New()
    MyBase.New()
    End Sub

    Then you’ll want to give it a strong key name and register using /codebase switch. Without the codebase switch mscore.dll can’t locate your library. If you omitt strong key name then the dll must be in the same folder as Excel.exe so always give your COM assembly a strong name and use /Codebase switch.

    Comment by Anonymous — March 6, 2011 @ 10:06 pm

  11. This doesn’t work for me.
    Dim testClass As New DotNetClass
    MsgBox testClass.DotNetMethod(“World”) ‘ errors here
    Excel/Access errors with Run-time error ‘429’ ActiveX component can’t create object
    excel/access Intellisense shows the method, I can tick/untick the reference but get the above error on the msgbox line
    Using VS2010UE + .NET2 tried with 4, Access/Excel 2010 x32 (windows 7 x64)
    Works fine if I reference the dll in another VS project i.e. not access/excel.
    Assembly Info Make COM visible is ticked
    Register for COM interop is ticked
    Any ideas would be appreciated.

    Comment by Anonymous — September 9, 2011 @ 1:52 pm

    • Found the answer to my problem (down to 64bit OS again)
      This gave me the idea http://blogs.msdn.com/b/jigarme/archive/2008/04/28/how-to-register-net-assembly-for-com-interop.aspx
      Create a vbs script (test.vbs)
      set c = createobject(“DotNetLibrary.DotNetClass”)
      tested it with : C:\windows\SysWOW64\cscript.exe test.vbs
      this didn’t error, if I ran the default cscript (64bit version fails, just like excel, access), so something to do with 64bit and 32bit.
      ran C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\regasm.exe dotNetLibrary.dll /tlb /codebase
      Suddenly it worked, checked excel and access both now can call the function

      Comment by Anonymous — September 9, 2011 @ 4:58 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: