How to set up VS 2017 C# to make .dll for Access 2016 reference

[Ivan Starr]

Dear Sirs,

Does anyone know how to setup VS 2017 C# project to make .dll for Access 2016 to be used through Tools->references?

I tried many combos of different options but just could not get anything to work. There is a function in System.Net.Sockets that I need to access in MS Access VBA.

If this is not the right place to post this, please advise where is.

Thanks,

Ivan

 

[NoUserHere]

Hmm, I'm not following, I'm running on fumes here (to many late nights this week) so...
This :

Error Cannot register type library "C:\Users\GL552V\source\repos\ClassLibrary9\ClassLibrary9\bin\Debug\ClassLibrary9.tlb". Error accessing the OLE registry.

Is fixed by using elevated permissions?

If that was correct, then, registration should work. Also be sure you are using the respective regasm version as there are 32 and 64 bit. You can also check your registry or write a file log while registering which shows the registering details.

Regasm.exe (Assembly Registration Tool) - .NET Framework

Use Regasm.exe, the Assembly Registration tool. Read assembly metadata and add the needed entries to the registry, allowing COM clients to create .NET classes.

docs.microsoft.com

Write a log and show us what you get. You are quite likely going to require Skydiver and JohnH's help from here, as most of what I knew about owl vb has slowly faded away. So I won't be much use to you here.

 

[JohnH]

If I recall correctly, static classes cannot be exposed to COM.

That's right, create a regular class with a regular method, no static.

This :
Error Cannot register type library
Is fixed by using elevated permissions?

True.

Also be sure you are using the respective regasm version as there are 32 and 64 bit

The "Register for COM" option automatically registers the COM library on development machine when you build the project. If deploying you need regasm.exe.
As an Office COM library it must be build as x86 (32-bit) if Office is installed as 32-bit, and vice versa.

 

[Ivan Starr]

Yes elevated permissions solved the error, and now I got the warning about no types exposed with the addition of an interface class and some other stuff as follows so that the code looks like this now:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace ClassLibrary9

{



    //[Guid("<PUT-GUID-HERE-1>")]
    //[ComVisible(true)]
    //[ProgId("ClassLibrary9")]
    // These are automatically generated I am told

    public interface iClass1
    {
        double Add(double num1, double num2);
    }


    public class Class1 : iClass1
    {
        public Class1() {
            int n = 1;  // some say this is needed, just ine line of code to avoid a
                        //  compiler optimization that mess up COM
        }

        public double Add(double num1, double num2)
        {
            return num1 + num2;
        }

    }

}

NOW the problem is when I try to make the code work from within Access VBA with the following code,

Sub bazzz()

    Dim zzz As Class1
    Set zzz = New Class1
    Debug.Print zzz.Add(1, 1)

End Sub

, the Set statement gets the dreaded "Automation Error" error.

Any Ideas?

 

[Ivan Starr]

Hmm, I'm not following, I'm running on fumes here (to many late nights this week) so...
This :

Is fixed by using elevated permissions?

If that was correct, then, registration should work. Also be sure you are using the respective regasm version as there are 32 and 64 bit. You can also check your registry or write a file log while registering which shows the registering details.

Regasm.exe (Assembly Registration Tool) - .NET Framework

Use Regasm.exe, the Assembly Registration tool. Read assembly metadata and add the needed entries to the registry, allowing COM clients to create .NET classes.

docs.microsoft.com

Write a log and show us what you get. You are quite likely going to require Skydiver and JohnH's help from here, as most of what I knew about owl vb has slowly faded away. So I won't be much use to you here.

