Run Microsoft Access Module in VS.NET C#

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to use Microsoft Access Automation within a Visual Studio 2003 program
written in C# for Windows Forms. When a button is clicked in my VS.NET
program, I want it to run a Microsoft Access Module.

Here is the info on the Microsoft Access Module:
Microsoft Access 2003 mdb is located at: C:\C#.NET\Esperanza
The mdb is called: EspThr.mdb
The Module is called: Module1
The function is: Public Function WriteHTML() As Integer
The result of the function is that it writes files to C:\UploadData

I found Knowledge Base Article 317114, so I think I need to use Automation.
But I've never used it and need help. Do I need to install anything else in
VS.NET? Do I need to add another Using statement? What statement(s) should I
put in my
private void btnAccess_Click(object sender, System.Eventargs e) { } event in
order to run the module?

I already am successfully using this Microsoft Access EspThr.mdb in ADO.NET
using System.Data.OleDB. But I don't think I can run the Microsoft Access
Module that way. Please tell me what I need to do to run the module.

Thanks in advance,
Pam
 
Hi Pam,

Here is the sample code.

private void button1_Click(object sender, System.EventArgs e)
{
Access.Application acApp = new Access.ApplicationClass();//create msaccess
application
acApp.OpenCurrentDatabase(@"C:\temp\db1.mdb",false,null);//open mdb file
object oMissing = System.Reflection.Missing.Value;
//Run the Test macro in the module
acApp.Run("Test",ref oMissing,ref oMissing,ref oMissing,ref oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing);
acApp.Quit();//exit application
}

You may try to tweak your code according your scenario.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Peter,
Sorry, I couldn't get it to work, probably because I'm just not good enough.

1. I think I'm missing a reference. It doesn't understand
Access.Application. What should my "using" statement be? Or please be very
specific and tell me what else I need to do to make it understand. I do not
program COM objects so I don't know anything about this.

2. I tried my best to convert what you put into something I could use. The
only thing I really want to do is run a specific Microsoft Access module
called "WriteHTML()" so here is what I wrote, but I don't have any idea if
it is right. I just don't understand enough.
(Here is the info on the Microsoft Access Module:
Microsoft Access 2003 mdb is located at: C:\C#.NET\Esperanza
The mdb is called: EspThr.mdb
The Module is called: Module1
The function is: Public Function WriteHTML() As Integer)

private void btnHTML_Click(object sender, System.EventArgs e)
{
Access.Application acAPP = new AccessibleEvents.ApplicationClass();
acApp.OpenCurrentDatabase(@"C:\C#.NET\EsperazaThreadsProgram\EspThr.mdb",
false, null);
object oMissing = System.Reflection.Missing.Value;
object rt = acApp.Run("WriteHTML()");
acApp.Quit(Access.AcQuitOption.acQuitSaveNone);
}

I didn't understand your use of System.Reflection or the oMissing, so I
doubt that it will work. Did I do it right? I apologize for understanding
so little. I really don't want to program com objects, I just want to know
enough to make older stuff run until Microsoft does away with the older
technology. Sorry if I offend you or if I am wasting your time.

Thanks,
Pam
 
1. I think I'm missing a reference. It doesn't understand
Access.Application.
Click to expand...

Go to Add Reference, from right clicking on the project in solution
explorer. From the COM tab, double click "Microsoft Access #.# Object
Library". You don't just have to click on it once, you have to make sure it
goes into the box at the bottom.
What should my "using" statement be?
Click to expand...

using Access;

but you don't need it if you've explicitly specified it like you have below.
2. I tried my best to convert what you put into something I could use.
The
only thing I really want to do is run a specific Microsoft Access module
called "WriteHTML()" so here is what I wrote, but I don't have any idea
if
it is right.
Click to expand...

It looks OK... does it work?
I just don't understand enough.
Click to expand...

Basically it's creating an instance of an Access.Application, then opening
your database, then telling Access to run the function in that database,
then closing it without save.
(Here is the info on the Microsoft Access Module:
Microsoft Access 2003 mdb is located at: C:\C#.NET\Esperanza
The mdb is called: EspThr.mdb
The Module is called: Module1
The function is: Public Function WriteHTML() As Integer)

private void btnHTML_Click(object sender, System.EventArgs e)
{
Access.Application acAPP = new AccessibleEvents.ApplicationClass();
acApp.OpenCurrentDatabase(@"C:\C#.NET\EsperazaThreadsProgram\EspThr.mdb",
false, null);
object oMissing = System.Reflection.Missing.Value;
object rt = acApp.Run("WriteHTML()");
acApp.Quit(Access.AcQuitOption.acQuitSaveNone);
}

