Calling a Dot Net DLL from MS Access

[Al Meadows]

This is driving us nuts! And, I got an almost immediate deadline to figure
this out.



We created a Dot Net DLL (in C#). Used the option to Male assembly
COM-Visible under Assembly Information. On the Build Tab, checked Register
for COM interop. And added an installer class (although I didn't know what
else I was suppose to do other than add it)



Compiled it and got XYZ.DLL and XYZ.TLB.



Next, I placed these two files along with everything else in the Bin\Debug
folder up on a shared network drive.



Next, I created a new Access Application and created a code module and then
added a reference to the network based TLB. DIMmed and SET it to an object
and then used the object to call a function in the DLL that just did a
message box saying "hello form the DLL"



This works great.



I copied the MDB file up to the network also and re-tested. Everything
STILL worked great.



The developer in the next cubicle opens up the Access database and tries to
run the code module and we run into trouble in the Set Statement.



So, obviously. I'm overlooking a step to make this DLL useable by someone on
a different machine.



My research uncovered REGASM and GACUTIL. We ran REGASM on it from the
other developer's desktop but this still has problems.



Now, if the developer re-compiles the DLL on his desktop.. everything WORKS.



So, again this points to the Dot Net compilation process doing something
'for me' that isn't done for someone else unless they also use Visual Studio
to re-compile the DLL. Obviously. something we can't rollout.



Can someone walk me through the steps I'm missing to make this XYZ.DLL work
for ANYONE that opens the Access Database? And, if that simply means running
something suck as REGASM or GACUTIL. that's cool. we can install for them.



The documentation online really sucks on this.



Other Misc: This is VS2005, XYZ.DLL has an added reference to ABC.DLL which
is a wrapper around a Web Service. XYZ.DLL is meant to be called from the
Access VBA Code and which will give it instructions to upload or download
stuff to/from the web service wrapper (ABC.DLL). Just to complicate things.
XYZ.DLL itself points back to the same MDB Database backend to run queries.
But, as I said. if I compile the DLL on my desktop. everything works fine
doing nothing but setting the options on the project tab.

[Jialiang Ge [MSFT]]

Dear Al Meadows. Welcome to the interop newsgroup! My name is Jialiang Ge
[MSFT]. It's my pleasure to work with you on this thread.

From the description of the issue, I hear your frustration and understand
the importance of the issue in your project. I immediately performed the
researches, and I reached the following solutions and summarized the cause
of the problem for your references. I place the solutions in the first
place considering that it is more important to your project.

//////////////////////////////////////////////////
Solutions

Here are two solutions. Each has its own advantages and disadvantages. You
can make the choice based on the context of the project.

=====================================
* Solution1
Still publish the assembly in the network share, but we need to configure
.NET Code Access Security (CAS) upon it.

You used regasm to setup the assembly in the colleague's desktop but still
got the problem. This most likely results from .NET CAS because the
codebase is a network share folder. These two articles introduce CAS in
detail:
http://msdn.microsoft.com/en-us/library/aa302424.aspx
http://msdn.microsoft.com/en-us/magazine/cc163700.aspx
Network share is not fully trusted by the .NET runtime. The code in the
network share will not execute as fully trusted and you may receive
unexpected security exceptions.

There are basically two methods to grant the full trust permissions to the
assemblies in the network share:

A. Use .NET Framework 2.0 Configuration. In your colleague's desktop /
Administrative Tools / Microsoft .NET Framework 2.0 Configuration / My
Computer / Runtime Security Policy / Machine / Code Groups / All_Code /
Right-click LocalIntranet_Zone and open its Properties dialog / Permission
Set tab / Select Permission set as FullTrust. Please note that this is a
global setting. In other words, all the assemblies in intranet shares (not
just the network share for your project) will have the full trust
permission.

B. Use Code Access Security Policy Tool (Caspol.exe).
http://msdn.microsoft.com/en-us/library/cb6t8dtz(VS.80).aspx
In your colleague's desktop, run this command:
caspol -machine -addgroup 1. -url \\<Network Drive Path>\<.NET
Assebmly>.dll FullTrust
This command adds that specific DLL to full trust, and will not affect the
global setting. I recommend this solution.

Advantages of the solution:

Easy to upgrade the assembly. For example, if one day you need to upgrade a
function in the .NET assembly and re-deploy it to your clients, as long as
there is no change to the type library (i.e. no new interfaces or public
methods are added), what we need to do is simply to disconnect the network
share from the intranet, copy & paste the new assembly to the folder and
reconnect the network share to the intranet. On the side of your clients,
they just need to restart the host application.

Disadvantages of the solution:

The first method (Use .NET Framework 2.0 Configuration) changes the global
setting which may not be preferred by your clients. However, the second
method (Use Code Access Security Policy Tool) overcomes this disadvantage.

=====================================
* Solution2
Instead of deploying the assemblies in the network share, we can build a
setup project to package the assembly and typelib, and ask your clients to
install them locally.

Here are the basic steps to create such a setup project:

In Visual Studio, Add New Project / Other Project Types / Setup and
Deployment / Setup Project. Right-click the resulting project, and select
Add / Project Output / Primary output of your .NET assembly project. After
doing this, select "Primary output from <.NET Assembly Project> (Active),
and open its Properties. Find the option "Register" and set it to be
vsdrpCOM. Build the setup project and deploy the setup.exe file to your
clients.

When your clients install the setup file, the TLB and .NET assembly file
are copied to the local folder that has .NET full-trust by default. So this
overcomes the .NET CAS problem too.

Advantages of the solution:
Easy to deploy. In this solution, we deploy the .NET component with a
standard setup project.

Disadvantage of the solution:
A little hard to upgrade. When a new version of the assembly is available,
your clients need to install the latest setup package.


Please try the above solutions and tell me whether they are helpful. If you
meet with any problems, please feel free to tell me.


//////////////////////////////////////////////////
Cause

In this section, I will try to answer some "why"s.

1. Why does not COM-visible assembly work fine in the development machine,
but not in the client's machine?

To make your COM-visible assembly work fine, there are two prerequisites:
A) the component needs to be registered to the registry HKCR as COM. B) The
assembly needs .NET CAS full-trust to run successfully.

