Bulk Insert Access from Dataset

Question

I have been following the code from Bulk Insert However I am getting an exception of Invalid Argument at AccesssRecordset.Update(); or rs.Update();

It takes two arguments Int UpdateType and bool Force

public void BulkExportToAccess(DataTable dtOutData, String DBPath, String TableNm) 
{ 
DAO.DBEngine dbEngine = new DAO.DBEngine();
Boolean CheckFl = false;

try
{
    DAO.Database db = dbEngine.OpenDatabase(DBPath);
    DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm);
    DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Rows.Count];

    //Loop on each row of dtOutData
    for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++)
    {
        AccesssRecordset.AddNew();
        //Loop on column
        for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++)
        {
            // for the first time... setup the field name.
            if (!CheckFl)

          AccesssFields[colCounter] =                                               
          AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName];
            AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter];
        }

        AccesssRecordset.Update();
        CheckFl = true;
    }

    AccesssRecordset.Close();
    db.Close();
}
finally
{
    System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine);
    dbEngine = null;
 }
 }

Can anyone help

Thank you

Can you post your code? There's a lot of code on the link, would help if you could post the relevant code and point out where you are getting an error — JMK, Feb 21 '13 at 12:28
@Remou I didnt get you But i'll try to explain the DsoutData is coming from Access and the TableNm is the name of the table Which is in my Mdb Hope it helps — Rohit, Feb 21 '13 at 12:40
Are you getting your error on the first iteration of your loop, or on subsequent iterations? Have you tried moving `AccesssRecordset.Update();` to the outside of your for loop? — JMK, Feb 21 '13 at 12:45
Yes I am getting Error at the First Iteration..Also I have deleted all the rows from the table in which I have to insert — Rohit, Feb 21 '13 at 12:49
The problem is that there is almost never any reason to run code such as included in the link. You can simply run SQL to update one table from another, create a table from another, copy a table from one Mdb to another mdb, etc, and across multiple database and text types. It seems that you want to copy a table from one mdb to another, is that correct? — Fionnuala, Feb 21 '13 at 12:53
Where are you actually adding to the recordset? Apart from the new row at `AccesssRecordset.AddNew();`, you add data to your AccessFields array, put then never put this data into the row, am I missing something? — JMK, Feb 21 '13 at 12:56
Also, as @Remou said, there is no need to use code like this, you can indeed run SQL to update one table from another. — JMK, Feb 21 '13 at 13:02
I changed the Force type to False there was no error But the data wasn't inserted any Clue? — Rohit, Feb 21 '13 at 13:21

score 1 · Answer 1 · answered Feb 21 '13 at 12:26

1

Okay, so the OpenRecordset creates a Recordset object. The UpdateType it's looking for is from the UpdateTypeEnum; you can just send a 1. And as far as the Force goes, I think in your case you probably want to send true. What that means is if there was somebody else that updated one of those records, their changes would not be considered and possibly lost because your updates would be forced.

answered Feb 21 '13 at 12:26

Mike Perrenoud

66,820
29
157
232

I have 103 Columns and 7823 ROws When I enter AccesssRecordset.Update(1, true); I get an Invalid Argument Error – Rohit Feb 21 '13 at 12:29
@Kyle, are you sure that's where the error is occurring? That's the same error you were getting before - and interestingly enough those parameters are actually *optional*. – Mike Perrenoud Feb 21 '13 at 12:34
Yes I did the Debugging and at that same point I am getting Invalid Argument Error – Rohit Feb 21 '13 at 12:37

Bulk Insert Access from Dataset

1 Answers1