473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 8584
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)?
¤
¤ 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)
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
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.