In your development machine, VS registers the component as COM when you
BUILD (in your words, compile) the project. Therefore, the first
prerequisite is met. It also explains why compiling the project in your
client's machine can seemingly solve the problem.

Because the project is in the local bin\debug folder, the resulting
assembly is always LOCAL to you and thus has the full-trust permission.
(This sentence may have small problems in some conditions, but it is true
in this specific case). Therefore, it meets the second prerequisite.

2. Why doesn't the COM-visible work fine in the client's machine?

At first, the component was not registered in the client's machine. Regasm
is the right tool to register the assembly. You ran REGASM on it from the
other developer's desktop but this still has problems. You did not describe
what the problem is in your question, but I think that the problem is most
likely caused by .NET GAC because the .NET assembly is in a network shared
folder. If I understand it rightly, the .NET assembly should be loaded to
the host application at this moment, but there are problems during the
execution. You can find the solution of this issue in the above "SOLUTION"
section.


If you have any other questions or concerns, please don't hesitate to let
me know. I will be more than happy to be of assistance.

Have a nice day!

Regards,
Jialiang Ge (***@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
***@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Jialiang Ge [MSFT]]

Dear Al Meadows

I am writing to check the status of the issue on your side. Would you mind
letting me know the result of the suggestions? If you need further
assistance, feel free to let me know. I will be more than happy to be of
assistance.

Have a great day!

