Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-20-2008, 02:15 AM   #1
choward
Registered User
 
Join Date: Jan 2008
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
choward is on a distinguished road
.net and Access 2007

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

choward is offline   Reply With Quote
Old 02-25-2008, 01:15 PM   #2
Uncle Gizmo
Super Moderator
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 6,029
Thanks: 35
Thanked 252 Times in 243 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
Send a message via Skype™ to Uncle Gizmo
>>>i'm guessing it's not possible<<<

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Uncle Gizmo is offline   Reply With Quote
Old 02-25-2008, 07:55 PM   #3
Dom DXecutioner
AWF VIP
 
Dom DXecutioner's Avatar
 
Join Date: Jun 2007
Location: California
Posts: 57
Thanks: 0
Thanked 7 Times in 5 Posts
Dom DXecutioner is on a distinguished road
Send a message via Yahoo to 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

__________________
Dom DXecutioner
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Xyon-X Studios
Dom DXecutioner is offline   Reply With Quote
Old 02-10-2009, 06:39 AM   #4
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 124
Thanks: 20
Thanked 6 Times in 6 Posts
winshent is on a distinguished road
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 by winshent; 02-10-2009 at 07:35 AM. Reason: typo
winshent is offline   Reply With Quote
Old 02-11-2009, 12:02 AM   #5
jal
Newly Registered User
 
Join Date: Mar 2007
Posts: 1,709
Thanks: 0
Thanked 2 Times in 2 Posts
jal will become famous soon enough jal will become famous soon enough
Re: .net and Access 2007

Quote:
Originally Posted by choward View Post
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 is offline   Reply With Quote
Old 02-11-2009, 12:08 AM   #6
jal
Newly Registered User
 
Join Date: Mar 2007
Posts: 1,709
Thanks: 0
Thanked 2 Times in 2 Posts
jal will become famous soon enough jal will become famous soon enough
Re: .net and Access 2003

Quote:
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
jal is offline   Reply With Quote
Old 02-11-2009, 12:52 AM   #7
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 124
Thanks: 20
Thanked 6 Times in 6 Posts
winshent is on a distinguished road
Re: .net and Access 2003

Quote:
Originally Posted by jal View Post
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 by winshent; 02-11-2009 at 12:55 AM. Reason: error line
winshent is offline   Reply With Quote
Old 02-11-2009, 01:52 AM   #8
Simon_MT
Mad kiwi in the corner
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,026
Thanks: 1
Thanked 128 Times in 127 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
Re: .net and Access 2007

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

Simon
Simon_MT is offline   Reply With Quote
Old 02-11-2009, 02:26 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 11,705
Thanks: 35
Thanked 563 Times in 552 Posts
gemma-the-husky is just really nice gemma-the-husky is just really nice gemma-the-husky is just really nice gemma-the-husky is just really nice gemma-the-husky is just really nice
Re: .net and Access 2007

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

Quote:
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.
gemma-the-husky is offline   Reply With Quote
Old 02-11-2009, 04:11 AM   #10
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 124
Thanks: 20
Thanked 6 Times in 6 Posts
winshent is on a distinguished road
Re: .net and Access 2007

Quote:
Originally Posted by gemma-the-husky View Post
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 is offline   Reply With Quote
Old 02-11-2009, 04:24 AM   #11
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 124
Thanks: 20
Thanked 6 Times in 6 Posts
winshent is on a distinguished road
Re: .net and Access 2007

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


Code:
<?xmlversion="1.0"?>
<configuration>
  <startup>
    <supportedRuntimeversion="v2.0.50727"/>
  </startup>
</configuration>
winshent is offline   Reply With Quote
Old 02-11-2009, 04:52 AM   #12
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 70 Times in 61 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Re: .net and Access 2007

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.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 02-13-2009, 03:48 AM   #13
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 124
Thanks: 20
Thanked 6 Times in 6 Posts
winshent is on a distinguished road
Re: .net and Access 2007

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?
winshent is offline   Reply With Quote
Old 02-13-2009, 04:46 AM   #14
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,319
Thanks: 0
Thanked 70 Times in 61 Posts
Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light Banana is a glorious beacon of light
Re: .net and Access 2007

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.

__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access 2007 Performance Help vtabor General 1 06-23-2008 06:11 AM
General Question about Access 2007 jukus General 6 02-12-2008 08:55 PM
OCX distribution - Access 2007 rtdgreg Forms 0 02-04-2008 02:36 AM
Does Access 2007 support coding modules in .net or still VBA? jim87 Modules & VBA 2 10-26-2007 09:05 AM
Access 2007 swanny General 2 06-04-2007 08:37 PM




All times are GMT -8. The time now is 11:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World