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.OLE db.OleDbConnect ion = New
System.Data.OLE db.OleDbConnect ion(QuantumConn ection)
Dim cnnAccessDB As System.Data.OLE db.OleDbConnect ion = New
System.Data.OLE db.OleDbConnect ion(AccessDB)

cnnA.Open()
cnnAccessDB.Ope n()

Dim daDataAdapterA As New
System.Data.Ole Db.OleDbDataAda pter("SELECT * FROM Advmast", cnnA),
dtDataTableA As New DataTable
Dim daAccessDB As New
System.Data.Ole Db.OleDbDataAda pter("SELECT * FROM Advmast",
cnnAccessDB), dtAccessDB As New DataTable

Dim myRow As DataRow
Dim i As Int16

daDataAdapterA. Fill(dtDataTabl eA)

dtAccessDB = dtDataTableA.Cl one
dtAccessDB.Begi nLoadData()
For Each myRow In dtDataTableA.Ro ws
'myRow("name") = "hi there"
dtAccessDB.Impo rtRow(myRow)
daAccessDB.Upda te(dtAccessDB)
Next myRow
dtAccessDB.EndL oadData()

daAccessDB.Upda te(dtAccessDB)
dtAccessDB.Acce ptChanges()

cnnAccessDB.Clo se()
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

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx

"JPO" <ju************ *****@sungard.c om> wrote in message
news:a3******** *************** ***@posting.goo gle.com...

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.OLE db.OleDbConnect ion = New
System.Data.OLE db.OleDbConnect ion(QuantumConn ection)
Dim cnnAccessDB As System.Data.OLE db.OleDbConnect ion = New
System.Data.OLE db.OleDbConnect ion(AccessDB)

cnnA.Open()
cnnAccessDB.Ope n()

Dim daDataAdapterA As New
System.Data.Ole Db.OleDbDataAda pter("SELECT * FROM Advmast", cnnA),
dtDataTableA As New DataTable
Dim daAccessDB As New
System.Data.Ole Db.OleDbDataAda pter("SELECT * FROM Advmast",
cnnAccessDB), dtAccessDB As New DataTable

Dim myRow As DataRow
Dim i As Int16

daDataAdapterA. Fill(dtDataTabl eA)

dtAccessDB = dtDataTableA.Cl one
dtAccessDB.Begi nLoadData()
For Each myRow In dtDataTableA.Ro ws
'myRow("name") = "hi there"
dtAccessDB.Impo rtRow(myRow)
daAccessDB.Upda te(dtAccessDB)
Next myRow
dtAccessDB.EndL oadData()

daAccessDB.Upda te(dtAccessDB)
dtAccessDB.Acce ptChanges()

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

[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 acceptchangesdu ringfill = false
http://msdn.microsoft.com/library/de...gfilltopic.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.nonquer y

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, ju************* ****@sungard.co m (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 ImportSQLServer ToAccess() As Boolean

Dim AccessConn As New System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;"
& _
"Data Source=e:\My Documents\db1.m db")

AccessConn.Open ()

Dim AccessCommand As New System.Data.Ole Db.OleDbCommand ("SELECT * INTO Orders FROM [Orders]
IN '' [ODBC;Driver={SQ L Server};Server= (local);Databas e=Northwind;Tru sted_Connection =yes];",
AccessConn)

AccessCommand.E xecuteNonQuery( )
AccessConn.Clos e()

End Function
Paul
~~~~
Microsoft MVP (Visual Basic)