Regards,
Jialiang Ge (***@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
***@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

[Mary M]

hello,

I'm facing the same issue that this user had. My question is, with solution number 2, by installing everything the assemblies locally in a trusted folder, if my trusted folder it is inside %user profile%.... will the Access database will pick up the reference accordingly??? Meaning, when user A, opens up the database from his/her desktop, he or she will see the reference to her/his own c:\%user profile%\documents and settings. And if user B, opens up the database from his/her destop, he or she will see reference to her/his own c:\%user profile%...???

Post by Al Meadows
This is driving us nuts! And, I got an almost immediate deadline to figure
this out.
We created a Dot Net DLL (in C#). Used the option to Male assembly
COM-Visible under Assembly Information. On the Build Tab, checked Register
for COM interop. And added an installer class (although I didn't know what
else I was suppose to do other than add it)
Compiled it and got XYZ.DLL and XYZ.TLB.
Next, I placed these two files along with everything else in the Bin\Debug
folder up on a shared network drive.
Next, I created a new Access Application and created a code module and then
added a reference to the network based TLB. DIMmed and SET it to an object
and then used the object to call a function in the DLL that just did a
message box saying "hello form the DLL"
This works great.
I copied the MDB file up to the network also and re-tested. Everything
STILL worked great.
The developer in the next cubicle opens up the Access database and tries to
run the code module and we run into trouble in the Set Statement.
So, obviously. I'm overlooking a step to make this DLL useable by someone on
a different machine.
My research uncovered REGASM and GACUTIL. We ran REGASM on it from the
other developer's desktop but this still has problems.
Now, if the developer re-compiles the DLL on his desktop.. everything WORKS.
So, again this points to the Dot Net compilation process doing something
'for me' that isn't done for someone else unless they also use Visual Studio
to re-compile the DLL. Obviously. something we can't rollout.
Can someone walk me through the steps I'm missing to make this XYZ.DLL work
for ANYONE that opens the Access Database? And, if that simply means running
something suck as REGASM or GACUTIL. that's cool. we can install for them.
The documentation online really sucks on this.
Other Misc: This is VS2005, XYZ.DLL has an added reference to ABC.DLL which
is a wrapper around a Web Service. XYZ.DLL is meant to be called from the
Access VBA Code and which will give it instructions to upload or download
stuff to/from the web service wrapper (ABC.DLL). Just to complicate things.
XYZ.DLL itself points back to the same MDB Database backend to run queries.
But, as I said. if I compile the DLL on my desktop. everything works fine
doing nothing but setting the options on the project tab.

Post by Jialiang Ge [MSFT]
Dear Al Meadows. Welcome to the interop newsgroup! My name is Jialiang Ge
[MSFT]. It's my pleasure to work with you on this thread.
From the description of the issue, I hear your frustration and understand
the importance of the issue in your project. I immediately performed the
researches, and I reached the following solutions and summarized the cause
of the problem for your references. I place the solutions in the first
place considering that it is more important to your project.
//////////////////////////////////////////////////
Solutions
Here are two solutions. Each has its own advantages and disadvantages. You
can make the choice based on the context of the project.
=====================================
* Solution1
Still publish the assembly in the network share, but we need to configure
NET Code Access Security (CAS) upon it.
You used regasm to setup the assembly in the colleague's desktop but still
got the problem. This most likely results from .NET CAS because the
codebase is a network share folder. These two articles introduce CAS in
http://msdn.microsoft.com/en-us/library/aa302424.aspx
http://msdn.microsoft.com/en-us/magazine/cc163700.aspx
Network share is not fully trusted by the .NET runtime. The code in the
network share will not execute as fully trusted and you may receive
unexpected security exceptions.
There are basically two methods to grant the full trust permissions to the
A. Use .NET Framework 2.0 Configuration. In your colleague's desktop /
Administrative Tools / Microsoft .NET Framework 2.0 Configuration / My
Computer / Runtime Security Policy / Machine / Code Groups / All_Code /
Right-click LocalIntranet_Zone and open its Properties dialog / Permission
Set tab / Select Permission set as FullTrust. Please note that this is a
global setting. In other words, all the assemblies in intranet shares (not
just the network share for your project) will have the full trust
permission.
B. Use Code Access Security Policy Tool (Caspol.exe).
http://msdn.microsoft.com/en-us/library/cb6t8dtz(VS.80).aspx
caspol -machine -addgroup 1. -url \\<Network Drive Path>\<.NET
Assebmly>.dll FullTrust
This command adds that specific DLL to full trust, and will not affect the
global setting. I recommend this solution.
Easy to upgrade the assembly. For example, if one day you need to upgrade a
function in the .NET assembly and re-deploy it to your clients, as long as
there is no change to the type library (i.e. no new interfaces or public
methods are added), what we need to do is simply to disconnect the network
share from the intranet, copy & paste the new assembly to the folder and
reconnect the network share to the intranet. On the side of your clients,
they just need to restart the host application.
The first method (Use .NET Framework 2.0 Configuration) changes the global
setting which may not be preferred by your clients. However, the second
method (Use Code Access Security Policy Tool) overcomes this disadvantage.
=====================================
* Solution2
Instead of deploying the assemblies in the network share, we can build a
setup project to package the assembly and typelib, and ask your clients to
install them locally.
In Visual Studio, Add New Project / Other Project Types / Setup and
Deployment / Setup Project. Right-click the resulting project, and select
Add / Project Output / Primary output of your .NET assembly project. After
doing this, select "Primary output from <.NET Assembly Project> (Active),
and open its Properties. Find the option "Register" and set it to be
vsdrpCOM. Build the setup project and deploy the setup.exe file to your
clients.
When your clients install the setup file, the TLB and .NET assembly file
are copied to the local folder that has .NET full-trust by default. So this
overcomes the .NET CAS problem too.
Easy to deploy. In this solution, we deploy the .NET component with a
standard setup project.
A little hard to upgrade. When a new version of the assembly is available,
your clients need to install the latest setup package.
Please try the above solutions and tell me whether they are helpful. If you
meet with any problems, please feel free to tell me.
//////////////////////////////////////////////////
Cause
In this section, I will try to answer some "why"s.
1. Why does not COM-visible assembly work fine in the development machine,
but not in the client's machine?
A) the component needs to be registered to the registry HKCR as COM. B) The
assembly needs .NET CAS full-trust to run successfully.
In your development machine, VS registers the component as COM when you
BUILD (in your words, compile) the project. Therefore, the first
prerequisite is met. It also explains why compiling the project in your
client's machine can seemingly solve the problem.
Because the project is in the local bin\debug folder, the resulting
assembly is always LOCAL to you and thus has the full-trust permission.
(This sentence may have small problems in some conditions, but it is true
in this specific case). Therefore, it meets the second prerequisite.
2. Why doesn't the COM-visible work fine in the client's machine?
At first, the component was not registered in the client's machine. Regasm
is the right tool to register the assembly. You ran REGASM on it from the
other developer's desktop but this still has problems. You did not describe
what the problem is in your question, but I think that the problem is most
likely caused by .NET GAC because the .NET assembly is in a network shared
folder. If I understand it rightly, the .NET assembly should be loaded to
the host application at this moment, but there are problems during the
execution. You can find the solution of this issue in the above "SOLUTION"
section.
If you have any other questions or concerns, please don't hesitate to let
me know. I will be more than happy to be of assistance.
Have a nice day!
Regards,
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Post by Jialiang Ge [MSFT]
Dear Al Meadows
I am writing to check the status of the issue on your side. Would you mind
letting me know the result of the suggestions? If you need further
assistance, feel free to let me know. I will be more than happy to be of
assistance.
Have a great day!
Regards,
Microsoft Online Community Support
=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================