Update Fails (Access db)

[vbchJack]

This is making me nuts!

Sub AddElement(ByVal newItem As String)

Dim cb As New OleDb.OleDbCommandBuilder(daFinishes)
Dim dsNewRow As DataRow

dsNewRow = dsFinishes.Tables("tblFinishes").NewRow()
dsNewRow.Item("Finish") = newItem
dsFinishes.Tables("tblFinishes").Rows.Add(dsNewRow)

Try
daFinishes.Update(dsFinishes, "tblFinishes")
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

The dataset is OK and is available in the app. When a new item is added,
it too is OK and appears in the dataset. But, the Access 2010 db will not
update. The db table has only 2 columns: an 'ID' (which is primary key)
and a text column named 'Finish.' The table "tblFinishes" is one of 8
tables in the db but is the only table in the dataset. (SELECT * from
tblFinishes)

The error from the 'Try ... catch" is: "Dynamic SQL generation is not
supported against a Select command that does not return any base table
info"

To add to the frustration, as a test I removed the routine, placed it in a stand alone app and it worked as it should.
There are a lot of posts about this but mostly they talk about absence of a primary key which is not the problem here.
Is the needed PK info somehow 'lost' after the sql select? Is this salvageable? Is there another way ... maybe a better way? I am very new to this having just bubbled up from vb6.

[dunfiddlin]

So, what is the Select command and, more top the point, where is it? There are a lot of items here where you seem to take it on trust that that values will carry over from other subs. I'd be very surprised if you could get away with not creating a new Adapter at the very least.

[vbchJack]

Thanks for the quick reply.
The select statement is:
strSelect = "SELECT * FROM tblFinishes"
daFinishes = New OleDb.OleDbDataAdapter(strSelect, con)
daFinishes.Fill(dsFinishes, "tblFinishes")
MaxRowsFinishes = dsFinishes.Tables("tblFinishes").Rows.Count

Originally it was in a module but I moved it to the FormLoad event. This app currently has only one form.
I have also used: "SELECT id, finish FROM tblFinishes" with the same error resulting. 'ID' is the primary key in the Access db. MaxRowsFinishes is used in the app. It always has the correct number of rows.

[dunfiddlin]

Well there you go then. You can't 'carry over' objects initialised in Form_Load to another sub. You need to initialise an adapter within the AddElement sub.

[jmcilhinney]

You might like to follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. It provides code examples that, amongst other things, demonstrate how to manage data access objects that you only need to use in one place and those that you need to use in multiple places. Like any objects, if you need to use them in more than one method then they must be assigned to a variable declared outside all those methods. Exactly where that is depends on the situation but, in your case, it sounds like a simple field, i.e. member variable, in the form is appropriate.