Dataset with Access Database

Hi Experts,

I am using the dataset to add/delete/update recrods in MS Access database. When I am updating large # of records (say 10,000), it is taking 4 minutes to update. Is there any way I can make it quicker?  The first field is the table is the key field. I can not use SQL server which was much faster in my other application.

Here is the code..
Dim mDataAdapter As OleDb.OleDbDataAdapter
 Dim cmdBuilder As OleDb.OleDbCommandBuilder
Dim strConnAcc As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & "FACS.mdb"
mDataAdapter = New OleDb.OleDbDataAdapter("select * from TwoD", cnnacc)
            cmdBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
            mDataAdapter.FillSchema(ds, SchemaType.Mapped, "tbl2d")
            mDataAdapter.Fill(ds, "tbl2d")
            Dim Twodtbl As DataTable = ds.Tables("tbl2d")
For i = 0 To 10000
                Dim dr As DataRow = Twodtbl.NewRow
                dr.Item(0) = Format(i, "00000000000000")
                dr.Item(1) = "Field1"
                dr.Item(2) = "Field2"
                dr.Item(3) = Format(Now, "MM/dd/yy")
                Twodtbl.Rows.Add(dr)

            Next
            mDataAdapter.Update(ds, "tbl2d")
            cnnacc.Close()
sadimulAsked:
Who is Participating?
 
CodeCruiserCommented:
Create a schema.ini file in the same directory with following contents

[file.csv]
Format=Delimited(|)
0
 
Chandan_GowdaCommented:
you can do a bulk insert. Please follow the below link

http://www.codeproject.com/KB/database/BulkRecordInsertAccess.aspx
0
 
sadimulAuthor Commented:
This is not faster than what I was doing
0
 
CodeCruiserCommented:
Here is an example of Actual bulk insert into Access from a text file

http://www.vbmonster.com/Uwe/Forum.aspx/vb-ado/688/Bulk-Insert-into-ms-access-table-from-text-file

0
 
sadimulAuthor Commented:
Is there anyway I can use a pipe delimited file instead of CSV file? Appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.