Bulk Insert into MSAccess thru vb.net
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great and informative . The actual issue was the indexes . It is fine now
Kelvin