I didn't understand your use of System.Reflection or the oMissing, so I
doubt that it will work.
Click to expand...

I'm not sure why you've got
Access.Application acAPP = new AccessibleEvents.ApplicationClass();
in my mind it should be
Access.ApplicationClass acAPP = new Access.ApplicationClass();
but test it to see what works and what doesn't, and post back if you get an
exception that you don't know what it means, telling what the exception was
and what line threw it.

The line involving System.Reflection.Missing.Value doesn't look to be used,
so you might aswell just remove it. But this is the constant you should pass
to any method that takes optional parameters that you don't want to specify
a value for.

Just as an example:
If I have an Excel.Workbooks object, and I want to call the Open method of
it - I would need to pass lots of Missing.Value because that method happens
to have a lot of optional parameters, and since c# doesn't support optional
parameters, you can't just leave them blank.
I could write
xlWbk = xlWbks.Open(ExcelFileName, false, true,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value);
but to me that looks bad. So I write
xlWbk = (Excel.Workbook)
((xlWbks.GetType()).InvokeMember("Open",BindingFlags.InvokeMethod,null,xlWbks,
new object[]{ExcelFileName, false, true}));
which is using the Type object property of the COM object in order to invoke
a method by using late binding, and it passes an array of objects to the
method. The compiler doesn't resolve and link into what method I'm calling
at compile time, so it doesn't mind that I haven't passed the parameters I
don't care about. And neither does Excel, because it's parameters are
optional. Also make sure you don't use these techniques on, say, Acces 10.0
and then try to use it on Access 9.0.
You need to either do all development on the lowest version you need to use,
or use late binding throughout. Developing on Access 9 and using on Access
10 should be OK though.
 
It's still not working, but it's closer! Thanks.

I added the reference to the COM object for Access. I know it added because
it showed under Reference in my Solutions Explorer. (version 11)

I tried adding the statement you suggested:
using Access;
but it didn't like it, "The type or namespace "Access' could not be found"

so I added
using Microsoft.Office.Interop.Access;
I don't know if this is what I should have done, but it liked it.

However, it still says I have a bug because
'Application' is an ambiguous reference,
'Form' is an ambigous reference.
etc.

Here's more of my code that it's complaining about, which used to work, but
now is ambiguous. What should I have put for my using statement so I don't
have ambiguity? Or what else should I do?

static void Main()
{Application.Run(new Form1());}

private void btnCancel_Click(object sender, System.EventArgs e)
{Application.Exit();}

public void btnParts_Click(object sender, System.EventArgs e)
{Form newform = new frmPartNo2();
newForm.Show();}

private void btnHTML_Click(object sender, System.EventArgs e)
{
Access.Application acAPP = new AccessibleEvents.ApplicationClass();
acApp.OpenCurrentDatabase(@"C:\C#.NET\EsperazaThreadsProgram\EspThr.mdb",
false, null);
object oMissing = System.Reflection.Missing.Value;
object rt = acApp.Run("WriteHTML()");
acApp.Quit(Access.AcQuitOption.acQuitSaveNone);
}

To explain what I'm doing, this form is just a bunch of buttons that go to
different forms, plus one button that runs the Microsoft Access module
function. Can I do this, or do I need to run the Microsoft Access module
function from a separate form?

Thanks :-)
Pam
 
Peter,

I think the code you sent me is the same that you originally posted, with
added comments. Yes, your code works, but it didn't answer my questions that
I posted on 10/15/04. So I'm still not sure what to do. Sorry for my
ignorance in this issue.

However, I do understand better why you are using the Missing.Value.

Thanks,
Pam
 
Peter,

I think the code you sent is the same as you initially posted, but with
added comments. Yes, the code does work. But it doesn't answer my questions
I posted on 10/15/04 so I still don't really know how to use it. Sorry for
my ignorance.

However, I do understand the Missing.Value better.

Thanks,
Pam
 
Hi

Based on my understanding, you wants to know how to run the function in the
module of Access mdb file.
Did I have any misunderstanding?

Since we have to automation acess to do, we do need to add a reference to
the access com object referecne.
I attach a sample together with the solution and project file which has
already add a reference to the access and declare the using detective.
That sample will run the Test function in the access module, you may just
change the name to the function you want to call will be OK.

If I have any misunderstanding, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Peter,

I did add a reference, and I also added your Using statement.
It now gives the error:
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in EsperanzaThreads.exe

