copying data from MS-SQL to MS-Access using VB.Net

[JPO]

Hi there,

I'm trying to use MSAccess as a "container" to move data around from
one MS-SQL server DB to another. This is basically already a design
decision that has been made for a lot of reasons and can't be changed
by me.

What I was wondering is what is the bet way to populate the MS-Access
DB. The DB will only contain 6 tables which will have a very small
amount of data in them. I have tried to use ADO.Net to read the data
from MS-SQL into a DataTable and then save it into the MS-Access DB
using again using a DataTable populated from the first, but I can't
seem to get the syntax right.

Even after I execute the Update method nothing happens to the Access
DB. So I modified one of the values in the data rows ('myRow("name") =
"hi there") and this seemed to force an update, but then threw a
concurrency error.

Attached below is the code I was using, can anyone help?

JPO




Dim cnnA As System.Data.OLEdb.OleDbConnection = New
System.Data.OLEdb.OleDbConnection(QuantumConnection)
Dim cnnAccessDB As System.Data.OLEdb.OleDbConnection = New
System.Data.OLEdb.OleDbConnection(AccessDB)

cnnA.Open()
cnnAccessDB.Open()

Dim daDataAdapterA As New
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast", cnnA),
dtDataTableA As New DataTable
Dim daAccessDB As New
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast",
cnnAccessDB), dtAccessDB As New DataTable

Dim myRow As DataRow
Dim i As Int16

daDataAdapterA.Fill(dtDataTableA)

dtAccessDB = dtDataTableA.Clone
dtAccessDB.BeginLoadData()
For Each myRow In dtDataTableA.Rows
'myRow("name") = "hi there"
dtAccessDB.ImportRow(myRow)
daAccessDB.Update(dtAccessDB)
Next myRow
dtAccessDB.EndLoadData()

daAccessDB.Update(dtAccessDB)
dtAccessDB.AcceptChanges()

cnnAccessDB.Close()
cnnA.Close()
cnnAccessDB = Nothing
cnnA = Nothing

 

[Robbe Morris [C# MVP]]

Is there a reason you don't just create a DTS package and point it to
an empty .mdb database file? Then, run the package from your code?

Here is a VBScript that does this sort of thing. You could use COM
interop to utilize much of it in .NET.

http://www.eggheadcafe.com/articles/20030923.asp

 

[Cor Ligthert]

JPO,

Beside the question from Robbie.

The problem with this program is probably that during the fill the
acceptchanges is done and there will be no update.

To prevent that you can use the acceptchangesduringfill = false
http://msdn.microsoft.com/library/d...aadapterclassacceptchangesduringfilltopic.asp

When you cannot do as Robbie wrote than the next good step can be doing it
streaming by reading using the datareader and inserting using the
execute.nonquery

There is in my opinion no need to use two datatables.

I hope this helps,

Cor

 

[Mark]

Why dont you just use DTS and go straight from one MSSQL server to another ?

 

[Paul Clement]

On 31 Mar 2005 17:51:09 -0800, (e-mail address removed) (JPO) wrote:

¤ Hi there,
¤
¤ I'm trying to use MSAccess as a "container" to move data around from
¤ one MS-SQL server DB to another. This is basically already a design
¤ decision that has been made for a lot of reasons and can't be changed
¤ by me.
¤

You can probably do this directly without using a DataSet:

Function ImportSQLServerToAccess() As Boolean

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

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)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function


Paul
~~~~
Microsoft MVP (Visual Basic)