OleDbDataAdapter - merging 2 tables

  • Thread starter Thread starter baldrick
  • Start date Start date
B

baldrick

Hello,

I am trying to plonk the entire contents of a dBase file into an
Access table.


I have scanned the dBase fields and created an empty Access table
with
the same field formats.
I have also created oledb connections to both data sources.


Below is my attempt at achieving this, which doesn't work. I've
googled all day and had lots of clues but not found any code that
works for me. I think I'm close but obviously missing something quite
important.


It would be much appreciated if anyone could tell me what it is?


Private Sub test()


Dim strquerySource As String = "Select * from dBaseFile"
Dim strqueryDestination As String = "Select * from
accessTable"


Dim daSource As OleDbDataAdapter
Dim daDestination As OleDbDataAdapter


Dim tableSource As New DataTable
Dim tableDestination As New DataTable


daSource = New OleDbDataAdapter(strquerySource, cnDBASE)


'if line below is NOT used then no error but no update.
'if it IS used then get error as commented later.
daSource.AcceptChangesDuringFill = False


daSource.Fill(tableSource)


'to visually check data - ITS OK, displays all data
Me.DataGridViewDBASE.DataSource = tableSource
Me.DataGridViewDBASE.Refresh()


'destination
daDestination = New OleDbDataAdapter(strqueryDestination,
cnAccess)
daDestination.Fill(tableDestination)


'to visually check data - ITS OK, just shows headers
Me.DataGridViewAccess.DataSource = tableDestination
Me.DataGridViewAccess.Refresh()


'ERROR - IF daSource.AcceptChangesDuringFill = False
' "Update requires a valid InsertCommand when passed DataRow
collection with new rows."
' otherwisae no error but the Access table doesn't get
updated.


daDestination.Update(tableSource)


End Sub
 
Baldrick,

As written are you missing the insert (and update etc command).

There is a commandbuilder, simple before the update

dim cmb as new OleDBCommandbuilder(daSource)

Cor
 
Baldrick,

As written are you missing the insert (and update etc command).

There is a commandbuilder, simple before the update

dim cmb as new OleDBCommandbuilder(daSource)

Cor

"baldrick" <[email protected]> schreef in bericht




















- Show quoted text -
Click to expand...

Thanks - but being a novice I'm not quite sure what the actual
commands should be. Any pointers to similar code?

Phil
 
There is nothing more needed then I wrote, the commandbuilder builds at
runtime the commands from the SelectString from the dataadapter, which was
in my idea daSource

Cor

"baldrick" <[email protected]> schreef in bericht
Baldrick,

As written are you missing the insert (and update etc command).

There is a commandbuilder, simple before the update

dim cmb as new OleDBCommandbuilder(daSource)

Cor

"baldrick" <[email protected]> schreef in
bericht
















- Show quoted text -
Click to expand...

Thanks - but being a novice I'm not quite sure what the actual
commands should be. Any pointers to similar code?

Phil
 
Back
Top