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

Copying from MSSQL -> Access through datasets

P: n/a
Hi all,

We have 2 databases that are not connected to each other directly.
Changes to either databases can only be done on one side.
Those 2 databases are of the type MSAccess or MSSQL but with the same
structure.

So if something changes ( added,updated,deleted ) in Database 1 this
should be replicated through a webservice to Database 2.
I was thinking to make a kind of sourcesafe where you can check-in &
check-out the database ( state will be held on webservice-server ).
And with every check-in, a 'changeset' would be send to the webservice-
server so that the other side can download it.
Those 'changesets' would consist of 3 type of data per table :
- Deleted rows ( only hold the id's in this list )
- Inserted rows ( contains full dataset for new records )
- Updated rows ( contains full dataset for updated records )

For the first two I already found out how I should do this but the
updated rows are a problem.
The updated rows are always added as new rows instead of updated rows.

The code follows here :

Dim myConnectionSQL As New OleDbConnection
Dim myDataAdapterSQL As OleDbDataAdapter = New
OleDbDataAdapter("Select * From TEST..Export", myConnectionSQL)

Dim myConnectionAccess As New OleDbConnection
Dim myDataAdapterAccess As OleDbDataAdapter = New
OleDbDataAdapter("Select * From Export", myConnectionAccess)
Dim builder As OleDbCommandBuilder = New
OleDbCommandBuilder(myDataAdapterAccess)
Dim myDataSet As New DataSet
Dim myDataSet2 As New DataSet

myConnectionSQL.ConnectionString = "Provider=SQLOLEDB;Data
Source=(local);Integrated Security=SSPI"
myConnectionSQL.Open()
myDataAdapterSQL.AcceptChangesDuringFill = False
myDataAdapterSQL.Fill(myDataSet, "Export")
MsgBox("Source table " &
myDataSet.Tables(0).TableName.ToString & " has " &
myDataSet.Tables(0).Rows.Count.ToString & " rows")

myConnectionAccess.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb"
myConnectionAccess.Open()

builder.GetUpdateCommand()

myDataAdapterAccess.Update(myDataSet, "Export")

What am I doing wrong here ?
Do you maybe have a better solution to synchronise 2 databases with
each other through a webservice ?

Thnx in advance ...
Sven Peeters
BELGIUM

Sep 10 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.