Access 2007 Automation from NAV

carstenPaul

Hello,
I like to open Access 2007 from NAV to run a public function "function03" or a public Sub "Sub07" from module "module05". With the return value of
the function I'd like NAV to decide with a case what to to next.
What I've tried ...
I googled a lot and tried mibuso for a solution. up to now no success.

I also did not find a documentation for "Microsoft Access 12.0 Object Library"

What I did in NAV up to now ...

C/AL Globals
Name	DataType	Subtype	Length
AccessApp	Automation	'Microsoft Access 12.0 Object Library'.Application

NAV Code

IF ISCLEAR(AccessApp) THEN CREATE(AccessApp);

// MsoAutomationSecurityForceDisable = 3
// MsoAutomationSecurityByUI = 2
// MsoAutomationSecurityLow = 1
AccessApp.AutomationSecurity := 3;

AccessApp.OpenCurrentDatabase('U:\temp\aa_aaaaa.accdb',FALSE,'');
AccessApp.Visible(TRUE);

Access 2007 opens ... that's very good

Then I want to start a function oder a procedure
Someome posted to make something like this

bool := AccessApp.Run('function03');

But where should Access 2007 know from in which module this function is ?

even if I try

bool := AccessApp.Run('module05.function03');

or

AccessApp.Run('module05.Sub07');

or

AccessApp.Run('Sub07');

or

AccessApp.Run('aaa_aaaaa.module05.Sub07');

or

AccessApp.Run('aaa_aaaaa.Sub07');

NAV returns a message with "Procedure could not be found"

What I like to do with automation from NAV

# Open Acccess 2007
works fine with

AccessApp.OpenCurrentDatabase('U:\temp\aa_aaaaa.accdb',FALSE,'');
AccessApp.Visible(TRUE);

# Run public function03 or public sub07 of module05
No success at the moment

# Get return value from function03 of module05 or run sub07
No success at the moment

# close Access 2007
works fine with

AccessApp.Quit

# decide in NAV what to do with the return vale
(case : no problem ;-) )


I tried to make some experiments with some further automation servers, e.g. 'Microsoft Access 12.0 Object Library'.AccessObject.

If I try to create an instance with

Name	DataType	Subtype	Length
AccessObject	Automation	'Microsoft Access 12.0 Object Library'.AccessObject

CREATE(AccessObject);

an error is reported by NAV : (translated from German)
No Instance could be created ... Find out if OLE Control or automation server was installes correctly.

Hmmmm.
I have no idea how to continue except waiting for a hint from here. ](*,)


Thank you for help

kine

May be you can run the function through ADO?

And help for the Access objects can be found when you run the Visual Basic from MS Access and you will press F1. There is description of all classes etc. 8)

carstenPaul

Hi Kamil,
thank you for your hint.

kine wrote:

May be you can run the function through ADO?

I thought ADO is for querying. Do you have any further hints to start a functionX in modulZ with ADO ?

I like some functions and procedures to open existing forms to deliver NAV with status of operation.

Carsten

carstenPaul

i try to make some experiences with Dot Net 3.5. Maybe it helps

carstenPaul

Even if i installed Dot 3.5 i get the following message...



Could anybody help what to to ?

Thanx, Carsten

modric

Office 2007 Apps have some new security enhacments - file formats, that PHYSICALLY doesn't allow macros.

For example, EXCEL default is myfile.XLSX, to use/run macros you need "macroenabled" format myfile.XLSM and macro execution enabled, as in previous versions.

I don't have Access`07 available to check - but maybe there is something similar?

carstenPaul

I switched the mentioned security off before because of I thought it was the problem. But it wasn't

carstenPaul

I trid somthing more ...

* Deinstallation of Office 2007 as Admin without ADS
* Reparing of Dot Net 3.5 (there are also 1.1, 2 and 3 on my Computer)
* Installation of Office 2007 as Admin without ADS


I can only make an instance of
'Microsoft Access 12.0 Object Library'.Application

The other like e.g. 'Microsoft Access 12.0 Object Library'.AccessObject

let the following error appear


I also tried ...
copy of "Microsoft.Office.Interop.Access.dll", gacutil and regasm from a machine with Visual Studio 2008 to my machine and then ...
c:\access\gacutil /f /i Microsoft.Office.Interop.Access.dll
c:\access\regasm Microsoft.Office.Interop.Access.dll

the message

C:\access>c:\access\gacutil /f /i Microsoft.Office.Interop.Access.dll

Microsoft (R) .NET Global Assembly Cache Utility. Version 1.1.4322.573
Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.

Assembly successfully added to the cache

C:\access>c:\access\regasm Microsoft.Office.Interop.Access.dll
Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.1433
Copyright (C) Microsoft Corporation 1998-2004. All rights reserved.

Types registered successfully

C:\access>pause


After registration I created a new Codeunit and instanciated "'Microsoft Access 12.0 Object Library'.AccessObject"

Name DataType Subtype Length
aaaa Automation 'Microsoft Access 12.0 Object Library'.AccessObject

OnRun()
CREATE(aaaa);

Same Error



[-o< ](*,)

carstenPaul

I made a new Installation of Navision, Office 2007 and Visual Studio 2008 on a new machine. It doesn't change anything. Same error.

Could anybody help ?

Regards carstenPaul

garak

Why do you use 'Microsoft Access 12.0 Object Library'.AccessObject ?
To create a Access instance u must first load 'Microsoft Access 12.0 Object Library'.Application

And then do the rest.

Regards

garak

Here a link to MSDN

Also read the VBAAC12.CHM file in your Office installation folder to learn more about the access interface.

Regards

carstenPaul

garak wrote:

Why do you use 'Microsoft Access 12.0 Object Library'.AccessObject ?
To create a Access instance u must first load 'Microsoft Access 12.0 Object Library'.Application

And then do the rest.

Regards

Hi garak,

thanx for posting. I did it like you suggested (please take a look at the top of the page).

I can start Access but I want to call a function with a return value and want to react on events

Greetz, carstenPaul

carstenPaul

garak wrote:

Also read the VBAAC12.CHM file in your Office installation folder to learn more about the access interface.

Hello again,

I googled and searched on my desktopcomputer for "VBAAC12.CHM ". No result

Regards, carstenPaul

garak

Then search only for VBAAC* These help files are in the 1031 or 1032 Folder of the Office installition (or search the CD).
To work with some eventy, if you really mean the events, u must enable the property with events.

But the Object Automation will be pass from a other Automation. For example from AccApp.Forms