VBA Recordset: How to Handle Sets of Records Easily

Visual Basic for Applications or VBA is an important event driven programming language.  It is used for creating programs which enhance the Microsoft Office suite of applications. Visual Basic for Applications is perfect for developing specific applications, whether these are office resources, graphics programs, file sorting programs, or any other kind of Windows-based software programs. Today we look at the useful and frequently used Recordset function, in this intermediate level tutorial. You require a basic level of familiarity with Microsoft Access. If you’re new to them, you should first do this introductory course to Microsoft Access.  For folks who are familiar with VBA, you can just do a quick brush up with this VBA tutorial.

What is Recordset

A recordset is a structure which stores a group of records in a database. These records could be the result of a query or the contents of an individual table. Modifying the information stored in record sets does not affect the corresponding information contained in the actual database tables. Recordsets are widely employed as an integral part of database programming which includes Visual Basic for Applications.

Note that recordset is a blank table which is fully customizable with infinite rows and columns. These include whatever information a computer’s database returns, no matter how small or large. Good databases have the ability to generate and display recordsets. You can learn more about managing databases in Excel in this course.

Microsoft Office Access, a database, permits you to use VBA to create applications in Access. VBA is usually used to automate repetitive tasks in MS Office applications. MS Access is a relational database system generally used for smaller databases. A select query retrieves data from a database table. A recordset reads the content returned by the query. Here we look at a program to create a table and view its content using a select query. You can take this course from Udemy to learn more about using VBA with MS Access.

Example 1: How to use recordset in a query

Sub useRecordset()
Dim strSQL1 As String
Dim dbs1 As Database
Dim rst1 As Recordset
Dim tmpStr As String
Set dbs = CurrentDb
tmpStr = "Company | Last Name | "
tmpStr = tmpStr& "First Name | "
tmpStr = tmpStr& "Job Title | "
tmpStr = tmpStr& "Business Phone"
Debug.PrinttmpStr
strSQL1 = "SELECT Customers.Company, Customers.[Last Name], "
strSQL1 = strSQL1& "Customers.[First Name], "
strSQL1 = strSQL1& "Customers.[Job Title], Customers.[Business Phone]"
strSQL1 = strSQL1& "FROM Customers;"
Set rst1 = dbs1.OpenRecordset(strSQL)
rst1.MoveLast
rst1.MoveFirst
Do While Not rst1.EOF
tmpStr = rst1.Fields(0).Value
tmpStr = tmpStr& " | " & rst1.Fields(1).Value
tmpStr = tmpStr& " | " & rst1.Fields(2).Value
tmpStr = tmpStr& " | " & rst1.Fields(3).Value
tmpStr = tmpStr& " | " & rst1.Fields(4).Value
Debug.PrinttmpStr
Rst1.MoveNext
Loop
rst1.Close
dbs1.Close

In this program, declare the variables dbs1 as database object, rst1 as recordset object. Use SQL select command to the query the customer’s table. Open the recordset using the query. Loop through the recordset and display the records till the end of the recordset. In the end, close the database and the recordset objects.

Example 2: VBA Recordset to run a select Query

Private Sub runSelectQuery()
Dim db1 As Database
Dim rcrdSe1t As Recordset
Dim strSQL1 As String
Dim Xcntr1As Integer
Set db1 = CurrentDb
strSQL1 = "CREATE TABLE selectQueryData (NumField NUMBER, Tenant TEXT, Apt TEXT);"
DoCmd.RunSQL (strSQL1)
strSQL1 = "INSERT INTO selectQueryData (NumField, Tenant, Apt)”
strSQL1 = strSQL1& "VALUES (1, 'John', 'A');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL1)
strSQL1 = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "
strSQL1 = strSQL1& "VALUES (2, 'Susie', 'B');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL1)
strSQL1 = "INSERT INTO selectQueryData (NumField, Tenant, Apt) "
strSQL1 = strSQL1& "VALUES (3, 'Luis', 'C');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL1)
strSQL1 = "Select selectQueryData.* from selectQueryData "
strSQL1 = strSQL1& "WHERE selectQueryData.Tenant = 'Luis';"
Set rcrdSet1 = db.OpenRecordset(strSQL1)
rcrdSet1.MoveLast
rcrdSet1.MoveFirst
For Xcntr = 0 To rcrdSet1.RecordCount - 1
MsgBox "Tenant: " &rcrdSet.Fields("Tenant").Value & ", Lives in apt: " & _
rcrdSet1.Fields("Apt").Value
rcrdSet1.MoveNext
Next Xcntr
rcrdSet1.Close
db.Close
End Sub

In this program, we declare the variables Db1 as database object, rcrdSet1 as recordset object, strSQL1 as string object and Xcntr as Integer.The string object is assigned a query string.  DoCmd.RunSQL is a command which runs the string to create a table. A new table is created. Insert SQl command is used to insert records in the table. The recordset is opened and we move to the first record in the recordset. Display each record in the recordset until we reach the last record of the recordset. In the end we close both the recordset object and the database object.

Example 3: Program to Set the Value of a Specific Record

