How to re write Office Automation Code

What is it you need help with exactly?

Also what is 'sample.txt'?

I'm pretty sure it's not a text file.

sample.txt is sample.mdb ( Please change the extension ).

In that MS Access Database, you can view the Saved ODBC Connection.

 " TestODBC " is the name used to save the connection.

At present, i am calling TestODBC -Instead of calling the ODBC Connection string ..!!!

This throws lot of error on deployment !!

How to call DSN Connection String :  ODBC;DSN=Salisbury;DBQ=S:\;CODEPAGE=1252;

Like Conn.Open() and Conn.Close() from C# ?

Saved import and Export specifications are accessbile in VB and C#.

Use the object browser.   They are under ImportExportSpecification.

Why do you need to 'call' the Saved ODBC connection?

Can't you just create your own connection in the code?

@imnorie, i dont know how to do that !! If you could help me that will be great. Because, ODBC Driver Connection works little different as compared to normal ODBC Provider connections

@DoDahD, Can you please explain it through screen shot !! I tried to find it through object browser but not able to succeed !!!

Visual Basic, use DoCmd.TransferText and you will be prompted for the specification, etc.

@DoDahD, I didnt get your point ?? This posting is related with C# ?

Where do you want me to write your one line syntax ???

I n C #, start with the access1.DoCmd.TransferText

@DoDahD,

your line syntax does nothing !! Any reason for sharing that piece of syntax ??

OK,

Where is this automation going to be run?
In the background of your image I see MS Access.
If you are going to be using it off a form or any other MS Access object then the required language will be VBA, and not C#

If you are NOT running it from MS Access, but from a Visual Studio project of some sort, you have posted to the wrong zone.

Please specify where the code you would like to create will be executed.

From Comment Id # 36354152 , @aikimark

If you download the Sample.mdb, you cannot find any tables. But, please - click : External Data - Click : Saved Exports

Under Saved Exports, You can see a new window !! Click Saved Imports

ie) Automated Saved Import Method which will extract the data automatically through DSN Connection which is already stored on Access database !!

@NICK67, Please download the Sample.mdb and go to External Data Tab -> Click Saved Exports -> You can see new window -> Look at Saved Imports !!

This will show you the Saved DSN Connection String on MS Access Database !!

The whole saved function stored inbuilt as VBA Code inside the Access database !!

I understand where your saved import is.
You need Access 2007 or 2010 to see those as they are a new feature.
That is not a problem

You mention C#
The language used by Office is VBA (Visual Basic for Applications) not C#

Just how or why is C# involved?

Nick67 :

At present, I am invoking this Saved Import of Access from C# Source Code.
I have attached the source code for your reference !!

What i am expecting is, I want the VBA Macro code which stores Saved Import Method on Access Database. If i am able to get that syntax, i can re-write that syntax in C# which could establish the DSN Connection, fetch the tables and import it to Access database !!

//string sSalisburyAccessDB = Server.MapPath("App_Data/Database1.mdb");
            //access1.OpenCurrentDatabase(sSalisburyAccessDB, true, null);

            //// Drop the existing table data

            //access1.DoCmd.DeleteObject(Access.AcObjectType.acTable, "plans");

            //access1.DoCmd.DeleteObject(Access.AcObjectType.acTable, "price");

            //// Run the saved import
            //access1.DoCmd.RunSavedImportExport("TestODBC");

            //// Close the database
            //access1.CloseCurrentDatabase();

            //// Quit MS Access
            //access1.Quit(Access.AcQuitOption.acQuitSaveAll);

            //Response.Write("successful");

Select allOpen in new window

I used A2003.  That explains it.

@aikimark, i didn't get you !

Could you please explain little briefly ??

Are you tried to open .mdb file on Access 2003? Have you able to view the Saved Import Feature ? Have you able to see the connection string mentioned over there ?

Have you able to view the VBA Macro code over there ??

Experts, As this question is opened for a long , i am not able to post new questions ..!!!

