ImportRow doesn't saves changes to database table

[mikus]

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.

[pclement]

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.

[mikus]

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?

[pclement]

See if the following works for you:

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()

[mikus]

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

[pclement]

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.