Dim sqlStr1 As String
Dim rst1 As Recordset
Dim dbs1 As Database
Set dbs1 = CurrentDb
sQLString = "CREATE TABLE editRecord (F_Name TEXT, L_Name TEXT)"
DoCmd.SetWarnings False
DoCmd.RunSQL (sQLString)
Strsql1 = "INSERT INTO editRecordVALUES('JOhn','Smith')"
DoCmd.RunSQL (strsql1)
strsql = "INSERT INTO editRecord VALUES('George','Bailey')"
DoCmd.RunSQL (strsql1)
strsql = "INSERT INTO editRecord VALUES('Glen','Maxwell')"
DoCmd.RunSQL (strsql1)
Set rst1 = dbs.OpenRecordset("SELECT editRecord.* FROM editRecord")
Set rst1 = dbs.OpenRecordset("SELECT editRecord.* FROM editRecord")
rst1.Move (2)
rst1.Edit
rst1.Fields("F_Name").Value = "PAUL"
rst1.Update
rst1.Close
Set dbs = Nothing

In this program, we declare sqlStr1 as string variable, rst1 as recordset object and dbs1 as database object. A new table is created using the DoCmd.RunSQL command. Three records are inserted into the table. A recordset is created by querying the table for allits records.We move through the record set to the record number 3 and update the “F_name” field to “Paul.” Then close the recordset. Press “F5” Functional key to run the subroutine.  You may want to learn more about Microsoft Access with this course to understand these examples better.

Example 4:  Search for Records in a Table

Sub searchRecords()
Dim rst1 As Recordset
Dim dbs1 As Database
Dim stringToSearch1 As String
Set dbs1 = CurrentDb
stringToSearch1 = "Dyna"
Set rst1 = dbs1.OpenRecordset("SELECT Customers.* FROM Customers")
Set rst1 = dbs1.OpenRecordset("SELECT <table name>.* FROM <table name>")
Rst1.MoveFirst
Do While Not rst1.EOF
If rst1.Fields("First Name").Value = stringToSearch1 Then
MsgBox "Found "&stringToSearch& " in record number: " & rst1.AbsolutePosition
rst1.MoveLast
Else
Rst1.MoveNext
End If
Loop
stringToSearch1 = "<your text to search>"
If rst1.Fields("<your column name>").Value = stringToSearch Then
rst1.Close
dbs1.Close

In the program, we declare the variables rst1 as Recordset object and  dbs1 as database object. Create a recordset by querying the customer’s table. Move to the first record of the recordset. Loop through the recordset by incrementing recordset position and search for “Dyna” in the first name column.  After the record is found, close the recordset and database objects. This course on using VBA with Microsoft Access has many more examples you may want to try out.

Example 5: How to copy from a recordset into a Table

Sub RecordsetExample()
Dim dbTest1 as Database
Dim rsRecordset1 as Recordset
Dim sqlStatement1 as String
Set dbTest1 = OpenDatabase("MyDatabase.mbd")
set rsRecordset1 = .OpenRecordset("Table1",dbOpenTable)
End With
sqlStatement1 = "INSERT INTO Table2 VALUES" & rsRecordset1
With rsRecordset1
rsRecordset1.MoveFirst
Do
rsRecordset1.RunSQL(sqlStatement1)
rsRecordset1.MoveNext
Loop While Not rsRecordset1.EOF
End With
End Sub

Declare the variables dbTest1, rsRecordset1,sqlStatement1 as type database, recordset and string objects respectively. Open the recordset object read the records and inserts the values into the database table. This operation terminates reaching the end of the recordset. In the end close the database and recordset objects.

Example 6:How to Use VBA to Import Data From Excel Into Access

Sub importExcelData()
Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim dbRst1 As Recordset
Dim dbs1 As Database
Dim SQLStrAs String
Set dbs1 = CurrentDb
Set xlApp = Excel.Application
Set xlBk = xlApp.Workbooks.Open("C:\Temp\dataToImport.xlsx")
Set xlSht = xlBk.Sheets(1)
SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
Set dbRst1 = dbs.OpenRecordset("excelData")
dbRst1.AddNew
xlSht.Range("A2").Select
dbRst1.Fields(0).Value = xlSht.Range("A2").Value
xlSht.Range("B2").Select
dbRst1.Fields(1).Value = xlSht.Range("B2").Value
dbRst1.Update
dbRst1.Close
dbs1.Close
xlBk.Close
End Sub

In the beginning, we declare xlApp, xlBk and xlSht as variables you will use to read Excel. Also, we declare the variables dbrst1 as recordset object and dbs1 as database object. Use DoCmd.RunSQL to execute an SQL command. We get values from the Excel workbook, save them to your table and update the records. In the end always remember to close  the database and recordset objects. If you’d like a more hands on approach, you can check out this course on MS Access.

Hope you had fun learning the Recordset function. Programming is best learnt by creating your own programs. Go through the examples above and play with the code. Once you’re ready to move it up to the next level, you can take this advanced VBA course for Microsoft Access by Simon Sez. It has a lot more juice and tips to make your work with MS Access so much more easier!