You should be able to ask new questions regardless of the status of this one.
You could also ask to have this question closed without accepting an answer by using the request attention link up in the original post.
Or you could answer this
<
You mention C#
The language used by Office is VBA (Visual Basic for Applications) not C#

Just how or why is C# involved?
>

and perhaps get your inquiry moving again.
I am an MS Access expert.
I do not do C#
I do not work with Visual Studio
If you are attempting to do something in a VS 2008 project that involves an MS Access databse I am not the expert to help you
The programming language of MS Access is VBA, which is my thing.
Please clarify what environment you are programming in

Thank you Expert,

Let's discuss only about MS Access / VBA Macro

1)  I used C# to invoke the Saved Import Feature on MS Access. So this application has certain percentage of usage of C#.


C# Application   calls --->   Saved Import  feature in MS Access


What i am expecting is , Instead of invoking the Saved Feature inside the MS Access, we need to do it through VBA Macro.

For Importing data to MS Access , I need to Save the feature through VBA Macro.

Our C# Application ( Parent Application ) will call the source code of VBA Macro and does the functionality of Import

@Nick67


I guess, your understanding about the posting is entirely different.

1)

This part of the Syntax works great only by running directly from Source Code.

DoCmd.RunSavedImportExport "Wizard Invoice Lines"
should work.
So, you already seem to have the syntax in place
//access1.DoCmd.RunSavedImportExport("TestODBC");

2) As you are not from .Net Background, I am trying to explain the negative side of this syntax.  We have implemented this part of code in ASP.Net Web Application. On deploying / hosting it from Server and mapping it to the databases, this part of syntax will not work. There are several technical reasons behind that syntax.


We are looking an alternate approach or calling the VBA Macro directly instead of calling the Saved Method.

Clearly there is a failure to communicate clearly
<We are looking an alternate approach or calling the VBA Macro directly instead of calling the Saved Method. >
To an MS Access guy, this is gibberish.
It leaves me guessing as to what you are attempting.
<This part of the Syntax works great only by running directly from Source Code.>
Does this translate to meaning that
DoCmd.RunSavedImportExport "TestODBC"
works properly when run from MS Access itself, but does not work if you are attempting to automate Access through C# code?

Notice that these syntaxes are NOT the same:

access1.DoCmd.RunSavedImportExport("TestODBC");
and
access1.DoCmd.RunSavedImportExport "TestODBC";

Are you able to get your C# code to run any kind  of Access VBA code?
Even something as simple as MsgBox "I can get something to fire"
That's kind of a prerequisite, that your automation code can get something simple going, and then tackling your import

If you are looking to get away from DoCmd.RunSavedImportExport because you can do Access automation code, but just not this syntax, then you have not provided nearly enough information.



 

@Nick67, Sounds like you are not familiar with MS Access.

I am looking for experts , please leave and give the option for others to exchange the comments

218 qustion answered, 330,000 points and a Wizard rank in MS Access, never mind knocking the product around for a decade != <Sounds like you are not familiar with MS Access>
But hey, if you want to find somebody else whose going to try and make sense of an OP whose asking "how do I write C# code to perform a SavedImport in MS Access,"  you go ahead.
You can have a gander here
http://support.microsoft.com/kb/317113

Good luck!

On Comment ID: 36530360, I have clearly mentioned that "Let's discuss only about MS Access / VBA Macro"

Title of this posting also says MS Access 2007

On your Comment  ID: 36530745 - " I do not have Access 2007+ installed at work. "

So, @Nick67, You are trying to give an answer by guessing or just want to say something ..!!!

<I do not have Access 2007+ installed at work.>
Access 2003 at work, being a professional developer, I need to work i the lowest common denominator.
At home and at play I have Access 2010.
The shims that I post here, I build in Access 2003 so I don't have compatibility problems.
I remind you gently that in ID:36358590 I understood immediately what your sample database contained.
<just want to say something>
I have no need to just say something.  I enjoy solving puzzles and applying troubleshooting skills.
You have a problem.  I'd like to help you solve it.

