472,808 Members | 2,005 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,808 software developers and data experts.

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
Nov 21 '05 #1
4 8539
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Alex Vorobiev | last post by:
hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
6
by: Roy Gourgi | last post by:
Hi, I am trying to add a row to my table but I get the error message "invalid column name SOBN and BN1" on this statement. Basically, I am trying to add the row into the same table that I am...
11
by: Sezai YILMAZ | last post by:
Hello I need high throughput while inserting into PostgreSQL. Because of that I did some PostgreSQL insert performance tests. ------------------------------------------------------------ --...
8
by: Josu Maldonado | last post by:
Hello List, I'm importing some data from Foxpro to Postgres, there is atable wich contains aprox 4.8 million rows and it size about 830MB. I uploaded it to Postgres using dbf2pg and worked fine,...
2
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request :...
5
by: robecflo | last post by:
Hi Forum, i have a problem, hope somebody can give me ideas. I'm developing with windows forms and vb.net, and oracle as a database. At this moment i have a table called amortizaciones, this table...
6
by: lenygold via DBMonster.com | last post by:
Hi everybody: What is the best way to I have 10 tables with similar INSERT requiremnts. INSERT INTO ACSB.VAATAFAE WITH AA(AA_TIN, AA_FILE_SOURCE_CD, .AA_TIN_TYP) AS ( SELECT AA_TIN,...
1
by: EJO | last post by:
with sql 2000 enterprise Trying to build a stored procedure that will take the rows of a parent table, insert them into another table as well as the rows from a child table to insert into...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.