By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,571 Members | 1,628 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,571 IT Pros & Developers. It's quick & easy.

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

P: n/a
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(QuantumConnectio n)
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
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.com> wrote in message
news:a3**************************@posting.google.c om...
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(QuantumConnectio n)
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

Nov 21 '05 #2

P: n/a
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
Nov 21 '05 #3

P: n/a
Why dont you just use DTS and go straight from one MSSQL server to another ?
Nov 21 '05 #4

P: n/a
On 31 Mar 2005 17:51:09 -0800, ju*****************@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=Micros oft.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)
Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.