So let's stick to that.
Now, after 32 posts, let's see if we can get to the heart of your problem.
You are trying to use Office Automation from C# to get the following command (native to Access) to execute correctly.
DoCmd.RunSavedImportExport "TestODBC"

The first step in troubleshooting is--can you get that command to execute correctly from within Access itself?
If you are able to get that line of VBA to execute successfully the next step is--can you get any Access VBA code to execute from your C# code?

Let me know the answers to both of these questions

Let me start this posting from beginning for your understanding and let me ask step by step. Please ignore C# and let's talk only about MS Access 2007.

Please check the Image enclosed on the posting.

In Access 2007, We have Saved Import / Saved Export. You can view that feature on the Image.

It shows Name of the Saved Connection and Connection String.

Inside the Access, there is VBA Macro code. We can programmatically write Macro code for Saving the same features.

Can you able to share the piece of VBA Macro Code for this saved feature / Or you can share any other sample of Saving Export / Import feature through Macro code.

Alright.
I am downloading VS 2010 Express C# at the moment to play with Office automation code.
I can see from here
http://support.microsoft.com/kb/302902
That the code you posted in ID:36358646 is very different.
No matter.

I have opened your sample in A2007, and looked at your saved import.
Because I don't have your ODBC item, it of course doesn't run.
But
http://stackoverflow.com/questions/143420/how-can-i-modify-a-saved-microsoft-access-2007-import-specification
Says I should be able to turn on Hidden and System tables and see MSysIMEXspecs and MSysIMEXColumns.
Which should contain the details of your import.

Those tables aren't there, which means your export is something very different...or your sample is defective.
Now, VBA != Macro

There are a myriad ways to accomplish an import via VBA code.
DoCmd.Database
DoCmd.TransferSpreadSheet
DoCmd.SQLDatabase
DoCmd.TransferText
amongst them.
But since you are having problems with
DoCmd.RunSavedImportExport "TestODBC"
and they all have similar syntax there really isn't much point in getting into those until we confirm that your C# can run any Access VBA code.

And then there's ADO.
Conceivably an ADO connection to your source data could be opened and an ADO connection made to your target Access db and your data can be blow in that way.
And you could probably write that all in C#
And maybe that's what you want to do.
And maybe you were hoping one of us could rip open your sample.mdb and see what the specs for "TestODBC" were and plop out some VBA code to transmogrify to C#.
Nope.
Not happening.
MSysIMEXspecs and MSysIMEXColumns aren't there.
I got no idea what "TestODBC" is doing.

You can walk through the import wizard again, and save screenshots of each step.
Maybe then I'll have something to work with.

Playing with Access 2010 this evening I created an ODBC import spec.
The MSysIMEX... tables do not get created.
So there was nothing wrong with your sample
I am unable to create a working aspx page that will open Access.
That also appears to be discouraged
http://support.microsoft.com/kb/257757
In researching I have come across your postings on stackoverflow.

You were under the idea that the mechanism of SavedImports creates some kind of code.
It does not.
If the simple syntax of
DoCmd.RunSavedImportExport "TestODBC"
does not work then you are left with trying to write your own ADO solution.

Instead of importing the tables, you can try linking to them.
The link tables will have the ODBC connection string in them.
That may help you.

At this point, you should post your FairCom dat files, directions to the FairCom documentation, and your C# project in a new question along with a MUCH more verbose description of what challenge you are trying to overcome.  You have given me much too little to work with.  Like the posters at stackoverflow, I cannot help you with a solution with the details you have provided.

Thanks,
Nick67

@Nick67, Please forget about Visual Studio ..!! Please don't talk about .Net or C#.

Lets focus only on MS Access.

In Access 2007, We have Saved Import / Saved Export. You can view that feature on the Image.

It shows Name of the Saved Connection and Connection String.

Inside the Access, there is VBA Macro code. We can pro-grammatically write Macro code for Saving the same features.

Can you able to share the piece of VBA Macro Code for this saved feature / Or you can share any other sample of Saving Export / Import feature through Macro code.

Try this
Sample.mdb

@Nick67, Thanks ..!!

