Database transfer between Access and SQL

[BuilderSpec]

Hi

Have a program I am writing and part of it is :

Run query on Access table to produce set of results
Add same results to same table in SQL Server

I have coded it as a ADOQuery for the Access and either ADOTable or ADOQuery for the SQL Server.

I read each record then loop round the fields copying values from the Access to the SQL then Post the SQL .

I was wondering if there was some other slick way to take the ADOQuery with the Access results and add those records to SQL Server table.

I have considered using a ADOCommand to perform the insert but that is still at record level.

Any ideas ? Mine works but is slow.

Regards

Graham


Hope this helps!

Regards

BuilderSpec

 

[2ffat]

I read each record then loop round the fields copying values from the Access to the SQL then Post the SQL.

That's how I do it when I use two different DBs. I would be interested if there is a better way, too.

Once thing to look at is if you are closing the DB between writes. Opening and closing them tends to slow the writes down. Also transaction files can be slow.



James P. Cottingham
[sup]I'm number 1,229!
I'm number 1,229![/sup]