Yeah the elevated permission fixed the error, and I added an "interface" and I got it to compile and load in vba, but when I tried to use it, it got the dreaded "automation error" error ((see above response to Sir JohnH). Got any ideas wut might be goin' on? Something having to do with passing values maybe?

Thanks,

Ivan

 

[Skydiver]

Did you make sure that you match the bit-ness of your Office installation? E.g. If you have 32-bit Office, you need to make sure that you compile (and register) your assembly as an x86 assembly. On the other hand, if you have 64-bit Office, you need to compile (and register) your assembly as an x64 assembly. "Any CPU" is not going to cut it.

It's been almost 20 years since I've had to deal with VBA. As I recall, the dreaded Automation Error also provided a way to get details about the error like an error code as well as some kind of log and/or callstack. Can you help us help you by providing those details?

This is just my opinion based on what I've seen on this thread, but it looks like you just gloss over the various replies that people give you and so we end up having to repeat ourselves. (ex. running as admin, how to make COM visible, using non-static classes, etc.) If you see something you don't understand, ask about it. Don't just blow it off and head on blindly.

 

[NoUserHere]

It's been almost 20 years since I've had to deal with VBA.

Thank you for making me feel more ancient. ?

 

[JohnH]

, the Set statement gets the dreaded "Automation Error" error.

I tried your code in a new Class Library project, just checked the two checkboxes "register COM" and "COM-visible" and build for x86 (for 32-bit Office). I could then add the reference in Word/Excel VBA and use it successfully with same VBA code as you posted. I'm not aware of "Automation Error", but have not worked much with this stuff either. If something just got messed up you could try "Clean Solution" in VS, this will unregister the COM and remove all built files to allow a fresh start.

The only thing I wanted to add was this attribute to the class, in order to get intellisense (from explicit interface) in VBA code editor:

[ClassInterface(ClassInterfaceType.None)]

Why is explained in this article: COM Callable Wrapper
and these help documents: ClassInterfaceType Enum (System.Runtime.InteropServices) ClassInterfaceAttribute Class (System.Runtime.InteropServices)

 

[JuggaloBrotha]

I have a different approach question, you say you need this C# code to be available to the Access database, is the database also housing the application stuff too? IE forms, reports, etc?
If so, what would it take to replicate that in a c# app?

Reason I ask is because using a C# dll in Access means you have to have .Net install which means you could just make a .Net app that uses the Access database for the data storage and I believe the reports, but would have to confirm that.

 

[Skydiver]

In the OP, he stated:

There is a function in System.Net.Sockets that I need to access in MS Access VBA.

If there is only one function that he needs, I wonder if it would be easier to just use VBA's equivalent of P/Invoke and instead of calling the .NET function, to call the OS function directly.

@Ivan Starr : What is that one function that you need to access?

 

[Ivan Starr]

Mr. Skydiver,

This bit-ness thing is something new to me - I will have to look into that now. When I registered the .dll it did seem to work but maybe actualy running it is another thing. I used the 64-bit version (the 2nd time, after realizing there were 2 different versions).

I'll got those details now... -2147024894(80070002) <-- that's all the error said...

I tried to answer everyone who replied definitively. I am not aware that I just glossed over anything. I believe I have tried to implement everything I hav been told about and then reported what happened. There obviously were many problems to be solved and hey it looks like I'm closer thanks to you all and I'm really thankful. You would think that doing this would be easier, being it's 2 of Microsoft's largest products... I do not think I blew off anyone and if I did I did not mean to.

AWESOMMMMMMMMMME! Dude, you just gave me the last thing I needed to make it work! I set the platform target to x64 and wham-bam! Then it worked! I took a pic of it so you can share in the thrill of victory! So uh, there's a bit of prof I was assiduous - I implemented everything everyone told me! I'm so proud, of us all!

Seriously dude, thanks!

And thanks again to everyone else, u guys are awesome!

Ivan

 

[Ivan Starr]

In the OP, he stated:

If there is only one function that he needs, I wonder if it would be easier to just use VBA's equivalent of P/Invoke and instead of calling the .NET function, to call the OS function directly.

@Ivan Starr : What is that one function that you need to access?

What is P/Invoke? A VBA socket library? I will look into it!


Oh ok it looks like a way to call lower-level platform-specific functions... But which function? I tried to create a web getpage fn with msxml2, but it uses ie and I think it sends an unwanted header with web requests that I'm tryingto get rid of. But it's all good, It's working now. Thanks again.

Ivan

 

[Skydiver]

I'm glad you got it working.

So it's not a single function that you needed from System.Net.Sockets as you claimed. You actually needed the full class infrastructure of the TcpClient class.