.net and Access 2007

[choward]

Hi Guys, just a quick question.

Within Access - building events and so on - can i code them in VB.net instead of VBA? Basically - i would like to take full advantage of Access' interface designer etc but also the power of .net

I cant really find any reference material on this so i'm guessing it's not possible, but a difinitive answer would be useful.

All the best,

Chris

[Uncle Gizmo]

>>>i'm guessing it's not possible<<<

I suspect it is possible, keep looking! If you find something post back!

[Dom DXecutioner]

Well, sadly Microsoft does not think that it would be a good idea to have Access and the .Net framework work together, even access 2007 does not have this capeability, or perhaps they just forgot

in either case, you can program against the .Net framework within vb.Net, but you must convert those classes to DLL files and call the DLL files within access...

this may be of interest:
http://www.devcity.net/Articles/18/msaccess_sp.aspx
http://dsmyth.blogspot.com/2006/03/c...vba-using.html
http://support.microsoft.com/kb/317535

there was a better article but i cannot find it, if i do, i'll post back

[winshent]

I am struggling with this..

I have compiled a dll in c# 3.5.. where i set the solution properties to register the build for COM interop.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace SBG_TestWithVBA
{
  [ClassInterface(ClassInterfaceType.AutoDual)]
  
  public class TestHello
  {
    [ComVisible(true)]

    public string SayHello(string name)
    {
      string message = "Hello " + name;
      return message;
    }
    public string HelloWorld()
    {
      string message = "Hello World!";
      return message;
    }
  }
}

I have then registered the assembly and tlb file using

"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\regasm.exe" "\\sbgfs01\users\Vincent Pirolli\My Docs\Visual Studio 2008\Projects\TestWithVBA\TestWithVBA\bin\Release\SBG_TestWithVBA.dll" /tlb: "\\sbgfs01\users\Vincent Pirolli\My Docs\Visual Studio 2008\Projects\TestWithVBA\TestWithVBA\bin\Release\SBG_TestWithVBA.tlb"

I then add the tlb file within vba from the tools>references menu..

and my VBA code is as follows:

Sub test()
    Dim f As SBG_TestWithVBA
    Set f = New SBG_TestWithVBA
    f.HelloWorld
End Sub

i get the following error:
"Automation error
The system cannot find the file specified."

Anyone got any ideas? I ahave the articles in the previous posts but i cannot find the solution.

This is the first time i have tried calling managed code that i have written myself.

[jal]

Originally Posted by choward

Hi Guys, just a quick question.

Within Access - building events and so on - can i code them in VB.net instead of VBA? Basically - i would like to take full advantage of Access' interface designer etc but also the power of .net

I cant really find any reference material on this so i'm guessing it's not possible, but a difinitive answer would be useful.

All the best,

Chris

As far as I know, VS 2008 Pro ships with a component called VSTO (Vistual Studio Tool for Office) which is a new version of Office VBA (it's actually VB.Net) and it works with Access 2007. I've never tried it as I do not have the Pro edition.

Actually a scaled-down version of VSTO was available with VS 2005 Pro as well, I believe.

[jal]

Originally Posted by winshentI am struggling with this..
[code

Sub test()
Dim f As SBG_TestWithVBA
Set f = New SBG_TestWithVBA
f.HelloWorld
End Sub
[/code]

I don't understand your code - probably because I've never done any interop. What confuses me is that you seem to be instanciating a Namespace. Normally in .Net you would instanciate a class, not a namespace. Maybe I'm missing something here. I would have expected something like this:

Dim f As SBG_TestWithVBA.TestHello
Set f = new SBG_TestWithVBA.TestHello
f.HelloWorld

[winshent]

Originally Posted by jal

I don't understand your code - probably because I've never done any interop. What confuses me is that you seem to be instanciating a Namespace. Normally in .Net you would instanciate a class, not a namespace. Maybe I'm missing something here. I would have expected something like this:

Dim f As SBG_TestWithVBA.TestHello
Set f = new SBG_TestWithVBA.TestHello
f.HelloWorld

fixed that and still got the same problem..

the error occurs on this line: 'Set f = New SBG_TestWithVBA.TestHello'

[Simon_MT]

Does .net support the [set] command? I have had to take out set in ASP.Net

Simon

[gemma-the-husky]

to use windows api you use constructs like this, to alias an external proc

Private Declare Function MyGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

so wouldnt you need to use something similar to get at your dll

alternatively, you must pe able to add it, to your projects database code references, but im not sure if such a library would be a normal dll, or something different.

[winshent]

Originally Posted by gemma-the-husky

to use windows api you use constructs like this, to alias an external proc



so wouldnt you need to use something similar to get at your dll

alternatively, you must pe able to add it, to your projects database code references, but im not sure if such a library would be a normal dll, or something different.

Hi Gemma..

I create the tlb file using regasm.exe and then add that file to the project references.

[winshent]

Have just read this on http://richnewman.wordpress.com/2007...ry-from-excel/:

Possible Problem with these Examples
One problem we have had with these examples is that Excel can get confused about which version of the .NET Framework to load if you have more than one version installed. If this happens you will get an automation error when you try to instantiate .NET objects at runtime from Excel. The .NET types will appear correctly in the Excel object browser.
The workaround for this is to tell Excel explicitly that the version of the .NET Framework that you are using is supported. To do this create a text file called Excel.exe.config and put it in the same directory as Excel.exe itself. The file should contain the text below (with the version number replaced with the .NET Framework version you are using):

<?xmlversion="1.0"?>
<configuration>
  <startup>
    <supportedRuntimeversion="v2.0.50727"/>
  </startup>
</configuration>

[Banana]

IINM, namespaces shouldn't be referenced; just classes.

Furthermore, I believe you need the switch /codebase when registering with regasm so it will work independently of GAC, something that Access or any COM-enabled applications cannot comprehend fully.

I also understood the .NET version to be touchy, but have not had problem with it, even though I was running both 1.1 and 2.0 (and used 2.0 for my particular case).

I don't remember seeing that particular error, but if you want some more concrete examples, I"ve already uploaded a sample of how to use .NET dll inside Access/VBA. linky. Maybe it'll help in addition to those articles Dom referenced.

[winshent]

ok, this is really annoying me now..

i've managed to get this to work.. but i dont understand why..

In solution properties within visual studio.. on the Build tab i set 'Register for COM interop' to true, and within 'Application>Assembly Information', i set 'Make assembly COM-Visible' = TRUE..

I have the VS project files in a shared drive. Now if i set the build location to the bin folder on the share drive, under my project files, when i call from VBA it doesnt work.. However, if i set the build folder to a folder on my local C Drive, then it works fine when calling from VBA..

Any ideas?

[Banana]

That's why I noted in my documentation that when building library for Access (or any COM-capable application) consumption, you have to build to COM specificatoins, not .NET specifications because COM specifications is more stringent and narrow than .NET. One of effect is that you have to use absolute paths when you register your library. While .NET uses GAC and provide a mean of entry to library without having to look up in the registry hive, COM does not just work that way and simply must be able to locate the library within the registry hive and get the full path from there.

So for your development, leave it in the bin folder, have your development mdb reference the library at bin folder. When you are ready for production, you will have to require an absolute path to your folder and ensure it is installed & registered the same way on everyone's computer.

I hope that helps some.