Last post Dec 13, 2009 02:28 PM by clio
Dec 12, 2009 08:13 PM|clio|LINK
I am so afraid that I'll win the prize for stupid question of the day. I have honestly searched and googled and I feel like I'm 99% of the way there and I hope someone out there will take pity on me.
My goal: move data from a table in one Access database into a twin table in another Access database (both tables have the same structure). At the moment my app can see both databases but later on it will receive the dataset from the ether and need to append
the records to its local database.
My approach: Fill a DataSet from the source database (turning off AcceptChanges). Create a DataAdapter for the dest database. Use CommandBuilder to create the insert string. Update with the DataAdapter to send info from the DataSet into the dest database.
If this is monumentally stupid please let me know.
After finding a few problems along the way I got the code to run and the Update method returns a value indicating that all the records have been affected. But when I open the database there aren't any records in the dest table.
I'm assuming it's something that I'm doing wrong with the Adapter or Builder. The InsertCommand text looks alright - I never explicitly deal with the parameters but my understanding was that this is part of what the builder/adapter do...but somewhere I
remember reading about Access being pickier about parameters.
I've never used the CommandBuilder before but in this instance the # of fields is quite large and I'd like to avoid debugging/maintaining my own homegrown command code if possible.
Any assistance is appreciated. Thanks!
Dim ds As New DataSet
ds = GetDS()
'at this point ds.haschanges is true and the rowstate for each row is Added
Dim newconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\App_Data\Newdata.mdb")
Dim sql As String = "SELECT * from MyTable"
Dim da As New OleDbDataAdapter(sql, newconn)
Dim builder As New System.Data.OleDb.OleDbCommandBuilder(da)
Dim i As Integer
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"
da.InsertCommand = builder.GetInsertCommand
da.UpdateCommand = builder.GetUpdateCommand
i = da.Update(ds, "MyTable")
'i indicates that all rows in ds have been updated but when I open Newdata.mdb there are no records in MyTable
Protected Function GetDS() As DataSet
Dim myconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\App_Data\Olddata.mdb")
Dim sqlsource As String = "SELECT * from MyTable"
Dim dasource As New OleDbDataAdapter(sqlsource, myconn)
dasource.AcceptChangesDuringFill = False
Dec 13, 2009 02:28 PM|clio|LINK
oh good grief - I hope no one spent time on this - after some super simple, frustrating tests I decided that there must a database filling up somewhere - I just couldn't see it - and indeed I had messed up the relative path filename. So sorry to have wasted