I feel, we are nearing the issue ..!! Let me pull this module and form to my main database and test it and get back to you ..!!

This is great efforts, Thanks Nick !!

Comment ID: 36537104 , Thanks.

Now, i understand Access Form / Module output can be taken either as .exe of Active X DLL - COM output.


 


untitled.JPG

Comment ID: 36537104 , Thanks.

Now, i understand Access Form / Module output can be taken either as .exe of Active X DLL - COM output.

Could you please help me to get this output as Active X DLL ( COM ) which does the function of Deleting Old DB, Creating New DB and Importing Tables ..!!

If i am able to get the output as Active X DLL, I can try to call the DLL / COM from my Parent Application

untitled.JPG

I have ZERO idea of what you are talking about.

Let me make it easier ..

You kept two buttons on the Form..

1) Import Manual
2) Import SavedMethod

At present, if i want to run the form i need to go to Source Code and then run the application.
Can we get the output as .exe ( as an executable format ) which can bring the form automatically .. and on clicking the form .. will import the database.

Technically, we cant give the database to the users .. we can give only the form ..!!

How could we generate .exe and keep the .exe away from Database ?

I have 0.000000001 idea of what you are talking about.
I put the form and module in to show you the VBA syntax for the commands to accomplish what you wanted.
Now you need to do the C# to get it done

I have built a C# ASP.NET page in a project 'testAccess', and thrown button1 on it and I can get it to open the database at m:\Test\TI_Prog.mdb
It opens a form and turns over control to the user.
As I am running Access 2003 I cannot test the RunSavedImportExport at the moment
But the syntax is there  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Access=Microsoft.Office.Interop.Access;
using System.Reflection;

namespace testAccess
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
        // Start a new instance of Access for Automation:
        Access.Application myAccessInstance = new Access.Application();


        // Open a database in exclusive mode:
        // Replace "M:\\test\\TI_Prog.mdb" with your own database
        myAccessInstance.OpenCurrentDatabase(
           "M:\\test\\TI_Prog.mdb", //filepath
           true //Exclusive
           );
        //for testing I want to see and control Access rather than quitting or hiding it
        myAccessInstance.Visible = true;
        myAccessInstance.UserControl = true;
        //myAccessInstance.DoCmd.OpenForm ("frmCertStatus", Access.AcFormView.acNormal);
        myAccessInstance.DoCmd.RunSavedImportExport("TestODBC");



        }
    }
}

Select allOpen in new window

Try it on a C# ASP.NET page

@Nick67, You said you are MS Access Expert and have 0% Knowledge on C# and now you jumped into C# by saying 0.00001 idea on MS Access.

F. Y. I - Access 2002-2003 format  is .mdb
and Access 2007 format is .accdb

So, here we are talking only about .mdb, and your posting say that you have .mdb on your computer.

Well, @Nick67 - Let me explain the issue ..!!

I tested your code and i pasted the exact code in this Code Snippet.

You have commented MS Access form and calling the Saved Method - My Source code also does the same.

What you have to notice in this code is, Please publish the project and deploy it on your local IIS / Laptop or any other computer which has Office.

Run the project from there - click the button which is on ASP.Net Page .. !!

Issue is, Application works great from Source Code, but it is not working on deployed version. Instead of calling the Saved Method in Access DB, we need to call the form of Access DB.

We need to invoke the form of access db from ASP.Net and test the application ..!!!

// Start a new instance of Access for Automation:
        Access.Application myAccessInstance = new Access.Application();
        myAccessInstance.OpenCurrentDatabase("c:\\ReportHost\\App_Data\\Salisbury.mdb",true,"" );
        
        myAccessInstance.Visible = true;
        myAccessInstance.UserControl = true;
        
        //myAccessInstance.DoCmd.OpenForm("frmImport", Access.AcFormView.acNormal);
        myAccessInstance.DoCmd.RunSavedImportExport("TestODBC");

Select allOpen in new window

I troubleshoot.
Since you are trying to use Access from C# the only way to ensure I am not talking out of my arse is to try to do that too.

