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] m> schreef in bericht
news:5B******** *************** ***********@mic rosoft.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] m> schreef in bericht
news:5B******** *************** ***********@mic rosoft.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 Oledbdataadatpe r
acceptchangesdu ringfill = 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(anot herdatatable)
sqlcommandbuild er(anotherdatat able)

And than inserting those
sqldataadapter( thedatatabletof ill)

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] m> 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.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;"
& _
"Data Source=e:\My Documents\db1.m db")

AccessConn.Open ()

Dim AccessCommand As New System.Data.Ole Db.OleDbCommand ("INSERT INTO Orders SELECT * FROM
[Orders] IN '' [ODBC;Driver={SQ L
Server};Server= (local);Databas e=Northwind;Tru sted_Connection =yes];", AccessConn)

AccessCommand.E xecuteNonQuery( )
AccessConn.Clos e()
Paul
~~~~
Microsoft MVP (Visual Basic)