VB.NET, How to insert rows from one table into another

[RG]

Using VB.NET, How do I insert rows from a SQL Server table into an Access
table with the same structure (and also the reverse, from Access to SQL)?

I’m new to this, so here’s what I’ve tried so far (unsuccessfully):

1. Fill Dataset ‘S’ from a SQL Data Adapter (many rows). I see it in a
DataGrid.
2. Fill Dataset ‘A’ from an Access OLE DB Data Adapter (just a few rows).
In a second DataGrid.
3. Merge dataset ‘S’ (SQLSrvr rows) into Dataset ‘A’ (Access). I see the
merged rows appearing in the Dataset ‘A’ DataGrid.
4. Update the Access OLE DB Data Adapter / Dataset ‘A’.

Nothing happens; it ignores the merged-in rows. I’ve lots of other ideas to
try, but I need some solid advice at this point.

Thanks.

Also, how are these postings sorted? They seem totally random.
--
RG

[Cor Ligthert [MVP]]

RG,

First of all, what are you trying to do, insert rows or update rows. That
makes a big difference in what you ask.

Although that the build in function from SQL server (if you not are talking
about MSDE of SQLExpress) for this is probably a lot easier.
Cor

"RG" <[e-mail]> schreef in bericht
news:5B**********************************@microsof t.com...

Using VB.NET, How do I insert rows from a SQL Server table into an Access
table with the same structure (and also the reverse, from Access to SQL)?

I'm new to this, so here's what I've tried so far (unsuccessfully):

1. Fill Dataset 'S' from a SQL Data Adapter (many rows). I see it in a
DataGrid.
2. Fill Dataset 'A' from an Access OLE DB Data Adapter (just a few
rows).
In a second DataGrid.
3. Merge dataset 'S' (SQLSrvr rows) into Dataset 'A' (Access). I see
the
merged rows appearing in the Dataset 'A' DataGrid.
4. Update the Access OLE DB Data Adapter / Dataset 'A'.

Nothing happens; it ignores the merged-in rows. I've lots of other ideas
to
try, but I need some solid advice at this point.

Thanks.

Also, how are these postings sorted? They seem totally random.
--
RG

[RG]

I'm trying to insert rows. Sorry, but I can't understand your second
sentence at all.
Thanks anyways for replying.
--
RG
"Cor Ligthert [MVP]" wrote:

RG,

First of all, what are you trying to do, insert rows or update rows. That
makes a big difference in what you ask.

Although that the build in function from SQL server (if you not are talking
about MSDE of SQLExpress) for this is probably a lot easier.
Cor

"RG" <[e-mail]> schreef in bericht
news:5B**********************************@microsof t.com...

Using VB.NET, How do I insert rows from a SQL Server table into an Access
table with the same structure (and also the reverse, from Access to SQL)?

I'm new to this, so here's what I've tried so far (unsuccessfully):

1. Fill Dataset 'S' from a SQL Data Adapter (many rows). I see it in a
DataGrid.
2. Fill Dataset 'A' from an Access OLE DB Data Adapter (just a few
rows).
In a second DataGrid.
3. Merge dataset 'S' (SQLSrvr rows) into Dataset 'A' (Access). I see
the
merged rows appearing in the Dataset 'A' DataGrid.
4. Update the Access OLE DB Data Adapter / Dataset 'A'.

Nothing happens; it ignores the merged-in rows. I've lots of other ideas
to
try, but I need some solid advice at this point.

Thanks.

Also, how are these postings sorted? They seem totally random.
--
RG

[Cor Ligthert [MVP]]

RG,

A SQL server can read direct an Access file.

If you want to do it with Net.

Just create your datatable however set before in the Oledbdataadatper
acceptchangesduringfill = false

http://msdn.microsoft.com/library/de...gfilltopic.asp

What is beneath I never did, however there is in my opinion nothing why this
would not go so you can try it.

sqldataadapter.fillschema(anotherdatatable)
sqlcommandbuilder(anotherdatatable)

And than inserting those
sqldataadapter(thedatatabletofill)

This assumes that both schemas are completly the same

I hope this helps,

Cor

[Paul Clement]

On Fri, 16 Sep 2005 16:26:25 -0700, "RG" <[e-mail]> wrote:

¤ Using VB.NET, How do I insert rows from a SQL Server table into an Access
¤ table with the same structure (and also the reverse, from Access to SQL)?
¤
¤ I’m new to this, so here’s what I’ve tried so far (unsuccessfully):
¤
¤ 1. Fill Dataset ‘S’ from a SQL Data Adapter (many rows). I see it in a
¤ DataGrid.
¤ 2. Fill Dataset ‘A’ from an Access OLE DB Data Adapter (just a few rows).
¤ In a second DataGrid.
¤ 3. Merge dataset ‘S’ (SQLSrvr rows) into Dataset ‘A’ (Access). I see the
¤ merged rows appearing in the Dataset ‘A’ DataGrid.
¤ 4. Update the Access OLE DB Data Adapter / Dataset ‘A’.
¤
¤ Nothing happens; it ignores the merged-in rows. I’ve lots of other ideas to
¤ try, but I need some solid advice at this point.

Why not just use the direct method:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders SELECT * FROM
[Orders] IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_ Connection=yes];", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)