?
Solved

Bulk Insert  into MSAccess thru vb.net

Posted on 2014-02-23
4
Medium Priority
?
977 Views
Last Modified: 2014-02-24
Hi Experts,

   I am developing a vb.net routine to  get data from oracle database (from multiple tables)   into a single Table of MS Access  ( 2003 Enterprise)

The pseudo code is like

    Query Oracle
    Get result in a OleReader
     Loop thru each row
        Insert Single row  into  Access Table  with OleDbcommand (using Sql Insert statement)
     End Loop


  The operation takes 15-20 minutes ( If performance can improve , it is desirable . But I am happy with it even otherwise )
 
 My real  issues is , the query result from oracle  has  700-800 thousands of  records .  
 As the routine is running , the Access size is increasing . And finally ,  it doesn't insert rows ( certainly  , the 2GB limit on MS access is the culprit)  . But when I compress, it reduces the size to 500 MB  .
 But I can't do this compression dynamically thru  the code  when the code is running (because the Table is locked)


  If there is some bulk insert source code  ( that meets my purpose instead of looping thru each rows )  , which will not increase the size dramatically - That is what I am looking for .

 Also, if the compression someway can be done dynamically ( which I really doubt can be done) , that also can be a solution

  Appreciate  your help  

 Sam
0
Comment
Question by:Sam OZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 800 total points
ID: 39881664
I have struck that as well (many years ago). The size comes from Access paging the data table as records come in to allow data to be "inserted" according to indexing. Check the target table and remove all indexes, other than the PK.  That MAY help you.


Kelvin
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39881665
If there are any relationships on the table, drop them as well (add them all back later).


Kelvin
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 400 total points
ID: 39881753
There is an UpdateBatchSize that you can set on a DataAdapter, but it works only with databases that supports Batch inserts, and Access does not. This is one of the many limitations of Access.

And the reason that you cannot compress by default because the database must be closed and not in use when you do so. And ADO.NET has a connection pooling mechanism (you can look up "connection pooling" in the documentation if you are curious) that keeps the connection opened for a while (60 seconds by default) even when you Close it explicitely in your code.

The solution would be to disable connection pooling and close the connection / compress / reopen the connection from time to time during your update. But unfortunately, once again, it is not possible with Access because the OleDBConnection does not have the ClearPool method that is available in other ADO.NET classes such as the SqlConnection.

If you want to be working with that amount of data, you will end up being frustrated by the limitations of Access. Why not move your stuff to SQL Server Express? It's free and more powerful. And if need be, Access can connect to it and be used almost as usual for Forms and Reports.
0
 

Author Closing Comment

by:Sam OZ
ID: 39881860
Great and informative  . The actual issue was  the indexes . It is fine now
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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