Access VBA First Record in Table

One thing that may confuse a lot of programmers, is that the first row of an Access table is NOT necessarily the first record.

For example consider the empty access table below:
Empty Table
Lets say we copy the some data into the table:
Data in Table
In the figure above the data being displayed in the first row of the table is the first record. The data being displayed in the second row is the second record, and so on ….

Lets say we change the order the data is being displayed in the table. Instead of showing data in ascending order, we change it to descending:
Descending

Right now the data in the first row of the table is associated with the last record.
Result

How does this affect programmers?
If in your program you assume the first row in the table is the first record, your program will run into logical errors.

For example in the figure above the value in the 3rd field of the first row is “Data 11”. The code below displays the value in the 3rd field of the first record:

Sub main()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1")
objRecordset.MoveFirst
MsgBox (objRecordset.Fields(2))
End Sub

Result:
Result 2

As you can see the value in the 3rd field of the first record (“Data 1”) is not the same as the value in the 3rd field of the first row of the table (“Data 11”).

For more information about reading data from access tables using VBA please see the link below:

You can download the file and code related to this article from the link below:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *

Search