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!