Dataset with Access Database

Posted on 2011-10-17
Last Modified: 2012-05-12
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")

            mDataAdapter.Update(ds, "tbl2d")
Question by:sadimul
    LVL 7

    Expert Comment

    you can do a bulk insert. Please follow the below link

    Author Comment

    This is not faster than what I was doing
    LVL 83

    Expert Comment

    Here is an example of Actual bulk insert into Access from a text file


    Author Comment

    Is there anyway I can use a pipe delimited file instead of CSV file? Appreciate the help.
    LVL 83

    Accepted Solution

    Create a schema.ini file in the same directory with following contents


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now