?
Solved

Dataset with Access Database

Posted on 2011-10-17
5
Medium Priority
?
241 Views
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")
                Twodtbl.Rows.Add(dr)

            Next
            mDataAdapter.Update(ds, "tbl2d")
            cnnacc.Close()
0
Comment
Question by:sadimul
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 36980149
you can do a bulk insert. Please follow the below link

http://www.codeproject.com/KB/database/BulkRecordInsertAccess.aspx
0
 

Author Comment

by:sadimul
ID: 36981065
This is not faster than what I was doing
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36985050
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
 

Author Comment

by:sadimul
ID: 36985547
Is there anyway I can use a pipe delimited file instead of CSV file? Appreciate the help.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 36988324
Create a schema.ini file in the same directory with following contents

[file.csv]
Format=Delimited(|)
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

864 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