OleDbDataAdapter - merging two tables

[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

[Rob Blackmore]

You can construct a system.drawing.point from the x and y co-ordinates so
you could save them as either a single string or as two seperate fields. I
would save each as a seperate field as it would be easier to report upon.

However, if you want to store them as two strings (or one string) the code
below shows how to reconstruct the system.drawing.point from a comma
seperated string.

Kind regards,

Rob
Code follows:

Dim sLocation As String = ""

sLocation = Me.Location.X.ToString + "," + Me.Location.Y.ToString

Me.Location = New System.Drawing.Point(0, 0)
MsgBox("moved")

Me.Location = New System.Drawing.Point(Split(sLocation, ",")(0),
Split(sLocation, ",")(1))
MsgBox("moved back")

"baldrick" <ph**********@hotmail.comwrote in message
news:54**********************************@m44g2000 hsc.googlegroups.com...

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

[Rich P]

Greetings,

I have observed that you can only use a DataAdapter to read data from an
Access table, but you can't Insert data to an Access table (or Update)
using an OleDataAdapter. Some reading I have done on this suggests that
this is intentional. So, the workaround that I have come up with is to
loop through your rows and insert each row individually using a straight
forward SelectCommand

For Each dr As DataRow In yourDataTable
da.SelectCommand.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToString & "','" & dr("fld2").ToString & "','" &
dr("fld3").ToString & "'," & ...
da.SelectCommand.ExecuteNonQuery
Next

You will have to delimit as required (single quotes for Text fields, and
probably for Date fields).

This may seem like a bit of a pain, but I feel that Microsoft is trying
to steer .Net away from Access -- keeping Access as a File-based com
system. I may be wrong, and if I am, hopefully someone else knows how
to use an OleDataAdapter Insert (and Update) command with Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

One Correction:

yourDataTable.Rows '--I forgot the .Rows part

For Each dr As DataRow In yourDataTable.Rows
da.SelectCommand.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToString & "','" &
dr("fld2").ToString & "','" & dr("fld3").ToString & "',"
& ...
da.SelectCommand.ExecuteNonQuery
Next


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[baldrick]

On 27 May, 22:14, Rich P <[e-mail]:

Greetings,

I have observed that you can only use a DataAdapter to read data from an
Access table, but you can't Insert data to an Access table (or Update)
using an OleDataAdapter. *Some reading I have done on this suggests that
this is intentional. *So, the workaround that I have come up with is to
loop through your rows and insert each row individually using a straight
forward SelectCommand

For Each dr As DataRow In yourDataTable
* da.SelectCommand.CommandText = "Insert Into AccessTbl Select '" &
dr("fld1").ToString & "','" & dr("fld2").ToString & "','" &
dr("fld3").ToString & "'," & ...
* da.SelectCommand.ExecuteNonQuery
Next

You will have to delimit as required (single quotes for Text fields, and
probably for Date fields).

This may seem like a bit of a pain, but I feel that Microsoft is trying
to steer .Net away from Access -- keeping Access as a File-based com
system. *I may be wrong, and if I am, hopefully someone else knows how
to use an OleDataAdapter Insert (and Update) command with Access.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Is it just Access? Are you saying that if the destination table was
SQL Server that it would work?

The code I'm going off is...

http://www.knowdotnet.com/articles/datasetmerge.html

[Rich P]

I mean just Access. Last year I wrote DLL (VB2005 and C#)which I
explosed to the Com environment which used OLE and Sql DataAdapters for
manipulating data. I made references to this DLL from Access 2003 and
Excel 2003. The DLL worked fine with Excel but would error out when
used with Access. So I modified the DLL to read from a text file (I
would export data to a text file from Access) - then the DLL worked OK
from Access. So I deduced that it was the DataAdapter which did not
work with Access. I posted some questions on this, and someone (a tech
author) got back to me and suggested that this was intentional.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Cor Ligthert[MVP]]

Baldrick,

Please don't multipost, it is not my purpose to post multiple answers in
every newsgroup.

Use crossposting next time, which is sending the same message to all
newsgroups, that can be about your question.

We can then see if your question is already answered.

Thanks in advance.

Cor

"baldrick" <ph**********@hotmail.comschreef in bericht
news:54**********************************@m44g2000 hsc.googlegroups.com...

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