-
ImportRow doesn't saves changes to database table
Can anybody help me please.
I'm new to VB.NET and I'm trying to import some data from SQL Server to MS
Access but cannot make it to work (note: I don't want to use DTS)
....goes like this
Dim daSql As New SqlDataAdapter("select * from table1", SqlConnection)
Dim dsSql As New DataSet() ' to hold info from SQL Server
daSql.Fill(dsSql, "table1")
Dim daAcc As New OleDb.OleDbDataAdapter("select * from table2",
AccessConnection)
' table2 is an empty table with the same schema as table1
Dim dsAcc As New DataSet() 'to hold info for Access
daAcc.Fill(dsAcc, "table2")
For i as Integer = 0 To dsSql.Tables(0).Rows.Count - 1
dsAcc.Tables(0).NewRow()
dsAcc.Tables(0).ImportRow(dsSql.Tables(0).Rows(i))
Next i
dsAcc.Tables(0).AcceptChanges()
daAcc.AcceptChangesDuringUpdate = True
daAcc.Update(dsAcc, "table2")
'...end
what am I doing wrong?
messagebox inserted anywhere after For..Next loop shows desired number of
rows in Access dataSet but the are no changes made to the actual database.
-
Are the tables from each database identical in structure? I would think the import would be easier to accomplish (and much quicker) using a SQL statement.
Paul
~~~~
Microsoft MVP (Visual Basic)
-
Thank you for such a prompt response.
Yes they are identical.
How would you approach this through SQL statement if one table is in SQL Server and another in MS Access? can you do this way?
-
See if the following works for you:
Code:
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\db1.mdb")
AccessConn.Open()
'New table
'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)
'Existing table
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)
-
Paul,
Thank you!!!
It worked just great.
Is there any place where I can rate this reply to my question?
Just for my curiosity would you happen to know why my ImportRow() wasn't saving data to the table?
Thanks
-
I don't know why the ImportRow method didn't work. I'll see if I can take a closer look and get you an answer.
Paul
~~~~
Microsoft MVP (Visual Basic)
Similar Threads
-
By mycwcgr in forum ASP.NET
Replies: 0
Last Post: 08-29-2005, 10:25 PM
-
By Robert in forum Database
Replies: 0
Last Post: 08-15-2002, 09:57 AM
-
Replies: 1
Last Post: 03-28-2002, 04:38 PM
-
By Jeremiah in forum VB Classic
Replies: 3
Last Post: 03-05-2001, 06:54 PM
-
By Justin in forum ASP.NET
Replies: 1
Last Post: 01-24-2001, 07:59 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|