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

1 Apr 2005 1:51 AM

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

1 Apr 2005 2:10 AM

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

Show quoteHide quote

"JPO" <justin.parry-oke***@sungard.com> wrote in message
news:a3311a11.0503311751.118ce2bd@posting.google.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.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

1 Apr 2005 8:03 AM

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclassacceptchangesduringfilltopic.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

1 Apr 2005 9:39 AM

Mark

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

1 Apr 2005 8:36 PM

Paul Clement

On 31 Mar 2005 17:51:09 -0800, justin.parry-oke***@sungard.com (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)