<F. Y. I - Access 2002-2003 format  is .mdb
and Access 2007 format is .accdb>
Trust me.  Access 2007+ can create and read accd* files.  Access 2003 and lower cannot
Access 2007+ can read and create adp, md* and accd* file types.
Access 2010 can work with any valid file type

<Application works great from Source Code, but it is not working on deployed version>
That is the first time you have said that you actually have Access working from C#
This
<How could we generate .exe and keep the .exe away from Database ? >
I have 0.0000001 idea about.

<Please publish the project and deploy it on your local IIS / Laptop or any other computer which has Office.>
I only have a vague idea how to do so, and I will not be mucking up my production server to test your problem.
Volunteerism only goes so far.

Now,
Dumb question.
The deployment server does have Access installed, right
After all, you are automating Access.  So if it is not installed on the server, that could be why it works on your Dev unit, but not in deployment

You may also be up against all the rejoinders mentioned here
http://support.microsoft.com/kb/257757
And all of this has been for nothing.

If that is so, then you must do all of this, not through Access automation, but through ADO
And that needs to be another question and a different set of Experts

@Nick67, Technically you have not answered or responded a direct answer for our posting !!

If you want, I can post the issue step by step and go further.

Your Comment ID: 36544278, works when we execute directly from Source Code but its not working in Deployed Version. Your Source Code and my Source Code are same.

Because, we both are calling the SAVED METHOD IN THE ACCESS DATABASE.

Your Comment ID: 36544278, Source Code is not working when we publish / deploy the application on IIS.

If you want to continue , i am willing to break down the issue and explain it to you step by step !!!

<I will not be mucking up my production server to test your problem>
I am a volunteer

<i am trying to rewrite the feature in C# which will call ODBC Connection to Import Data to MS Access Database>
I worked out C# code to do that for you
I worked out the VBA TransferDatabase syntax that would allow you to avoid DoCmd,RunSavedImportExport
Please don't say
<Technically you have not answered or responded a direct answer for our posting>
Because I have.

<works when we execute directly from Source Code>
New problem
<Source Code is not working when we publish / deploy the application on IIS>

Possible troubleshooting step
<The deployment server does have Access installed, right?>

Possible showstopper
<http://support.microsoft.com/kb/257757>

Likely step that will be required if the present approach is impossible
<If that is so, then you must do all of this, not through Access automation, but through ADO>

<If you want to continue , i am willing to break down the issue and explain it to you step by step !!! >
If you wish to go further, you may spell out in clear language what you have done.
Do me the courtesy of <responded a direct answer for> every question I have posed to you

Also do me the courtesy of trying to understand my responses:
There is no underlying Access VBA code or macro to pull forth other than

DoCmd.RunSavedImportExport "TestODBC"
or
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=Salsibury;DBQ=S:\;CODEPAGE=1252;", acTable, "WhateverTabelYouAreTryingToImport", "WhateverNameYouWantToGiveIt"

<Can we get the output as .exe ( as an executable format ) which can bring the form automatically .. and on clicking the form .. will import the database.
Technically, we cant give the database to the users .. we can give only the form ..!!
How could we generate .exe and keep the .exe away from Database ? >
I am an Access database developer.  I have no idea how you would create an exe or dll to do this for you

<I am trying to explain the negative side of this syntax.  We have implemented this part of code in ASP.Net Web Application. On deploying / hosting it from Server and mapping it to the databases, this part of syntax will not work. There are several technical reasons behind that syntax.>
The only other Access VBA syntax that will do what you want is the exact same oAccess.DoCmd. that seems problematic.

So this may have been a complete waste of my time and yours.
Although I may have learned that I really don't want to automate Access through a C# web page <grin>

@Nick67 : I will not be mucking up my production server to test your problem>
I am a volunteer

I understand, Thank you for your help.

As i mentioned on my previous post, You have wrote the same syntax of my original posting. We both are executing the SAVED METHOD IN THE ACCESS DATABASE.

I am looking for experts to resolve the issue / posting ..!!

