Access makes it easy to call code inside a component built using Visual Basic 6.0 or another COM-based programming language (see Chapter 12). By default, however, Access can't normally call code in a .NET component. Is there some way that Access can call a .NET component created using Visual Basic .NET, Visual C# .NET, or another .NET language?
By default, .NET components can't be called by Access and other COM programs for at least two reasons. First, .NET components are not installed into the registry. In order to automate a component, however, certain registry entries must be present. Second, .NET components aren't COM components; they aren't structured to look and behave like a COM component and they have separate and distinct type systems.
Fortunately, the Microsoft .NET SDK includes a utility, RegAsm.exe, that you can use to create a COM-callable wrapper for a .NET component. RegAsm.exe also registers the .NET component so that it can be called from a COM program such as Access.
One nice feature of .NET is that it only takes a single line of code to determine the current Windows user name. Trying to do this from Access requires at the very least a cumbersome Windows API call.
Follow these steps to create a simple .NET component, UserNameVB, that contains a single class named UserName with a single method, GetUserName, which returns the current user name:
Start Visual Studio .NET.
Create a new VB .NET Class Library project named UserNameVB.
Delete the initial Class1.vb file from the project.
Select Project Add Class... to add a new class file to the project named UserName.cls.
Add a method named GetUserName to the class that returns the Environment.UserName property. The complete code for the UserName class should look like the following:
Public Class UserName Public Function GetUserName( ) As String Return Environment.UserName End Function End Class
Compile the project by selecting Build Build Solution. If all goes well, the status bar will display "Build succeeded."
At this point you could easily create a .NET Windows Form or Web Form application that calls the .NET component. To make it callable from Access, however, you need to use the RegAsm utility to create a COM-callable wrapper component that will call UserNameVB on your behalf. RegAsm also takes care of making the necessary registry entries as well so that Access and other COM programs can see your component.
Follow these steps to make the UserNameVB component callable from Access:
From the Microsoft Visual Studio .NET 2003 Start menu, select Visual Studio .NET Tools Visual Studio .NET Command Prompt to create a Visual Studio .NET command prompt.
Navigate to the folder containing the compiled assembly by using the CD command. By default, the assembly should be found in the following location:
C:\Documents and Settings\<yourusername>\My Documents\Visual Studio Projects\ UserNameVB\bin
Use the .NET registration assembly utility (RegAsm.exe) to register the UserNameVB.dll by entering the following into the command prompt window:
regasm UserNameVB.dll /tlb:UserNameVB.tlb /codebase
RegAsm will display a warning about this being an unsigned assembly but you can safely ignore the warning.
Now you are ready to create the Access application that will call the UserNameVB component. Follow these steps to create an Access form that calls the .NET component:
Create a new Access form named frmGetUserName.
Add a command button to the form named cmdGetUserName and a label named lblUserName.
From the VBA IDE, select Tools References. At the References dialog, select the UserNameVB component (see Figure 17-1).
Attach the following code to the Click event of the cmdGetUserName command button to instantiate the UserNameVB.UserName class and call its GetUserName method:
Private Sub cmdGetUserName_Click( ) Dim objUN As UserNameVB.UserName Set objUN = New UserNameVB.UserName lblUserName.Caption = objUN.GetUserName( ) End Sub
Load and run the form, clicking on the cmdGetUserName command button to return the current user name as show in Figure 17-2.
There is an alternate technique for creating a .NET component that can be called from Access and other COM programs that requires a bit less work than the solution presented here. This solution, however, only works with Visual Basic .NET.
The basic difference with this version of the solution is to create a special type of class library, called a COM Class, that automatically enables it to be called from a COM application. Here are the steps:
Follow Steps 1-3 of the solution.
Select Project Add Add New Item.... At the Add New Item dialog box, select the COM Class template and name the file UserName.cls.
Follow Steps 5-6 of the solution.
Skip Steps 7-9 of the solution. They are no longer necessary.
Follow the remaining steps of the solution.
Not all .NET components can be called from Access and other COM programs. The main limitation is that you can't instantiate any objects for classes containing parameterized constructors. A constructor is code that executes when an instance of a class is created. Constructors are similar in concept to the Class_Initialize event handler within a Visual Basic 6 class. .NET, however, allows you to create constructors that can accept parameters, so-called parameterized constructors. COM, however, has no way to call a class containing a parameterized constructor. If you attempt to create an object from a .NET class that contains a parameterized constructor, you will get a runtime error. A workaround for this issue is presented in topic 17.2.
Another limitation of calling .NET components from Access is that you won't be able to access any properties, methods, or events marked as static (also know as shared). A static member of a .NET class is a member that applies across all instances of a class. Static members cannot be called from Access or other COM programs.
Microsoft Office and .NET Interoperability (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office11012001.asp).