Get Value From Recordset Vba

(See the Type property section for a discussion of recordset types. The Button Code. This all works fine. Using Collections in VBA. Let's try: strSQL = "SELECT * FROM tblMain " & _ "WHERE [TextField] = " & strMyString When attempting to open this recordset, we get the same error! Why?. Create Individual Reports Based On The Value Of A Given Field In A Related Table This code is designed to create individual reports as Excel Workbooks based on the value of a given field in a related table. Return value. A Recordset object consists of rows and columns (like an Excel range), but the Recordset object can also hold only a single record with only one row and one column. I want to delete every entry in that recordset from the database. Value = FName End Sub. Introducing the DAO Recordset The DAO Recordset is basically a VBA object which enables us to gain access to information stored in database tables or queries via code. This string opens a recordset. Recordset Set rs = CurrentDb. When you create a recordset from VBA, however, the Jet engine isn’t able to locate the parameter references. Use these defaults to simplify your code. activeconnection = connection recordSet. When creating a query in Microsoft Access, you might want to return only distinct or unique values. Excel VBA Programming Arrays and Loops Arrays are usually used with loops. Most often, you won't know what values to supply until you get to that point in your code. When the user clicks it, they enter a value in an InputBox, and then I have used this value to fetch data into a recordset from a read only Oracle database connection. When we deal with data bases using ADO or DAO, we often retrieve the data using select statement to a record set, and we process to an array or we paste into a range. They are not actually on the form. I'm very happy to see you do it the right way. 5 AS MedianSpeed, tblSurveyData. Range("I3"). This post shows how to define an array, and to loop through each of the items in it. ADO can only read the Value property once from the provider. transfer the range table data to a ADODB. In this How To, I will show how to get the last value of certain field from the last record. I'm writing an Access VBA procedure which will call various SQL Server stored procedures. 10+ mistakes to avoid when using VBA Recordset objects the possibility means that any Recordset that includes such a field must be able to handle the multiple values. This example has just two columns but you can have as many as you like. Recordsets are objects that represent collections (sets) of records. Excel Geeking: Using VBA And ADO To Pull Data From SharePoint Lists Scott Excel , Software , VBA May 14, 2014 3 Minutes I've been developing a SharePoint team site for my area which will house all kinds of information about the process and tools that I and my team support. Simply assign a variant variable to this function's result. Otherwise it returns the first value found, or Null if there are no matches. Extract data from a record in a DAO Recordset. This string opens a recordset. All VBA related information will be posted on this blog. This is how I get the data and fill it (note: the query is simplified, I can't just ORDER BY DESC):. It then uses the GetRows method to retrieve the first three records of the Recordset, and it stores the selected records in a two-dimensional array. I think this works but how i can do to resolve a array of queries using transaction or multiples transactions, the code i put works only for one querie. Find "[PART NUMBER] = invPartNumberProperty. Well after spending more time than I wish to admit I thought I would post some code that I adapted/created (thanks to Carlmack for core sections of the code). count - 1 debug. recordSet Set recordSet = New ADODB. The following code uses the DAO database and Recordset objects to open the table, evaluate the SalesYTD field using a Select Case statement and then updating the Rating field with the value set in the variable strRating. The FreeVBCode site provides free Visual Basic code, examples, snippets, and articles on a variety of other topics as well. In other words, a. I'm trying to get records from a database and fill them into excel. sSQL = “Select Raw_Data. Use these defaults to simplify your code. Enumerating memberOf results in a collection of string values, the DN of each group the user is a member of. Connection Dim rst As New ADODB. MoveLast is the best and most appropriate way to populate the recordset and get its count. The problem is, of course, how to supply those values? The answer is to base your recordset on a Command object, which itself is based on the Parameter query. NET [Book]. Use the GetRows method to copy records from a Recordset. Collection types are very useful because it is easy to add items to it and iterate over the items in the collection for whatever purpose you have in mind. Finding a Specific Record in a Recordset. If the value returned is an object, the requested expression is a multi-value field, so we loop through the multiple values to return a delimited list. Open a table and read data by column: 18. BOF then Else do while not rec. I would like to know how this could be done in a reverse way i. To get the values in VBA you need to assign a ProcessBook symbol, as suggested by Marcos, and read from the symbol. This means that items with equal values keep the order in which they were added to the set. Recordcount property. Check if Value is in Array using VBA Private Function IsInArray ( valToBeFound As Variant , arr As Variant ) As Boolean 'DEVELOPER: Ryan Wells (wellsr. Examples: To reference a field named "FirstName", which is the second field in the table, you can use any of the following:-. On the right see my Excel Worksheet and the Message Box with the similar output from my VBA Macro. That needs to happen inside the Do-Until-Loop, to get the current value for each customer record. MoveNext Loop End If Recordset. For this example, I drew a simple shape at the size that I want, but you could add this smartness to any shape. The code below will return the value in the first field of the current record the Recordset object is pointing at: objRecordset. Learn More. The first subscript identifies the field and the second identifies the row number. If you have used Microsoft ADO in your VBA project, you must have used Recordset object. If you, or one of your users, wants to review the values in a table field without…using a form, you can do so by moving through each table row and displaying the…target field's value in a message box. in turn, those RecordSet objects get populated with data when Access is sort the client-side Access Forms without any problems in Access 2007 and 2010. From the help: "The default collection of a Recordset object is the Fields collection, and the default property of a Field object is the Value property. Download VBA functions Database functions Get recordset as array. Setting recordset to query in Access VBA [Solved] If this is your first visit, be sure to check out the FAQ by clicking the link above. 09/21/2018; 3 minutes to read +1; In this article. Value() property. A Property Get works very much like any function and follows the same pattern as any VBA function. LocalProvider. Nested recordsets. Introduction. ConnectionError, ComboBox, Cascade, ADODB, and GetString. To end the for loop at any given point we can use the exit for statement. You are already setting the connection in the. Asc The Ascii code of a character. Frankly speaking, I faced this kind of situation in my previous company and I prepared the report using VBA and handed over the file to my manager. It gives the row index (i) and the column index (0, 1, etc. Read record in recordset by referening the field name with '!' 17. sort ="name asc". Manual Vba Access 2007 Recordset. two methods of use: select value in listbox, , click on button execute subroutine, works fine. count - 1 debug. Fields(index). How can you check for null in a VBA DAO. The Rating field will be updated for each sales person based on their SalesYTD value. This MSAccess tutorial explains how to retrieve a value from a table using VBA code in Access 2003 (with screenshots and step-by-step instructions). However, you can easily get the proper count using either of the two methods written below. They are not actually on the form. VBA string to fetch a value from SQL server RecSet = CreateObject("ADODB. I'm writing an Access VBA procedure which will call various SQL Server stored procedures. The fields available to us in the recordset is based on the table or query that is used as a datasource, and are ordered in the recordset the same as they are ordered in the table or query. Get the most out of Microsoft Access with VBA - [Instructor] One of the most basic questions you can ask about a table or other recordset, is how many records there are within it. Hit ALT +F11 to open the VBA editor. VBA – Loop through arrays. Read record in recordset by referening the field name with '!' 17. Using a global connection like here #164 for use Transaction. Excel has some manual methods, such as Advanced Filter, for getting a list of distinct items from an input range. Show field name, type and value data type: 19. Recordset Dim strList As String Set db = DBEngine(0)(0) Set qd = db. Go to the Visual basic Editor (Press Alt+F11). This string opens a recordset. Nested recordsets in VBA Access 2007 introduced the possibility that a field in a recordset may itself be a recordset. (See the Type property section for a discussion of recordset types. Next, you define the MDX query and execute the query into a recordset. Otherwise it returns the first value found, or Null if there are no matches. We have a call to a stored procedure that was migrated to GP 2013. If the value has changed, execute a SQL INSERT statement to add a new row to the new table with the Column1 value and the concatenated Column2 value(s). From the help: "The default collection of a Recordset object is the Fields collection, and the default property of a Field object is the Value property. FindFirst Method: You can use the find first command to search through records quickly and manually coordinate two forms. Until we see what you have, everything we say is apt to be poorly focused generalities. Sum is a SQL function, not VBA. So using your example code, I (personally) would try something like this:. My problem is that I can get either a recordset or return value but not both. MoveLast is the best and most appropriate way to populate the recordset and get its count. If the count of numbers is odd the mid point is used if the count of numbers is even the average of the two values around the midpoint is used. For all general questions relating to Excel but not including VBA or formulas. Below is the VBA code snippet which reads all the Records of your RecordSet and put it in your Excel Sheet named Data. Sub FillPersons(ByRef connection As ADODB. Both have a final callback function where you can commit or rollback the transaction. I'll cover the following topics in the code samples below: ADODB. OpenRecordset( _ "Select Sum([SomeField] from [SomeTable] where ", _ dbOpenForwardOnly) ctl. If a column was given a Double data type and later in a row the value in that column is not numeric, that value will be ignored (set to Null). We are updating the table values using the input box. How to import Access table data to Excel. NET [Book]. The Button Code. Although VBA has ways of getting this information too, you might want to 'go outside' of VBA to get the milliseconds and system time directly from the windows operating system. If I SELECT * instead of MAX(EventID) I do pull back all the records which I can tell by using recordcount. However, you can easily get the proper count using either of the two methods written below. This ensures they have access to their results until they commit a new query. For this example, I need to get the value in Balance field from the last record which is $300. Sometime, we need a specific value of the last record. MS Access 2003: Access a value from a table using VBA code. Enumerating memberOf results in a collection of string values, the DN of each group the user is a member of. I can loop through the recordsets and get the cursor to stop on a field whose values don't match, lets say on row #x and the programmed message will say something like: "ROW: 699 Field: [RequestStatus] rs1. A primary reason for its success is its interactive query interface. The FreeVBCode site provides free Visual Basic code, examples, snippets, and articles on a variety of other topics as well. Return multiple values by using database recordset. The reason for this is I have to use dynamic query to query a linked server from a database built in Progress(Uhg!). If you, or one of your users, wants to review the values in a table field without…using a form, you can do so by moving through each table row and displaying the…target field's value in a message box. In this How To, I will show how to get the last value of certain field from the last record. As far as I know this is just as good as. bat file; List sheets. From the help: "The default collection of a Recordset object is the Fields collection, and the default property of a Field object is the Value property. I guess this is a limitation of using VBA & DAO with MS SQL Server. Value of the cell to the. Get the most out of Microsoft Access with VBA - [Narrator] Tables usually contain lots of data. com) 'DESCRIPTION: Function to check if a value is in an array of values 'INPUT: Pass the function a value to search for and an array of values of any data type. Here are some VBA techniques to keep the garbage out. Manual Vba Access 2010 Recordset Query I used the below vba code in Excel 2007 to query Access 2007 accdb database successfully. each if you want to execute them in parallel or async. I can loop through the recordsets and get the cursor to stop on a field whose values don't match, lets say on row #x and the programmed message will say something like: "ROW: 699 Field: [RequestStatus] rs1. Show field name, type and value data type: 19. Recordset Object. Visual Basic for Applications (VBA) can be used in Access to create Windows applications and retrieve database data. Although the Recordset object has a built in RecordCount property, there are some special requirements needed in order to use it. While interpreted ASP code is looping through the Recordset, making decisions about what to display and response writing some HTML, the server is using limited resources holding open a connection to the database. So you are saying leave it as I have it and return a recordset and then access the field value via the recordset. But what my question is why can't i get the ID for each iteration in excel from sql table and if the ID holds some value,i will update the record with that ID other wise i can insert the record. transfer the range table data to a ADODB. New keyword is used because in Dim statement only variable type is defined, here with "New" we are creating a New connection object and records set object and then assigning values to these two variables. However, in the end, I get date values in the Excel-sheet like 1955-08-31 and 1955-09-29 instead of 2015-09-01 and 2015-09-30. 5) to an integer variable, VBA will round the decimal value up or down to fit into an Integer data-type. Get the most out of Microsoft Access with VBA - [Instructor] One of the most basic questions you can ask about a table or other recordset, is how many records there are within it. Subject: [vb-access-l] Recordset in Access VBA -- using parameter value in Select query Posted by Paula (Database Systems) on Feb 18 at 2:51 PM Hello All, I have a ""credit card payment authorization"" form with multiple entries for a particular month. Depending on the cursor type chosen when opening the Recordset object this may or may not work. Sub FindProject() Dim strSQL As String Dim rst As ADODB. - make the data part of the recordset that the report is based on (it appears that you can get it from your query so why not add it to the report) - pass the value to the report in the OpenArgs parameter of DoCmd. Suppose you need to create a recordset that is based on a parameter query. When to use a form's recordset and when to use recordsetclone Recordsets are an essential tool of any Access programmer, there are many types and are used in a wide range of situations, today we're going to talk about form recordsets and provide some examples of their use. A Collection is an type that contains a set of related objects - in memory. Recordset Set rs = CurrentDb. 3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. I guess this is a limitation of using VBA & DAO with MS SQL Server. SQL, MS Access etc. Often it is required to get the records retrieved in an SQL fetch into a recordset to an array. Nested recordsets. Troubleshooting - How can I combine two reports and get the report of transactions of a customer. Re: Extracting values from RecordSet Hey BNIX, I took out that Connection part and it seems to work fine, appreciate the help! I do have another question however, I'm not asking how to actually achieve this as I know the forum has rules about bypassing security features, just want to ask if its doable. Tags: create a data table, create table vba, dynamic array, sql, vba create table, vba create table from recordset Become An Access Guru (Click to find out how. Instructions. ) both DAO and ADO recordsets. To use the ADODB connection you need add Microsoft ActiveX Data Objects reference. [Access 2003 VBA] Return AutoNumberID of SQL Insert Result? Mini Spy Is there a way to get the AutoNumber ID value of the record that was just inserted via a SQL INSERT query? [Access 2003. If the count of numbers is odd the mid point is used if the count of numbers is even the average of the two values around the midpoint is used. 09/21/2018; 3 minutes to read +1; In this article. Let's try: strSQL = "SELECT * FROM tblMain " & _ "WHERE [TextField] = " & strMyString When attempting to open this recordset, we get the same error! Why?. This VBA code helps you to download or import Access Table data or Query to Excel in one click. Re: Extracting values from RecordSet. Knowing how to read the results from a Microsoft Access query can help you make your Access application more dynamic. Bob says: As you can see, the disconnected recordset can do everything a collection class can do, but without having to hand-roll any of the methods, recordset has them builtin. I am trying to analyse the data via vba (For each file the recordset takes the data based on criteria). Close Example of DSum:. This MSAccess tutorial explains how to retrieve a value from a table using VBA code in Access 2003 (with screenshots and step-by-step instructions). The wizard should now display the Find a record on my form based on the value I selected in my combo box/list box option. ActiveConnection = Cnn1 ' rest of code. RecordCount I am told I need to move last since the query does not. Getting Column (Field) Names from a Recordset If this is your first visit, be sure to check out the FAQ by clicking the link above. This ensures they have access to their results until they commit a new query. Set F = Forms![Customers1] Set RS = F. This allows me to create a checkbox-style input for the user, but I am having difficulty retrieving the values using SQL in VBA to create a recordset. I need to get a cell value from a selected row of a table into a variable to pass to a java bean. Use these defaults to simplify your code. Then I add code to assign the values from the Recordset to these strings. Nested recordsets in VBA Access 2007 introduced the possibility that a field in a recordset may itself be a recordset. Note that ELookup() requires a reference to the DAO library. MS Access 2003: Access a value from a table using VBA code. I have run into an issue with sorting/ordering field values that contain parathesis. The following example will show you how to read or get the data from Worksheet Cell using Cell. Note that ELookup() requires a reference to the DAO library. This is because it's (fairly) easy to access each array position in a loop - you just use the loop variable between the round brackets of the array. 1): using VBA to pull values directly? May 09, 2012 at 04:45 PM | 422 Views I have a situation where I'd like to be able to pull a single value (or no more than a handful) via SAP Analysis for Excel, using only VBA- bypassing the need to use worksheet cells. The Value property of the Field object is the Default property viz Recordset. the IPICalculation interface of PI SDK to retrieve calculated data directly from the PI Server. To create a QueryTable connected to a database table using Excel or VBA. The following example will show you how to read or get the data from Worksheet Cell using Cell. If you, or one of your users, wants to review the values in a table field without…using a form, you can do so by moving through each table row and displaying the…target field's value in a message box. sSQL = “Select Raw_Data. VBA – Loop through arrays. Creating Login Security using Access VBA First, let me state that Access is not the most secure platform. So you are saying leave it as I have it and return a recordset and then access the field value via the recordset. I can get one record's result by using the DLookup fuction, of course, but I want to get every record's value. How can I find the maximum value in a recordset column? Function UpperPace(lngSurveyID As Long) As Integer. If I SELECT * instead of MAX(EventID) I do pull back all the records which I can tell by using recordcount. COUNTIF issue in my VBA (dynamic range needs fixing) Sum based on dates; Reposition Cells in a Column based on another; Help Please - need to copy data from Sheet 1 to all other sheets created using "add sequence worksheets based on list of cells". If you frequently update the list, you'll want to automate that task using Visual Basic for Applications (VBA. This means that items with equal values keep the order in which they were added to the set. …To do that in VBA, you can use the code that I'll show you right now, and it's…contained within the ShowEach module. If you want to retrieve data from another worksheet than the first worksheet in the closed workbook, you have to refer to a user defined named range. Clicking on the command button calls this code. I need to get a cell value from a selected row of a table into a variable to pass to a java bean. Only unique visible values from Column1 are added to the list box. MoveLast is the best and most appropriate way to populate the recordset and get its count. The default collection of a DAO recordset object is the FIelds collection, so what you wrote is the equivalent of: Recordset. There are basically 4 Methods that you can use to access the values of Fields in Recordsets. No you can define ADODB. Access VBA ADODB CopyFromRecordset Method to Copy All Fields Data from Access Table to Excel How to Transfer All Fields data from an Access ADO Recordset to Excel with VBA Macro 'Using ADO Method to Import data from an Access Database Table to an Excel worksheet, we need to use ADO in your VBA project, you must add a reference to the ADO Object. strSQL = "SELECT TOP 8 SpeedConv([speed],[measure])+0. Microsoft Access / VBA. Recordset Dim strList As String Set db = DBEngine(0)(0) Set qd = db. A stored procedure can be declared like this: CREATE PROCEDURE GetUserId @userid INT,. [Access 2003 VBA] Return AutoNumberID of SQL Insert Result? Mini Spy Is there a way to get the AutoNumber ID value of the record that was just inserted via a SQL INSERT query? [Access 2003. I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code. ) both DAO and ADO recordsets. This blog will also post articles related to them too Happy reading. It is my understanding - and I may be wrong - that you will have to open a recordset based on your query then retrieve the field value from your recordset to work on. You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. Specifically, I want to concatenate all the values of the "rosEmail" field from query "qselRosterEmailList" into one string (strEmails). The values are in my Parent Form's record set. The following code uses the DAO database and Recordset objects to open the table, evaluate the SalesYTD field using a Select Case statement and then updating the Rating field with the value set in the variable strRating. Note that ELookup() requires a reference to the DAO library. Raw data consists of customer details, which includes Name, Street Address, City, Region, Country and Phone number. As far as I know, a record has many values, so your RecordSet(index) does not return the value os a record. Re: VBA use iproperty part number to search an Access database recordset I'm not too up on pulling info from Access via VB, but this line: rs. 17 responses on " MS Access - VBA - Export RecordSet to Excel " Segolene Sansom December 21, 2017 at 5:23 pm. Fields(index). The dynamic query runs fine but I am having trouble returning the value back to Access using VBA. If your recordset has a large number of rows returned, then there's no reason to iterate through all of them. No you can define ADODB. 1 Explore Objects and Collections Callahan Chapter 12 Introducing the Issues Application Startup form to identify the user selection is stored in a global Public. A Property Get works very much like any function and follows the same pattern as any VBA function. For example, intField represents the field, and intRecord identifies the row number: avarRecords(intField, intRecord). This applies to complex data types - multi-value fields and attachments - in an ACCDB, not MDB. Value and will return the first fields's value. Download VBA functions Database functions Get recordset as array. Since some of the sprocs won't return data, I want the ability to both read a return value (which is a rowcount) AND a recordset. VBA - Unable To Get Data For Recordset Feb 28, 2014. I'll cover the following topics in the code samples below: ADODB. Method #1 Set Temporary Value using VBA. The reason for this is I have to use dynamic query to query a linked server from a database built in Progress(Uhg!). Learn More. (The failure message from PostgreSQL was: "ERROR: ExecAppend: Fail to add null value in not null attribute vendor_id". 5 AS MedianSpeed, tblSurveyData. Filter an Excel defined Table based on selected cell [VBA] Populate a combobox with values from a pivot table [VBA] Add values to a regular drop-down list programmatically [VBA] Filter an Excel defined Table programmatically [VBA] Populate a list box with unique distinct values from a filtered Excel table [VBA] Populate a combo box (form. Use these defaults to simplify your code. This MSAccess tutorial explains how to retrieve a value from a table using VBA code in Access 2003 (with screenshots and step-by-step instructions). 2 - The user clicks on the corresponding button to select the row. ) both DAO and ADO recordsets. The macro below opens the Sample. EOF and rec. Using Excel VBA to Copy an ADODB Recordset to Multiple Worksheets In adding functionality to an ancient Excel VBA reporting app for an Oracle database, I decided to set up a temporary worksheet to use as a place to store data before copying it to multiple other worksheets rather than copying directly from the recordset multiple times. Well after spending more time than I wish to admit I thought I would post some code that I adapted/created (thanks to Carlmack for core sections of the code). In workbook A, the name of Column A is FID, Column B is ASSETID. I really only want to use a return value in the transaction, not even an OUTPUT parameter, but it seems this won't be possible. This is done through the use of QueryDef objects. Manual Vba Access 2007 Recordset. The Value property of the Field object is the Default property viz Recordset. For all general questions relating to Excel but not including VBA or formulas. This sorting method is very fast. My problem is that I can get either a recordset or return value but not both. Often it is required to get the records retrieved in an SQL fetch into a recordset to an array. Updating, Adding And Deleting Records In a Recordset Robert Austin | October 3, 2013. In online forums or newsgroups dealing with Microsoft Access I frequently read questions about how to get the data from one column in a table or query into a comma (or similar) separated list of values in a string. Excel VBA - RecordSet To Array I have an SQL Query which returns a subset of data from one worksheet to another worksheet. Using recordset in VBA excel I have a huge data (16 files with 300,000 rows and 65 columns in each) in excel. However, you can easily get the proper count using either of the two methods written below. They are not actually on the form. Create a new saved query, or convert a SELECT statement to a saved query If the data you want to find is contained in more than one table, you will need to bind the form to a query that selects the data from those tables. I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code. CSV to recordset; Extract URL from shape; Extract URL from text; Find a suitable delimiter; Get file name or path; Get Program Files path; Get the address parts from a selected range; Get the last row or column; Get the windows username; List all Comments; List files properties from a folder; List files or folders using. Then it sets the value and displays the new value. However, the resulting recordset is read-only, so ADO cannot be used to modify Active Directory objects directly. Before moving to the next record the value of i is incremented by one ready for the next item. (See the Type property section for a discussion of recordset types. RecordCount I am told I need to move last since the query does not. If you, or one of your users, wants to review the values in a table field without…using a form, you can do so by moving through each table row and displaying the…target field's value in a message box. VBA -Import Data from Access To Excel ' The code below fetches data from an ACCESS Database ' Set a reference in the VBE to the latest version of the Microsoft ActiveX Data Objects Library on your computer. Let's try: strSQL = "SELECT * FROM tblMain " & _ "WHERE [TextField] = " & strMyString When attempting to open this recordset, we get the same error! Why?. two methods of use: select value in listbox, , click on button execute subroutine, works fine. Using Ref Cursors To Return Recordsets Since Oracle 7. Excel VBA Part 56. Assign this macro to the shape called Get the File Path on the Export worksheet by right clicking the shape and then choosing Assign Macro and selecting the macro and then clicking OK. The EventID field is numeric. In this How To, I will show how to get the last value of certain field from the last record. …To do that in VBA, you can use the code that I'll show you right now, and it's. It is VBA's representation of the table or query, and we make use of the methods and properties of the DAO Recordset to manipulate the data that it contains. Populate Combo box using recordset in vba Hello All, I am using the foll. LocalProvider. After an ADO Database Connection has been created, as demonstrated in the previous chapter, it is possible to create an ADO Recordset. Often it is required to get the records retrieved in an SQL fetch into a recordset to an array. Sorry I found the error, the Usage you put in the comments isn't the right name compared to the actual function name: Export2XLS vs ExportRecordset2XLS. As far as I know, a record has many values, so your RecordSet(index) does not return the value os a record. Filter VB recordset using VBA with a text field or combo box. Bob says: As you can see, the disconnected recordset can do everything a collection class can do, but without having to hand-roll any of the methods, recordset has them builtin. I'm writing an Access VBA procedure which will call various SQL Server stored procedures. Just as I love query solutions because they're clean, I love VBA solutions because they're cool. Recordset Object. Connection rst. OpenRecordset( _ "Select Sum([SomeField] from [SomeTable] where ", _ dbOpenForwardOnly) ctl. By default in ADO, when the Recordset is available, the connection to the database is open. Microsoft Access Query Tips and Techniques (SQL and VBA) by Luke Chung, President of FMS, Inc.