Append Data

[Reggie]

Hi and TIA. I have an OleDB connection to an Access db which I connect to
and place data from a table into a recordset. What I want to do is copy
this recordset to an SQL table. I have no problem connecting to the SQL
table but can't figure out how to pump the data from the recordset into the
table. Was wondering how to do it and what would be the fastest procedure.
The recordset usually consist of 20 to 100K records. Thanks very much for
your time!

 

[Guest]

Hi Reggie,

The simplest way is to loop thru recordset and create Insert sql command to
insert data into the DB. However, it’s slow especially for large records of
data. Instead, you can create middle step text data file (using StringBuilder
can improve performance), then apply bulk insert.

BTW, why do you use Recordset rather than DataTable?

HTH

Elton Wang
(e-mail address removed)

 

[Reggie]

Also, If I could simply loop through my dataset, grab each record and run
an Update query that would be fine. Can't figure out how to setup the loop
and grab the data. Any advice or pointers, or examples are appreciated.
Thanks again for your time!

 

[Reggie]

Elton, I say recordset (old VB habit), but I'm actually using a dataset
filled by a MS ASccess DataTable. Would you happen to have any example of
this process cause I can't find anything on looping through the dataset. In
vb I could use do until rst.eof but doesn't seem to work on the dataset.
I'll look into your second suggestion. I came across this in my research,
but thought the looping would be faster. Goes to show you what I know.
Anyway thanks much for your time!

 

[Guest]

Actually, data is in DataTable. You should loop thru datatable:

Dim name As String
' Suppose the first table. Or you can use dataset.Tables("table_name")
For Each row As DataRow In dataset.Tables(0)
name = row("Name").ToString
' orther field

Next


Elton

 

[Guest]

Some thing wrong in previous post

Code should be
For Each row As DataRow In daset.Tables(0).Rows
name = row("Name").ToString
' orther field
Next

Elton

 

[Reggie]

Elton thanks for setting me straight and for the code snippet. Very much
appreciated.