By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,325 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,325 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
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" <rg@medworks.com> 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

Nov 21 '05 #2

P: n/a
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" <rg@medworks.com> 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


Nov 21 '05 #3

P: n/a
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
Nov 21 '05 #4

P: n/a
On Fri, 16 Sep 2005 16:26:25 -0700, "RG" <rg@medworks.com> 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)?

Im new to this, so heres what Ive 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. Ive 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)
Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.