473,372 Members | 1,042 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Run Microsoft Access Module in VS.NET C#

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
Nov 16 '05 #1
17 26462
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.

Nov 16 '05 #2
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\EsperazaThre adsProgram\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
Nov 16 '05 #3
> 1. I think I'm missing a reference. It doesn't understand
Access.Application.
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?
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.
It looks OK... does it work?
I just don't understand enough.
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\EsperazaThre adsProgram\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.
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",BindingFla gs.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.
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

Nov 16 '05 #4
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\EsperazaThre adsProgram\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


Nov 16 '05 #5
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 Huang"" wrote:
Hi,

Here I attach a simple sample, you may have a try.
NOTE:please copy the db1.mdb to the c:\temp\ so that the application will
pick up the db file.
Also C# is a strong typed language, in c#'s function call we must provide
all the parameters it needs while in VB or VBA, we can omit the parameter
that is not necessary, so I use System.Reflection.Missing.Value which will
tell the ACCESS to use the default value just as we omit the parameter in
VBA.
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

Nov 16 '05 #6
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

""Peter Huang"" wrote:
Hi,

Here I attach a simple sample, you may have a try.
NOTE:please copy the db1.mdb to the c:\temp\ so that the application will
pick up the db file.
Also C# is a strong typed language, in c#'s function call we must provide
all the parameters it needs while in VB or VBA, we can omit the parameter
that is not necessary, so I use System.Reflection.Missing.Value which will
tell the ACCESS to use the default value just as we omit the parameter in
VBA.
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

Nov 16 '05 #7
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.

Nov 16 '05 #8
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\EsperazaThre adsProgram/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

""Peter Huang"" wrote:
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.

Nov 16 '05 #9
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\EsperazaThre adsProgram/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
Nov 16 '05 #10
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.

Nov 16 '05 #11


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #12


Does anyone know how to run a function in a Microsoft Access module
using ASP.NET? Code examples would be great! :-)

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #13
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.AcQuit Option.acQuitSaveNone)
;
}

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #14

Solved.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #15
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

"Matt Howard" wrote:


Does anyone know how to run a function in a Microsoft Access module
using ASP.NET? Code examples would be great! :-)

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #16
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

"Matt Howard" wrote:


Does anyone know how to run a function in a Microsoft Access module
using ASP.NET? Code examples would be great! :-)

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #17
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
Nov 16 '05 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Matt Howard | last post by:
I was wondering if anyone had any code samples on how to call functions within a Microsoft Access module using ASP.NET. I found a posting on how to do it with C#, but does anyone know how to do it...
3
by: JimF | last post by:
I need to scan a couple of hundred databases in several directories to locate all code that uses a particular function, but cannot figure out how to read the Form or Module text itself. I am...
10
by: Marcin Zmyslowski | last post by:
Hello all! I have a database created in MS Access 2003 which works fine in Win2000. This database shows me the following message in WinXP: "The expression On Load you entered as the event...
2
by: tonytony24 | last post by:
Hi All: I was wondering if there's a simple way to call a MS Access Module through either Command Prompt MS Script any other way... Thanks for the response.
0
by: bazzer | last post by:
hey, im trying to access a microsoft access database from an ASP.NET web application in visual basic 2003.NET. i get the following error when i try running it: Server Error in...
2
by: Cele Balser | last post by:
Hello, This can't be that hard! Could someone give me the code to run a macro in my Personal.xls from my Access module? I have created a query in Access, exported it to a network drive, I can open...
1
MitchR
by: MitchR | last post by:
Good Morning Folks; I have a question that is pretty far fetched but here goes nothing... I am looking to find a way to insert a macro into an Excel command button located in an Access VBA...
24
by: joeldault | last post by:
Question For Microsoft Access Data Base -------------------------------------------------------------------------------- I am Trying to create a single formula that would do the following: If...
0
by: titli | last post by:
I have created the excel worksheet from MS Access using the following code: Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Set xlApp = New Excel.Application With xlApp ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.