Performance question

[John]

Hi!

I have a question that I would be very happy to get some input about.

This is the case: I want to develop an application that reads records from
an Access database and then inserts these records into another database
(DB2, but that's unimportant for my question).

At this moment I first read all records into a VB Collection and after that
I loop through this collection and insert into the second db. My first
question is if someone have any clue if this procedure takes more/less time
compared to if I should have choosen a solution where I insert every record
into the other db after each one is read from the Access db (in that case I
don't need to use a Collection to store the data in). I can't see the reason
why my solution should take more time, but I'm not sure...

My second question: I have made a progress bar so I can monitor the read
operation from the Access db to the VB collection. The problem is that the
read operation is decreasing in speed all the way through. I guess that this
is due to that the available RAM decreases, any other explanations? Is there
any possibility to solve this or at least make it go faster? I didn't think
that the speed should decrease that much since the data I read into the
collection is only allocating approximately 2-6 MB?

VERY grateful for suggestions about these questions.
/John

[Phil Weber]

> This is the case: I want to develop an application that reads
> records from an Access database and then insert these records
> into a DB2 database. At this moment I first read all records into
> a VB collection and after that I loop through this collection and
> insert into the second db.

John: This seems like an extremely inefficient way to copy data from one
database to another. Why not simply attach your DB2 table(s) to an Access
database and use a SELECT...INTO query to copy the data?
---
Phil Weber

[John]

Hi and thx for your answer!

Can you explain a little bit more about how you mean I should do this. I
have to have some sort of application (preferrable VB) " in between" the
databases. The application is for an administrator that should be able to
(for example) choose from where to read data and to which db2 to copy the
data. Another thing that I need the VB application in between for is to make
sure that the format of each field is correct when it get's stored in the
DB2 database. For example: If I have a textfield that has it length set to
30 and the field only consist of the string "teststring", when the data is
copied into DB2 I want to make sure that "teststring" is filled out with
blanks..i.e the string must be like "teststring ". As far as I understand
I need this VB-app in between to take cara of such things...Please let me
know if know some other way to do it.

Please give me more input about this if you can....

/John


>John: This seems like an extremely inefficient way to copy data from one
>database to another. Why not simply attach your DB2 table(s) to an Access
>database and use a SELECT...INTO query to copy the data?
>---
>Phil Weber
>
>