How can I accelarate my Database-Update ?

[Paulus]

I have an Acess 97 Recordset and want to update it from an AS/400-Recordset and it takes 24 hours !!! with an ado-connection. Do you know how to accelerate the process without using stored procedures ?

 

[petermeachem]

Do you know where the slow bit is. From as400 or to access.
How much data are you updating? Peter Meachem
peter@accuflight.com

 

[Paulus]

I don´t know which one is slow.
I try to update 4400 records

 

[petermeachem]

Thats ridiculous. I thought you were going to say 44,000,000. Are you doing a lot of calculations? How are you doing the update?
Peter Meachem
peter@accuflight.com

 

[Paulus]

I open both recordsets as commands from the Dataenvironment, the Access-RS as readonly, the AS/400-RS as read and write. Then move to the first record in the Access-RS, and ... (de=Dataenvironment)

with de.rsAccess
while not.eof
de.rsAS400.addnew
de.rsAS400.fields("test&quot.value = .fields("test&quot.value
de.rsAS400.update
wend
.close
end with
de.rsAS400.close

But, what´s wrong and why is it so slow ?

 

[shope]

Have you tried using a command object that has an INSERT INTO statement in it and uses parameters to pass the values?

I am not sure if that will help but it may be worth a try

i.e. your command object's sql is

INSERT INTO rsAS400table (TEST)
VALUES (prmTEST)

and your code goes something like this

with de.rsAccess
  while not.eof
    de.rsAS400.Parameters("prmTEST") = .fields("test").value
    de.rsAS400
  wend
  .close
end with

maybe worth a try
Scott Hope
shope@gov.mb.ca