multiple insert in access

[tpafr]

Hi,
I would like to insert multiple rows in an Access DB from a datagrid (or
a txt file)
It is very slow for large number of insertions(5000 -> >30 seconds !).
Is there another way to do this

myOleDbConnection.Open();
string sqlst="INSERT INTO TUsers (IDUser,Pwd,...) VALUES
(@IDUser, @Pwd, ...)";
OleDbCommand myCommand = new OleDbCommand(sqlst, myConn);
myCommand.Parameters.Add(new OleDbParameter
("@IDUser", ""));
myCommand.Parameters.Add(new OleDbParameter("@Pwd", ""));

foreach(GridEXRow drow in rows) {
myCommand.Parameters["@IDUser"].Value = drow.Cells
["IDUser"].Value.ToString();
myCommand.Parameters["@Pwd"].Value = drow.Cells
["Pwd"].Value.ToString();
....

try {
myCommand.ExecuteNonQuery();
}
catch{}
}
myOleDbConnection.Close();

Thanks in advance

 

[Ben]

I haven't used access in ages. For something like this in Sql server I'd
upload the data as xml, have a stored proc, parse it and then copy it
over... Maybe you can try something like that in access?

 

[Bruce Barker]

not using .net. the ado, ado.net, and odbc drivers lack the faster insert
mode. to get fast insert performance you need to use the native jet engine
(DAO), which is only supported by vb6 and access. you might be able to
pinvoke to it.

-- bruce (sqlwork.com)