I cannot get the last record in an Access table, only the 2nd to last record.

[ddutke]

Wrong forum, coffeeless, sorry.

I'm new here and didn't tag my code and was scolded and informed I wouldn't get the answer I was looking for until my code was properly tagged. I'm posting again with tags. Apologize for posting the same issue twice. Here goes...

I cannot get the last record in an Access table, only the 2nd to last record. My Access 2010 tables are solid, containing a last record and a blank row underneath for the next record to be inserted. I've included my code below showing what I've tried in order to get the last record in my tables. It should be self explanatory. Thanks in advance for your help! Been beating my head against the wall all day on this! I'm new here; is there a way to post my code formatted? Thanks!

Private Sub btnLastRow_MouseUp(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles btnLastRow.MouseUp    
Dim strFieldName As String    
Dim objCn As New OleDb.OleDbConnection(strCn)    
Dim objDA As New OleDb.OleDbDataAdapter("Select * from tblExam", strCn)    
Dim objCB As New OleDb.OleDbCommandBuilder(objDA)    
Dim objDS As New DataSet    
Dim Sql As String    
Sql = "select * from tblExam"    
objDA = New OleDb.OleDbDataAdapter(Sql, objCn) 'Create dataset    
objDA.SelectCommand.CommandText = ("Select * from tblExam")    
'Create Schema    
objDA.FillSchema(objDS, SchemaType.Source, "DStblExam")    
'Create DataSet    
objDA.Fill(objDS, "DStblExam") 'DStblExam represents the name of the DataSet    
'=================================================  ==================================================  ===========    
'The following line only shows me the 2nd to last record in my database, but I need the last record. If I take    
' away the "- 1" after "Rows.Count" I get an error; There is no row at position 86. I can also use the actual    
'row numbers with the same result as I'm showing below.    
'=================================================  ==================================================  ===========    
MsgBox(objDS.Tables("DStblExam").Rows((objDS.Table  s("DStblExam").Rows.Count - 1)).Item(2)) 'Will show 2nd to    
'last record. I need last record.    
MsgBox(objDS.Tables("DStblExam").Rows((objDS.Table  s("DStblExam").Rows.Count)).Item(2)) 'I get an error; There is    
'no row at position 86 even though there is.    
MsgBox(objDS.Tables("DStblExam").Rows(85).Item(2)) 'Will show 2nd to last record. I need last record.    
MsgBox(objDS.Tables("DStblExam").Rows(86).Item(2)) 'I get an error; There is no row at position 86 even though    
'there is.    
'=================================================  ==================================================  ===========    
'The following code only shows me the 2nd to last record in my database. I need last record.    
'=================================================  ==================================================  ===========    
Dim row As DataRow    
For Each row In objDS.Tables(0).Rows    
strFieldName = row("Exam_Date")    
Next    
MsgBox(strFieldName) 'Will show 2nd to last record. I need last record.    
End Sub                   

 

[IanRyder]

Hi,

I was about to post this earlier but I thought someone else had seen something I had missed? Anyway here are my comments:-

The actual select query looks fine so do a quick test. In Access, have a look at the table and find out how many records are in the table. Is it 85 or 86. Once done check the value of objDS.Tables(0).Rows.Count. These two numbers should be exactly the same being either 85 or 86.

If these two numbers are the same then you know that ALL the records in the Access table are being returned correctly to the DataSet. I expect this to be the case but please confirm if this is not the case.

If these two numbers are the same then it sounds like the data you are looking to compare against in your access table is being returned in an unexpected order. Add an Order By Clause to your query and then check the results again.

Hope that helps.

Cheers,

Ian

 

[ddutke]

Thanks Ian, that makes sense. I'll give it a try!