Additional information: Microsoft Office Access can't find the procedure
'WriteHTML().'
when I use the following code:
private void btnWebCode_Click(object sender, System.EventArgs e)
{
Access.Application acApp = new Access.ApplicationClass();//create msaccess
applicatio
acApp.OpenCurrentDatabase(@"C:\C#.NET\EsperazaThreadsProgram/EspThr.mdb",false,null);//open mdb file
object oMissing = System.Reflection.Missing.Value;
//Run the Test macro in the module
object rt = acApp.Run("WriteHTML()",ref oMissing,ref oMissing,ref
oMissing,ref oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing);
//MessageBox.Show(rt.ToString());
acApp.Quit(Access.AcQuitOption.acQuitSaveNone);//exit application
}

Again, I apologize for not understanding enough to fix it and would
appreciate your suggestions. I am trying to use the function WriteHTML() in
Module1. Your code works fine, it is just that I do not understand enough to
convert your code into something that I can use for my specific needs.

Thanks,
Pam
 
I played around with it until I got it working!!!! Thank you, thank you,
thank you, Peter Huang! :-)

Here is the final code to use a Microsoft Access module function in C#.NET:

Be sure to add the COM reference for Access. (Solutions Explorer -> right
click the project -> Add Reference -> COM tab -> search near Microsoft Access
for the correct version of Access (Office 2003 is version 11).

Add the statement in the Using section:
using Access = Microsoft.Office.Interop.Access;
// note that "using Access;" or "using Microsoft.Office.Interop.Access;"
will NOT work.

Then double click a button on a form to open the C# code and use the
following code to run a function in a Microsoft Access module. Also see
Peter's previous posts in this thread about oMissing. I don't understand
much of COM programming, so if you have a problem please post to the forum, I
probably won't be able to help you.

private void btnWebCode_Click(object sender, System.EventArgs e)
{
Access.Application acApp = new Access.ApplicationClass();//create msaccess
application
acApp.OpenCurrentDatabase(@"C:\C#.NET\EsperazaThreadsProgram/EspThr.mdb",false,null);//open mdb file
object oMissing = System.Reflection.Missing.Value;
//Run the Test macro in the module

// note that I am using Microsoft Access
// module: Module1
// function: WriteHTML()
// but don't put the parenthesis, just put function name:
// WriteHTML
// and it will find the function properly without having to tell it that
it's in Module1.

object rt = acApp.Run("WriteHTML",ref oMissing,ref oMissing,ref oMissing,ref
oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,
ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing
,ref oMissing,ref oMissing);
//MessageBox.Show(rt.ToString());
acApp.Quit(Access.AcQuitOption.acQuitSaveNone);//exit application
}

Thank you Peter Huang!!! Case definitely closed :-)

Pam
 
Hi,

I am glad that the problem has been resolved.
Cheers!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Does anyone know how to run a function in a Microsoft Access module
using ASP.NET? Code examples would be great! :-)

Thanks
 
Peter,

I have a much simplier problem then Pam. I'm just trying to open a mdb
file. In my case, the menu_click turns the cursor to a waiting cursor
and then nothing happens. In some case the mdb file is a Table in the
other cases it's a Access Form - if it matters, no modules.

Folder security??

Steve

private void ndtCertMainDatabase_Click(...)
{
//local network location: QA="L:\QA\"
string fileName = QA + @"NDT\NDT.mdb";
openAccessDatabase(fileName);
}

private void openAccessDatabase(string fileName)
{
///process() method, old way - BAD??,
///need independence from my getter program
//running in background
//openAllOtherProgramsAndFiles(fileName);

///better way - Interop
Microsoft.Office.Interop.Access.Application oAccess
= new Microsoft.Office.Interop.Access.ApplicationClass();

oAccess.OpenCurrentDatabase(fileName, false, null);

///like to say oAccess.activate() or show() like Word

oAccess.QuitMicrosoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone)
;
}
 
I would like to run an Access Module in ASP.NET also. I attempted the
solution described earlier in this thread with late binding, but I got an
exception that Access could not find the macro I specified. My macro name is
"JanList" but in the error message Access said that it could not find
"JanList. " Does the addition of a period and space in the macro name string
inidicate something about what I'm doing wrong? Do I need to put some
delimiter or something around the macro name?

Thanks in advance.
Stan
 
I would like to run an Access Module in ASP.NET also. I attempted the
solution described earlier in this thread with late binding, but I got an
exception that Access could not find the macro I specified. My macro name is
"JanList" but in the error message Access said that it could not find
"JanList. " Does the addition of a period and space in the macro name string
inidicate something about what I'm doing wrong? Do I need to put some
delimiter or something around the macro name?

Thanks in advance.
Stan
 
Stan,

When you get no answer, than I would place this question as I was you in the
newsgroup.

Microsoft.public.dotnet.general

Probably when you are lucky will Paul Clement than catch this message and
maybe he can give you than an answer.

Cor
 
Back
Top