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!

--

Reggie

[Elton W]

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
el********@hotmail.com

"Reggie" wrote:

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!

--

Reggie

[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
"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:4d********************@comcast.com...

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!

--

Reggie

[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!

--

Reggie
"Elton W" <El****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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
el********@hotmail.com

"Reggie" wrote:

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!

--

Reggie

[Elton W]

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

"Reggie" wrote:

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!

--

Reggie
"Elton W" <El****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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
el********@hotmail.com

"Reggie" wrote:

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!

--

Reggie

[Elton W]

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" wrote:

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!

--

Reggie
"Elton W" <El****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

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
el********@hotmail.com

"Reggie" wrote:

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!

--

Reggie

[Reggie]

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

--

Reggie
"Elton W" <El****@discussions.microsoft.com> wrote in message
news:D5**********************************@microsof t.com...

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" wrote:

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!

--

Reggie
"Elton W" <El****@discussions.microsoft.com> wrote in message
news:78**********************************@microsof t.com...

> 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
> el********@hotmail.com
>
> "Reggie" wrote:
>
>> 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!
>>
>> --
>>
>> Reggie
>>
>>
>>