This
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=Salsibury;DBQ=S:\;CODEPAGE=1252;", acTable, "WhateverTabelYouAreTryingToImport", "WhateverNameYouWantToGiveIt"
is not executing the saved method.
It does something different
But you have not provided me with any feedback about your attempts to first make that syntax work in Access, and then make that or any other syntax work in C#
For example, does this work
myAccessInstance.DoCmd.OpenForm("frmImport", Access.AcFormView.acNormal);

If it does not, then basically the whole enterprise is kaput
And then you are left with doing the whole nine yards through ADO.
And basically all the mdb is in that scenario is a datastore.
And you don't need to talk to MS Access specialists

Your Syntax :


myAccessInstance.DoCmd.OpenForm("frmImport", Access.AcFormView.acNormal);

Throws an exception as ..

No overload for method 'OpenForm' takes '2' arguments      

Nick67, if you wish to help , again i'm repeating please allow experts to comment !!

On one comment you say that have 0% knowledge on C# and on the next comment you are providing syntax for c#... and one comment you are saying that you have 0.00000001 idea on MS Access ..!!!

Thank you for your help !!

<gently>
You started this question on 22-Jul-11
You've had it bumped once.
Nobody but me is paying attention.
Which is why I have suggested <very gently> multiple times</very gently> that you start a new question
</gently>

If I stop responding, in 21 days a clean-up volunteer will come by and mark this as abandoned
I left your question alone between 12-Aug-11 and 7-Sep-11.
Nobody else posted.
Nobody else will.

<On one comment you say that have 0% knowledge on C# and on the next comment you are providing syntax for c#... >
Are you not suitably impressed that I installed a product I have never used, and using a syntax I find despicable created code that works on both my machine and your dev machine?  I have gone to extraordinary lengths to help you.
<and one comment you are saying that you have 0.00000001 idea on...>
Your statement
<How could we generate .exe and keep the .exe away from Database ? >
I think you are talking about creating a COM add-in or ActiveX dll, neither one of which has anything at all to to with MS Access or VBA

I have repeatedly asked you for verbose and detailed feedback to your activities.
You never give it to me.
That makes this VERY frustrating, as you keep me completely in the dark.

<No overload for method 'OpenForm' takes '2' arguments>
At last, a smidgen of feedback!
Very well,
I suspect that oMissing is what you may be needing, and the list of arguments may need to be filled in entirely in C#, where in VBA they are optional with assumed defaults.

This code snippet implements oMissing and is fully verbose with arguments.
It compiles and executed on my dev machine

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Access=Microsoft.Office.Interop.Access;
using System.Reflection;

namespace testAccess
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
        // Start a new instance of Access for Automation:
        Access.Application myAccessInstance = new Access.Application();

        // Object for missing (or optional) arguments.
        object oMissing = System.Reflection.Missing.Value;


        // Open a database in exclusive mode:
        myAccessInstance.OpenCurrentDatabase(
           "M:\\test\\TI_Prog.mdb", //filepath
           true //Exclusive
           );
        myAccessInstance.Visible = true;
        myAccessInstance.UserControl = true;
        myAccessInstance.DoCmd.OpenForm ("frmCertStatus", Access.AcFormView.acNormal,oMissing,oMissing,Access.AcFormOpenDataMode.acFormEdit,Access.AcWindowMode.acWindowNormal,oMissing);
        //myAccessInstance.DoCmd.OpenReport ("myreport", Access.AcView.acViewNormal);
        //myAccessInstance.DoCmd.RunSavedImportExport("TestODBC");



        }
    }
}

Select allOpen in new window

Disregard last, I posted to the wrong question.....my bad

@BKennedy2008, Whether Comment Id # 36561406 is for me ?

@chokka
No he posted here by error.
I helped him create automation code to open an MS Access report from a VB ASP.NET web page here
https://www.experts-exchange.com/questions/27310498/Binding-a-textbox-from-a-access-query.html?cid=1572&anchorAnswerId=36561445#a36561445
Since that is essentially a big chunk of what you are doing, I cross-referenced him to your problem.

I'm checking that link , it also has a blog posted on it ..!

I will update !!