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

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" <justin.parry-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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/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.nonquery

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

I hope this helps,

Cor

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

On 31 Mar 2005 17:51:09 -0800, justin.parry-(E-Mail 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)