.net and Access 2007 (1 Viewer)

choward

Registered User.
Local time
Today, 04:00
Joined
Jan 31, 2008
Messages
39
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

Nifty Access Guy
Staff member
Local time
Today, 11:00
Joined
Jul 9, 2003
Messages
16,244
>>>i'm guessing it's not possible<<<

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

Dom DXecutioner

AWF VIP
Local time
Today, 04:00
Joined
Jun 25, 2007
Messages
57
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/calling-managed-code-from-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

Registered User.
Local time
Today, 11:00
Joined
Mar 3, 2008
Messages
162
Re: .net and Access 2003

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.

Code:
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

Code:
"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:

Code:
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.
 
Last edited:

jal

Registered User.
Local time
Today, 04:00
Joined
Mar 30, 2007
Messages
1,709
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

Registered User.
Local time
Today, 04:00
Joined
Mar 30, 2007
Messages
1,709
Re: .net and Access 2003

winshentI am struggling with this.. [code said:
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

Registered User.
Local time
Today, 11:00
Joined
Mar 3, 2008
Messages
162
Re: .net and Access 2003

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'
 
Last edited:

Simon_MT

Registered User.
Local time
Today, 11:00
Joined
Feb 26, 2007
Messages
2,177
Does .net support the [set] command? I have had to take out set in ASP.Net

Simon
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:00
Joined
Sep 12, 2006
Messages
15,613
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

Registered User.
Local time
Today, 11:00
Joined
Mar 3, 2008
Messages
162
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

Registered User.
Local time
Today, 11:00
Joined
Mar 3, 2008
Messages
162
Have just read this on http://richnewman.wordpress.com/2007/04/15/a-beginner’s-guide-to-calling-a-net-library-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):


Code:
[FONT=Courier][SIZE=2][COLOR=blue]<?[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]xml[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue] [/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=red]version[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]=[/COLOR][/SIZE][/FONT]"[FONT=Courier][SIZE=2][COLOR=blue]1.0[/COLOR][/SIZE][/FONT]"[FONT=Courier][SIZE=2][COLOR=blue]?>[/COLOR][/SIZE][/FONT]
[FONT=Courier][SIZE=2][COLOR=blue]<[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]configuration[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]>[/COLOR][/SIZE][/FONT]
[FONT=Courier][SIZE=2][COLOR=blue]  <[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]startup[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]>[/COLOR][/SIZE][/FONT]
[FONT=Courier][SIZE=2][COLOR=blue]    <[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]supportedRuntime[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue] [/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=red]version[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]=[/COLOR][/SIZE][/FONT]"[FONT=Courier][SIZE=2][COLOR=blue]v2.0.50727[/COLOR][/SIZE][/FONT]"[FONT=Courier][SIZE=2][COLOR=blue]/>[/COLOR][/SIZE][/FONT]
[FONT=Courier][SIZE=2][COLOR=blue]  </[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]startup[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]>[/COLOR][/SIZE][/FONT]
[FONT=Courier][SIZE=2][COLOR=blue]</[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=#a31515]configuration[/COLOR][/SIZE][/FONT][FONT=Courier][SIZE=2][COLOR=blue]>[/COLOR][/SIZE][/FONT]
 

Banana

split with a cherry atop.
Local time
Today, 04:00
Joined
Sep 1, 2005
Messages
6,318
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

Registered User.
Local time
Today, 11:00
Joined
Mar 3, 2008
Messages
162
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

split with a cherry atop.
Local time
Today, 04:00
Joined
Sep 1, 2005
Messages
6,318
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.
 

Users who are viewing this thread

Top Bottom