Read Data from Table, Access VBA

In this article I will explain how you can read data from an Access table using VBA.

This article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:


How To?

Assume we have a table with the name “MyTable1” with the following fields:
Table

As you can see the table has 4 fields:

  • MyField1
  • MyField2
  • MyField3
  • MyField4

The following code will initiate an ADODB.Recordset object for the table:

Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")

Note: The code above will initiate a recordset object that will pointing at the first record of the access table.

Note: Rather than opening the entire table, we could have used a query to filter the data and return only the values we need. For example we could have replace the last line of the code above with the code below to only return values from the table whose first field has a value greater than 15:

objRecordset.Open ("Select MyField1, MyField2, " & _
"MyField3, MyField4 " & _
"From MyTable1 " & _
"Where MyField1 > 15 ")

For more information about this topic please see the link below:

Note: The first record of the table is not necessarily the first row of the table. The figure below is displaying the first row of the table:
Recordset location

By using the code below we can move the recordset pointer to the next record:

objRecordset.MoveNext

Example: The code below will loop through all the records in the recrodset:

While objRecordset.EOF = False
objRecordset.MoveNext
Wend

The value of the current record the recordset object is pointing at can be read through the Fields collection. The code below will return the value in the first field of the current record the Recordset object is pointing at:

objRecordset.Fields.Item(0).Value

Note: The Fields collection of the Recordset object is a zero based collection. Therefore the first field is reference by the index “0” and the 4th field is referenced by the index “3”.

Note: The Recordset object can be moved to the previous record using the code below:

objRecordset.MovePrevious

Note: The Recordset object can be moved to the last record using the code below:

objRecordset.MoveLast

Note: The Recordset object can be moved to the first record using the code below:

objRecordset.MoveFirst


Example 1:

In this example we are going to the:

  • Step 1: Search the table for the record whose first field has the value “14”.
  • Step 2: Display the value in the 3rd field (“MyField3”) of that record in a message box.

Note: “MyField3” is the 3rd field therefore is reference through the index “2”:

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
'find the target record
While objRecordset.EOF = False
'check for match
If objRecordset.Fields.Item(0).value = 14 Then
'get value
MsgBox (objRecordset.Fields(2).value)
'exit loop
objRecordset.MoveLast
End If
objRecordset.MoveNext
Wend
End Sub

Result:
ResultNote: Although in this example the 14th record was the same as the 14th row of the table but this is not always true. For more information please see the link below:


Example 2:

In this example all the values in the second field (“MyField2”) will be stored in an array:

Sub Example2()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim arrValues(1 To 100) As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
i = 1
'keep going until the end of the recordset is reached
While objRecordset.EOF = False
arrValues(i) = objRecordset.Fields.Item(1)
i = i + 1
objRecordset.MoveNext
Wend
End Sub

Result:
Result 2


Example 3:

In this example we will loop through the values of all records whose first field has value between 5 and 10. Although we could have used the method explained in the previous examples, but a faster method would be using a Select Query:

Sub Example3()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim varField1 As Variant
Dim varField2 As Variant
Dim varField3 As Variant
Dim varField4 As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("Select MyField1, MyField2, " & _
"MyField3, MyField4 " & _
"From MyTable1 " & _
"Where MyField1 > 5 and MyField1 < 10 ")
'find the target record
While objRecordset.EOF = False
varField1 = objRecordset.Fields(0).value
varField2 = objRecordset.Fields(1).value
varField3 = objRecordset.Fields(2).value
varField4 = objRecordset.Fields(3).value
objRecordset.MoveNext
Wend
End Sub

You can download the file and code used in this article from the link below:

See also:

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

3 thoughts on “Read Data from Table, Access VBA”

  1. sleeplesscat says:

    Thanks, it’s very helpful!
    How about when we need all record where the myfield3 = “data 16” and store it in an array? Assuming all of the datatypes are string.

  2. avi says:

    hi I need your help, I have a table that I need to fill in with data – but the data is a description of an activity and each activity is given a grade of number.
    for example : runing =2
    jumping =3
    writing=6
    what I want is to select from a list the activitys and to compile that in to a sum of the numbers given to the activitys.
    if that is posible I will be more thankfull for your help.

  3. Takis says:

    Hi I need your help.
    I have i.e. 3 tables in a Acces database and i have to create one more table which include all records from 3 old tables.It is possible

    Thanks

Leave a Reply

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