How to re write Office Automation Code

I have a Saved Import Feature in Access Database which invokes ODBC Connection. Instead of depending on In-built Saved Import feature, i am tryin开发者_C百科g to rewrite the feature in C# which will call ODBC Connection to Import Data to MS Access Database.

I am not able to call the connection string which is saved in the Access Database which calls ODBC Drivers. I keep failing .. !!

    Access.Application access1 = new Access.Application();
    try
    {

        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");



    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }

To me it seems you are using automation in IIS (Server.Mappath... ans Response.Write...) ?

IF this is the case THEN this is NOT supported by MS - see http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

Reasons are several: starting from security and not ending with the fact that office was never built for such server usage scenario...

I don't understand why you are not just using ODBC from C# instead of MS Access ? Perhaps you could show some more surce code or the exception/error message... ?

EDIT - after long discussion (see comments):

IF you need to access the Faircom DB then add a using System.Data.Odbc; and try

OdbcConnection DbConnection2Salisbury = new OdbcConnection("DSN=Salisbury;DBQ=S:\;CODEPAGE=1252;");

DbConnection2Salisbury.Open();

After this you are able to use ADO.NET to query etc. the Faircom DB i.e. with OdbcCommand and OdbcDataReader etc.

For some information see http://msdn.microsoft.com/en-us/library/system.data.odbc.aspx
For sample code see http://www.easysoft.com/developer/languages/csharp/ado-net-odbc.html