Extending Access applications with .NET libraries

1. Extending Access applications with .NET libraries

2. .NET and COM Interop • Issues COM does not know anything about .NET framework. The library must meet the COM’s specifications to be consumable for any COM-aware application, including Access.

3. .NET and COM Interop • COM Specifications COM was designed to be language-neutral and easily accessible by variety of clients. COM depends on interfaces and type library (.TLB) to provide information about a given class and how it can communicate with the given class.

4. Scenario You have been contracted by the bank of Zimbabwe to develop an Access application. Given that most transactions involve trillions of dollars, we need to be able to handle numbers larger than long integer.

5. Requirements • Need to be able to do: • Addition • Subtraction • Multiply • Division • Modulo

6. Locating the library • Several libraries are freely available on internet • Sourceforge • Codeproject • Codeplex

7. Examining the library • Object browser • VS’s similarity to VBE • Discovering methods we need • Custom types and portability between two different environments

8. Creating a VS project • We want to use a wrapper class to facilitate the interoperability between .NET library and VBA environment. • Creating a new project • Adding the .NET library as reference • Declaring the assembly as COM-visible

9. Making assembly COM-usable • The compiler will do the minimum necessary to meet COM requirement: • Generate a GUID and ProgID • Implement a empty interface • This will only enable late binding. • Registration is necessary to use it.

10. Imports <namespace> • Analogous to VBA’s Tools -> References • Each namespace has its own set of references so we must declare for our namespace to reference the library

11. Creating a wrapper class • Not the only way to do it; just most simple and effective way. • The wrapper class must not inherit from any other class, guaranteeing that it is a ‘Object’ base type. • Each method should be a simple call or at most a rephrasing of methods used within the library for VBA interoperability.

12. Building the wrapper • For testing, just build from menu. • Build vs. Rebuild • For production, build it to where you want the library to be in each client’s computer to simplify the registration process.

13. Registering the wrapper • Use regasm tool (analogous to regsvr32 tool) • Two flags necessary: /tlb – Usually same name as dll /codebase – register the library path (COM spec) Sample: >regasm mylib.dll /tlb: mylib.tlb /codebase

14. Registering the wrapper • regasm will raise a warning if assembly is not “strong named” (e.g. digitally signed) • Strong naming an assembly requires all references to be strong named as well. • If references cannot be strong named, ignore warning but document the fact!!!

15. Using the wrapper • When the requirement for COM interop are met, there should be at least three files: • The library .DLL file • The wrapper .DLL file • The wrapper .TLB file • Add the .TLB to VBE References • Use it like any other object.

16. Enabling Early Binding • Import System.Runtime.Interop This provides us with more control over how the specifications of class will be exposed to COM • Creating a interface Basically list all methods you want to expose for early binding. No actual codes.

17. Enabling Early Binding • Define attributes for both interface and class to satisfy COM requirements: • GUID • ProgID • Interface Type • DispID

18. Defining attributes • Must be a single statement with the interface/class declaration; use line continuation for readability • Enclosed in <>s and delimited with commas

19. Creating interface • Interface is an abstract object with no actual implementation details. It merely is used to declare to the world what a particular class can do. • Do not use “Public” or “Private” modifier within the interface.

20. Creating interface • List all methods you want to be available for early binding, including its parameters and types for the method itself and the parameters

21. Implements Statement Declare to compiler that a particular class or method will inherit from any other class or interface. For our class, we implement our interface.

22. Implements <interface> • For class, we want to implement the interface. • The class then must have all methods listed in interface; else will have a compile error. • Each method within class then must implement the corresponding method in interface.

23. Implements <interface.method> • For methods in a class, they must in turn implement the method listed in the interface. • Unlike the Implements declaration for class, Implements must be a single statement; use line continuation.

24. Distributing for production • Whenever any Access application references a .NET library, four requirements must be met for each computer running the application: • Library must be in same path as it was recorded in the registry • Each application must have all files used for the library.

25. Distributing for production • Library must be registered on each clients’ computer using regasm • Client computer must have the .NET framework installed. Note that there are two version; one for distributing to clients and one for developing. Use “redistributable” version for production.

26. Conclusion • The general steps to make a library available for Access: • Making it COM-discoverable by either • Using Visual Studio’s provided Interop • Importing Interop and setting attributes • Providing a thin wrapper class • With interface if early binding is desired • Building it to desired location. • Registering it and include .NET framework • Adding it to VBE’s References.