Refreshing Data from Microsoft Acess in ADO.Net

It looks to me like all you really need is the autonumber value. I got code for this from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

It seems rather convoluted for such an essential function, but here's how I made it work for my needs (VB 2003, FWIW):

    Friend WithEvents odbconnData As System.Data.OleDb.OleDbConnection
    Friend WithEvents odbdaHeader As System.Data.OleDb.OleDbDataAdapter
    ' Define the Connection & DataAdapter....
    AddHandler odbdaHeader.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf HeaderUpdated)

   Public idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", odbconnData)
   Public gintLastID as Integer

    Private Sub HeaderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
        ' Include a variable and a command to retrieve the identity value from the Access database.

        If args.StatementType = StatementType.Insert Then
            ' Retrieve the identity value and pass it back
            gintLastID = CInt(idCMD.ExecuteScalar())
        End If
    End Sub

And the actual code doing the update...

                Dim rowCurState As DataRowState = DataRowState.Unchanged
                If dsCurrent.Header.Count <> 0 Then
                    rowCurState = dsCurrent.Header(0).RowState
                    result = DA.odbdaHeader.Update(dsCurrent)
                    If rowCurState = DataRowState.Added Then
                        Dim drHeader As dsData.HeaderRow = dsDataA.Header.FindByID(dsCurrent.Header(0).ID)
                        ' Set the parent right, and the children follow
                        dsCurrent.Header(0).ID = gintLastID
                    End If

Ive tried using
myUsersRecSet.Fill(myUsersDataTable)
to repopulate the datatable with the updated data in Access including the newly created auto number but it isnt working. Any idea why?

"Isn't working" is a bit vague. It's easier to provide help when you provide details.

Have you tried setting breakpoints in your code in both the RowUpdated event handler and your update code to see if the Scalar command to get the value is working? What's in the autonumber field, both in your program (before and after you update the database) and in the database itself? Is the record in the Access database at all? It shouldn't be necessary to do a .Fill with this code in place.

I should mention that I'm using this code to get an autonumber value which is the primary key for the row. If your autonumber is not the primary key, I don't know if the code will work correctly.

If these ideas don't give you enough to work out what's going on, it would probably be good for you to post your code (both the RowUpdated event handler and your database updating code), so we can make sure my sample was grafted in properly, with appropriate variable references and the like.

I should have included at the beginning of my "actual code doing the update" the following lines, just to make sure you're getting the right information to put back to the database:

        Dim dsCurrent As dsData
        dsCurrent = CType(dsDataA.GetChanges, dsData)

and at the end of the update process:

         dsDataA.AcceptChanges()

The code I am using to connect to my Access database is shown in my original question. Below is the code I am using to create a new record in Access then try to find the new blank record and retrieve the Access generated Autonumber(which is the table's primary key)

 Dim myNewBlankRow As Data.DataRow = myUsersDataTable.NewRow
            myNewBlankRow("InvoiceTo") = ""
            myUsersDataTable.Rows.Add(myNewBlankRow)
            myUsersRecSet.Update(myUsersDataTable)
            For i As Integer = 0 To myUsersDataTable.Rows.Count - 1
                If myUsersDataTable.Rows(i)("InvoiceTo") = "" Then
                    myOrderNo = myUsersDataTable.Rows(i)("OrderNumber")
                    Exit For
                End If
            Next

It is finding the new record I have created but when I try and pull out the OrderNumber (which is the auto-number) is gives me an error saying it is DBNull. Even though it is not null in Access.

I hope this helps explain the problem I am having a little clearer

This code doesn't show any use of the RowUpdated event handler that I described in my first message. Did you try to merge the code I showed into your code?

In addition, if you're getting a DBNull error, it suggests that the definition of your internal DataTable doesn't include an autonumber reference. Therefore, you'll need to put a temporary value into the OrderNumber field when you create it, so that you can find the row to go back and put the Access-generated value in, unless there's another unique key that you can use to search on. In situations where I have to have a temporary key value, I keep a separate counter, initialized at a very high value that the autonumber generator will never reach (100,000,000 or something like that). So, for instance:

            Dim myNewBlankRow As Data.DataRow = myUsersDataTable.NewRow
            myNewBlankRow("OrderNumber") = intCounter
            intCounter += 1
            myNewBlankRow("InvoiceTo") = ""
            myUsersDataTable.Rows.Add(myNewBlankRow)
            myUsersRecSet.Update(myUsersDataTable)
            ' Following line calls on value generated by RowUpdated event
            myNewBlankRow("OrderNumber") = gintLastID

Then you need to defined the RowUpdated event handler:

   ' This goes at the beginning of your form - either in the New or the Form_Load
   AddHandler myUsersRecSet.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OrderUpdated)

   ' At the beginning of your form, or wherever you define your data connection.
   ' Replace "odbconnData" in next line with the name of your OleDBDataConnection.
   Public idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", odbconnData)
   Public gintLastID as Integer

    Private Sub OrderUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
        ' Include a variable and a command to retrieve the identity value from the Access database.

        If args.StatementType = StatementType.Insert Then
            ' Retrieve the identity value and pass it back
            gintLastID = CInt(idCMD.ExecuteScalar())
        End If
    End Sub

Does this make more sense?

sorry but that is way over my head. ive just closed the connection and reopened it to force a refresh of the data. ill accept your solution anyway because im sure your way would have worked if i had the experience to implement it

Well, yes, that will work, but means a lot of extra data accessing. If your database is small and you're doing only a limited amount of adding, it may not be a big deal.

As someone who is still very much a learner in VB, I can tell you that one of the ways I've been learning has been to dissect the answers I get here (I've asked as well as answered a lot of questions over the last year). To be sure, sometime's it's not worth the effort, and finding a way that works, even if it's not the most elegant, is good enough to keep you moving. But sometimes it's worth taking a little more time to learn more about how the language works to assist with both current and future needs. Best wishes in figuring out when each approach is best for you.