VBA for smarties: ADODB Recordset

ADODB Recordset

1. What is an ADODB Recordset ?

2. Purpose of an ADODB Recordset

3. Origin of ADODB Recordset

4. Create an ADODB Recordset
4.1 Early binding
4.1.1 Local scope
4.1.2 Private scope
4.1.3 Public scope
4.2 Late binding
4.2.1 Local scope
4.2.2 Private scope
4.2.3 Public scope

5. ADODB Recordset structure
5.1 Define fields
5.2 Fields collection
5.3 Field Properties
5.4 Delete field
5.5 Store the Structure
5.6 Create Records
5.7 Records from Excel Range
5.8 Records from Listobject
5.9 Records Add

6. External Data
6.1 ADTG file
6.2 XML file
6.3 Access file
6.4 Excel file
6.5 Text file
6.6 Open Excel file

7. Number of Records
7.1 Number of linked Records

8. Adapt Record
8.1 non linked Record
8.2 linked Record

9. Sorting Records
9.1 By 1 field
9.2 By 1 field ascending/descending
9.3 By several fields
9.4 By several fields ascending/descending
9.5 Restore sorting

10. Find Record
10.1 Search forward
10.2 Search backwards
10.3 Ignore Records
10.4 Search criteria
10.5 Record exists ?

11. Filter Records
11.1 Filter by 1 field
11.2 Filter by 2 fields
11.3 Remove filter

12. Store Recordset
12.1 .GetString
12.2 .GetRows
12.2.0 Transpose .GetRows
12.2.0.1 Application.Transpose
12.2.0.2 ActiveX ListBox
12.2.0.3 VBA-Array
12.2.1 Record selection
12.2.2 Field selection
12.2.3 Field order
12.3 .CopyFromRecordset
12.4 .Save
12.4.1 ADTG file
12.4.2 XML file
12.4.3 Excel to XML

13. Delete Record
13.1 Unlinked record
13.2 Several records
13.3 All records by filter
13.4 All records by loop
13.5 Linked record
13.5.1 Access > 2003
13.5.2 Access < 2007
13.5.3 XML
13.5.4 ADTG
13.5.5 CSV
13.5.6 Excel

14. Index

- .AddNew
- .CopyFromRecordSet
- .Delete
- .Fields.Append
- .Fields.Delete
- .Filter
- .Find
- .GetRows
- .GetString
- .MoveFirst
- .MoveNext
- .MoveLast
- .MovePrevious
- .Move
- .Open
- .RecordCount
- .Save
- .Sort
- .Update

1. What is an ADODB Recordset ?

An ADODB Recordset in VBA is a storage item: you can store all kinds of different things in it: numbers, texts, dates.
An ADODB Recordset is a database that you can design, fill and edit completely in the working memory.

VBA has several other options for storing data:
- a dictionary
- a collection
- an array variable
- an ActiveX ComboBox
- an ActiveX ListBox
- a Userform control ComboBox
- a Userform control ListBox
- a sortedlist

The choice of one of these methods depends on the goal to be achieved.
In this page no attempt is made to compare all these methods.
We limit ourselves to a discussion of the possibilities of the ADODB Recordset.

Most 'storage' methods do not have a sorting method.
The sorting method of the ADODB Recordset is the only VBA sorting method known to me that can be used to sort by different fields.
This sorting method is very fast.
Moreover, this sorting method is very special.
In mathematics it is called a 'stable sort'.
This means that items with equal values keep the order in which they were added to the set.
The ADODB Recordset also contains a built-in filter method.
These can be arguments to use an ADODB Recordset.

Since the ADODB recordset has a separate VBA library, you can use this method in all Office applications (Word, Access, Outlook, Excel, Powerpoint, etc.).

In this page I will not cover all aspects of the ADODB Recordset.
I will limit myself to the properties and methods that are similar to the other 'storage' methods in VBA that I mentioned above.

2. What is the use of an ADODB Recordset ?

You can use the ADODB Recordset to put data from all kinds of sources together and quickly edit them because they are loaded into the working memory via the ADODB Recordset and are easily accessible.
Instead of editing data in an Excel worksheet, a Word Document, a Powerpoint presentation, you perform all tasks in the working memory.
So there is no need to refresh the screen, or to do any recalculation; this makes the ADODB Recordset very fast.

3. The ADODB Recordset's origin

The ADODB Recordset is not part of the standard VBA library.
The ADODB Recordset is part of the library "ADODB.Recordset".
This can be found in the VBEditor; tab 'Tools', option 'References' as Microsoft ActiveX Data Objects n.n Library. Office 14 version 2.0, office 365 version 6.1
It is located in the file .... ....\Common Files\System\Ado\msado20.tlb or in a similar directory.

You can make an active connection to this file via the VBEditor by checking Microsoft ActiveX Data Objects n.n Library in the References (Tools/References...)
If you distribute a file in which you use an ADODB Recordset, the connection to this library is also distributed.

In VBA you can create this reference in two different ways

ThisWorkbook.VBProject.References.AddFromFile "C:\Common Files\System\Ado\msado20.tlb"

or by

ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4

In order to use the ADODB Recordset, an active reference ('early binding') is not required.
You can also have the code connect to the library when needed ('late binding').
Use this VBA code for 'late binding':

createobject("ADODB.Recordset")

4. Create an ADODB Recordset

There are several methods to create an ADODB Recordset.
They depend on:
- the way you want to refer to the library of the ADODB Recordset: 'early binding' or 'late binding'
- the 'scope' of the ADODB Recordset:

available in 1 procedure:'local scope'

available in all procedures in the same codemodule: 'private scope'

available in all codemodules: 'public scope'

4.1 Reference to the library: 'early binding'

4.1.1 Local scope

If you only need the ADODB Recordset in 1 macro or function you can limit the definition of the ADODB Recordset to that procedure.

without a variable

With new ADODB.Recordset

.Fields.Append "aa1", 129, 120

end with

declaration and assignment to a variable simultaneously

dim db_00 as New ADODB.Recordset
db_00.Fields.Append "aa1", 129, 120

assignment to a variable and implicit declaration simultaneously

set db_00 = New ADODB.Recordset
db_00.Fields.Append "aa1", 129, 120

declaration (as Object) and distinct assignment to a variable

dim db_00 as Object
Set db_00 = new ADODB Recordset
db_00.Fields.Append "aa1", 129, 120

declaration (as ADODB Recordset) and distinct assignment to a variable

dim db_00 as ADODB.Recordset
Set db_00= new ADODB Recordset
.Fields.Append "aaa", 129, 120

4.1.2 Private scope

If you want to make an ADODB.Recordset accessible for different procedures (macros or functions) in 1 worksheet, workbook, Document, Userform, Macromodule or Classmodule, you can use Private Scope.
The use of a variable is indispensible in this case.

First this variable must be declared in the declaration area of the codemodule.
You can declare the data type of the variable for an ADODB.Recordset as Object, ADODB.Recordset or Variant.
Since Variant is the default data type, you can omit the specification of this data type.

In the declaration area of the codemodule

Dim db_00

Private db_00

Dim db_00 as Variant

Private db_00 as Variant

Dim db_00 as Object

Private db_00 as Object

Dim db_00 as ADODB.Recordset

Private db_00 as ADODB.Recordset

In a procedure (macro or function)

Set db_00 = New ADODB.Recordset

A compact approach in the declaration area

Dim db_00 As New ADODB.Recordset

Private db_00 as New ADODB.Recordset

Now then ADODB Recordset is available in all procedures in the codemodule by the variable db_00.

4.1.3 Public scope

4.1.3.1 Declaration and assignment

In order to make a variable accessible in the whole VBA Project the variable must be declared Public in the declaration area of the codemodule in a standard macromodule.
The next step is to assign a new instance of an ADODB Recordset to the public variable.
If you use 'early binding' it can be done very densely in one line using this code in the declaration area.

Public db_00 As New ADODB.Recordset

Now the ADODB Recordset is available in every procedure in the VBproject (= workbook, document, presentation) through the variable db_00.

4.1.3.2 Declaration in the declaration area and assignment in a procedure

This approach has 2 steps:
- Declare the variable as public in the declaration area of the codemodule of a standard macromodule.

The variable type can be Object, ADODB.Recordset or Variant.
Since Variant is the default variable type, you can omit this type's specification.

- Assign an ADODB Recordset instance to the previously declared public variable in a procedure.

In the declaration area

Public db_00

Public db_00 as Variant

Public db_00 as Object

Public db_00 as ADODB.Recordset

In a procedure (macro or function)

Assign a new ADODB Recordset instance to the declared public variabele.

Set db_00 = new ADODB.Recordset

Now the ADODB Recordset is available in every procedure in the VBproject through variable db_00.

4.2 Without reference to the library: 'late binding'

4.2.1 Local scope

If the use of the ADODB Recordset is restricted to 1 macro or function you can restrict the ADODB Recordset definition to that procedure.

without a variable

With CreateObject("ADODB.Recordset")

db_00.Fields.Append "aa1", 129, 120

end with

with a variable

set db_00 = CreateObject("ADODB.Recordset")
db_00.Fields.Append "aa1", 129, 120

4.2.2 Private scope

If you want to make an ADODB.Recordset accessible for different procedures (macros or functions) in 1 worksheet, workbook, Document, Userform, Macromodule or Classmodule, you can use Private Scope.
The use of a variable is indispensible in this case.
This variable must first be declared in the declaration area of the codemodule.
Every workbook, document, userform, sheet, classmodule has its own codemodule.

You can declare the data type of the variable for an ADODB.Recordset as Object, ADODB.Recordset or Variant.
Since Variant is the default data type, you can also omit the specification of this data type.

In a procedure (macro or function) in the same codemodule where you declared the variable you can assign an ADODB Recordset instance to that variable.

In the declaration area in the codemodule

Dim db_00

Private db_00

Dim db_00 as Variant

Private db_00 as Variant

Dim db_00 as Object

Private db_00 as Object

In a procedure (macro or function)

Set db_00 = CreateObject("ADODB.Recordset")

Now the ADODB Recordset is available in every procedure in the codemodule throug the variable db_00.

4.2.3 Public scope

If you want to make an ADODB.Recordset accessible to all procedures (macros or functions) in the Workbook, Document, Worksheet, Workbook, Userform, Macromodule or Classmodule, you must use Public Scope.
The use of a variable is indispensible in this case.
This variable must first be declared in the declaration area of a standard macrocodemodule.

The variable's data type can be either Variant or Object.
Since a link to the ADODB Recordset library is lacking data type 'ADODB.Recordset' isn't available.

In a procedure (macro or function) you assign an ADODB Recordset instance to the declared variable.

In the declaration area in the codemodule of a standard macromodule

Public db_00

Public db_00 as Variant

Public db_00 as Object

In a procedure

Set db_00 = CreateObject("ADODB.Recordset")

Now, by the use of the variable db_00 the ADODB Recordset is accessible for every procedure in the VBproject (=workbook, Document, Presentation, etc.).

In this web page I will only use the With ... End With method in a 'late binding' situation.
It will keep the example code simple.
'Late binding' can be more robust too, because it always uses the last available library on a system.
The number of 'missing' references will be diminished when exchanging files.
There is one limitation to 'late binding' though: you can't use the 'constants' (e.g. adFilterPendingRecords) in arguments of methods.
Only the use of values (e.g. 1 for adFilterPendingRecords) is allowed.

5. The ADODB Recordset structure

An ADODB Recordset requires a specific construction order in VBA

- Create a new ADODB Recordset instance

CreateObject("ADODB.Recordset")

- define the fields in the Recordset

.Fields.Append

- store the field definitions (i.e. the structure)

.Open

- add records

.Addnew

- fill the fields in a new record

.fields(0)=".."

- add the filled record to the recordset

.Update

So the basic VBA procedure to create an ADODB recordset looks like:

With Createobject("ADODB.Recordset")

.Fields.Append

- - - - -

.Open
.Addnew

- - - - -

.Update

End with

If the ADODB Recordset instance is meant for several successive 'databases' you need the method .Close to 'clean' the instance.

Structuring the ADODB Recordset precedes the filling of the database.
The fields constititute the ADODB Recordset's structure, similar to a database.
After defining the fields and saving their properties you can add records and fill the fields in each record.

5.1 Create Fields

A lot of field types (approx. 23) is available.
A substantial amount is meant for compatibility purposes.
I will use only the most common ones.

Create a field using the method .Fields.Append.
Each field requires a unique name: the first argument.
The field type is also obligatory: the second argument.
You must indicate the field type by its 'DataTypeEnumeration'.
The DataTypeEnumeration can be entered either by its name/constant or index number.
In several cases (a string or binary field) the field length has to be defined too: the third argument.
The field definition restricts the kind of data you can store in it.
It works analogously to the declaration of variables in VBA.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 120
.Fields.Append "bool_001", 11
.Fields.Append "curr_001", 6
.Fields.Append "date_001", 7
.Fields.Append "numeric_001", 131
.Fields.Append "dec_001", 14
.Fields.Append "double_001", 5
.Fields.Append "integer_001", 3
.Fields.Append "single_001", 4
.Fields.Append "binary_001", 128, 4

End With

or in 'early binding'

With New ADODB.Recordset

.Fields.Append "text_001", adChar, 120
.Fields.Append "text_002", adChar, 120
.Fields.Append "bool_001", adBoolean
.Fields.Append "curr_001", adCurrency
.Fields.Append "date_001", adDate
.Fields.Append "numeric_001", adNumeric
.Fields.Append "dec_001", adDecimal
.Fields.Append "double_001", adDouble
.Fields.Append "integer_001", adInteger
.Fields.Append "single_001", adSingle
.Fields.Append "binary_001", adBinary, 4

End With

5.2 The fields collection

The defined fields constitute the ADODB Recordset's structure.
In VBA it is the collection 'Fields'.
The first field is .Fields(0).

You can read all items in the Collections 'Fields' using For Each ... Next

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
for each it in .fields

msgbox it.name

End With

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
for j = 0 to .fields.count - 1

msgbox .fields(j).name

End With

5.3 Field properties

Every field has certain properties.
All properties can be read, some can be changed.
Some properties are independent of the values they contain.
Some other properties are only readable if the field contains some data.
It's not possible to change the 'Name' of a field, since it is part of the Recordset's structure.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
msgbox .Fields(0).Name
msgbox .Fields(0).Precision
msgbox .Fields(0).Type
msgbox .Fields(0).Attributes
msgbox .Fields(0).DefinedSize
msgbox .Fields(0).NumericScale
msgbox .Fields(0).Status

End With

' "text_001"
' 0
' 200
' 0
' 120
' 0
' 0

5.4 Delete a field

Before saving the field definitions by the method .Open you still can remove fields; afterwards you can't.
Use the index number to remove a field.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Fields.delete 0

End With

5.5 Save the database structure: the field definitions

The method .Open saves the database structure.
You can't adapt the saved structure.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

End With

5.6 Create Record

The ADODB Recordset distinguishes the creation of records and the storing of created records in the database.
The Method .AddNew creates a new record, the method '.Update' adds created records to the database.

When you create a record, it immediately consists of an item with all predefined fields.
If you use .Addnew you create an empty new record.
After that you can fill one or more fields.
To fill a field you can use the name of the field or the index number.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew
.Fields("text_001")="first"
.Fields(1)="second"

End With

You can create a record and fill one of its fields simultaneously.
You can do so using the arguments of the method .AddNew.
In the first argument you enter the name of the field that has to be filled.
In the second argument you enter the value of that field.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew "text_001","first"
.AddNew 1,"second"

End With

You can also create a record and fill several or all of its fields simultaneously.
You can do so using the arguments of the method .AddNew.
The first argument contains an Array of names or index numbers of the fields that have to be filled.
In the second argument you enter the values of those fields in another Array.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array("text_001","text_002"),Array("first","second")
.AddNew Array(0,1), Array("first value","second value")

End With

Which values can be assigned to a field is dependent of its field definition.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "bool_001", 11
.Open

.AddNew Array("text_001","bool_001"),Array("first",true)
.AddNew Array(0,1), Array("first value",False)

End With

You can fill the fields of a new record before it is added to the recordset or afterwards.

5.7 Convert an Excel Range into a Recordset

Assuming data in Range("A1:E10"), where rows can be interpreted as records and columns as fields.
Assuming the first row contains column headers.
To reduce the worksheet interaction we write the data into an array.
Now the array can be converted into an ADODB Recordset.

sn = sheets(1).Range("A1:E10")

With CreateObject("ADODB.Recordset")

for jj=1 to Ubound(sn,2)

.Fields.Append sn(1,jj), 129, choose(j,120,80,20,12,90)

next

.Open

for j=2 to Ubound(sn)

.AddNew Array(0,1,2,3,4),Array(sn(j,1),sn(j,2),sn(j,3),sn(j,4),sn(j,5))

End With

5.8 Convert a ListObject into a Recordset

The field names and data will be stored in 2 separate arrays to minimize worksheet interaction.
In this example string Fields will be defined with a fixed length (120).

sp = Sheet1.ListObjects(1).HeaderRowRange
sn = Sheet1.ListObjects(1).DataBodyRange

With CreateObject("ADODB.recordset")

For Each it In sp

.Fields.Append it, 129, 120

Next

.Open

For j = 1 To UBound(sn)

.AddNew sp, Application.Index(sn, j)

End With

5.9 Add Records

Add a created record to the database using the method .Update.
You can add each record separately or as a group.
You can add empty records or filled records.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "bool_001", 11
.Open
.AddNew Array(0,1), Array("first field",False)
.Update

End With

6 External data

If you read data from a file, you do not need to record the structure of the database.
The driver interprets automatically how many fields, what kind of fields and how many records are involved.
The file may be external, or the file that contains the macro itself.
Since the ADODB Recordset library is a database library, it contains features so that different users can edit the same database.
This is done with a number of properties: .CursorLocation and .LockType.
By default, a file is opened as Read-only (.Locktype = adLockReadOnly 1).
If you want to change records, the .LockType parameter must be adLockPessimistic ( 2 ) or adLockOptimistic ( 3 ).
In order to sort the data, the .CursorLocation property must have the adUseClient (3) value.

The method .Open creates access to the data in the file.
It has 5 arguments.
1 - the full name of the file involved.
2 - the driver that interprets the connection to the file.
3 - the property .CursorType.
4 - the property .LockType.
5 - additional information how to interpret the data.

6.1 ADTG file

The ADODB Recordset library creates ADTG files by default by its method .Save.
Open an ADTG file as Read-Only (default .LockType = 1):

With CreateObject("ADODB.Recordset")

.Open "G:\OF\example.adtg", "Provider=MSPersist", , , 256

End with

Open an ADTG file that you want to adapt (.LockType = 3):

With CreateObject("ADODB.Recordset")

.Open "G:\OF\example.adtg", "Provider=MSPersist", , 3, 256

End with

6.2 XML file

The ADODB Recordset library can convert a recordset into an XML file with the method .Save.
Open an XML file:

With CreateObject("ADODB.recordset")

.Open "G:\OF\example.XML", "Provider=MSPersist", , , 256

End With

6.3 Access file

If you open an Access file you can specify which data (which one of several Tables) should be read.
In a SQL-string you can specify which fields in which table should be read.
Read all fields in all records in table Q_test in Access file G:\OF\example.accdb.

With CreateObject("ADODB.recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\example.accdb"

End With

Access files earlier than Office 2007

CreateObject("ADODB.Recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\OF\example.mdb"

End With

6.4 Excel file

In the SQL string you'll have to enter the name of the worksheet (not the codename) that contains the data.
The first row in the worksheet should contain the field names.
The row in the worksheet will be interpreted as record.
Everything will run more smoothly if you use a Listobject.

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Sheet1$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\example.xlsb;Extended Properties=""Excel 12.0 Xml"""

End With

6.5 Text file

A txt/csv file should contain the field names in the first line, separated by the same field separator as in the following lines (records).
In order to read the file G:\OF\example.txt the file name is part of the SQL string.
The files' Path is the second element in the Driver description.
The cursortype is adOpenStatic (3).

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `example.txt`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\",3

End With

6.6 The macro containing file

A special case is the option to create an ADODB Recordset with the data in the file that contains the macro.
So you can use the properties and methods of the ADODB Recordset on those Excel data.
Most of the time these are much faster than Excel itself.

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `" & thisworkbook.sheets(1).name &"$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Thisworkbook.fullname & ";Extended Properties=""Excel 12.0 Xml"""

End With

7 Number of Records

The property .Recordcount returns the number of records in the ADODB Recordset.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first", "second")
.AddNew Array(0,1), Array("second record","second field")
.Update

msgbox .RecordCount

End With

7.1 Number of Records in a linked file

The property .Recordcount doesn't automatically indicate the number of records in a linked file.
The property .CursorType determines whether .Recordcount can read the number of records.
The default value of .RecordCount is -1.
Only if the property .CursorType is set to adOpenStatic (3), .RecordCount returns the number of records in the linked file.

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `example.csv`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\", 3
MsgBox .RecordCount

End With

8 Adapt a Record

The position of the recordpointer determines which record you can adapt.
Only the fields of the 'current' record can be adapted.
So you need to move the recordpointer to the record you want to adapt.
You can move the recordpointer in the recordset with
.MoveFirst: the first record
.MoveNext: the next record
.MoveLast: the last record
.MovePrevious: the previous record
.Move 3: move to the 3rd record after the current recordpointer position
.Move -6 : move to the 6th record before the current recordpointer position
.Move 3, 1 : move to the 3rd record in the recordset (3rd form the first record)
.Move -7, 2: move to the 7th to last record in the recordset.

Another method to move the recordpointer is the use of the property .Find.
If the property .Find has a valid result the found record is the recordpointer position.

The property .Filter can also be used for this purpose.
The property .Filter sets the recordpointer in the first record of the filtered records.

After you have adapted a value in a field of the 'active' record you need to .Update in order to make this adaption permanent.

8.1 non linked Record

Example: change the second record

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "second field")
.AddNew Array(0,1), Array("second record","second field")
.AddNew Array(0,1), Array("third record","second field")
.Update

.MoveFirst
.MoveNext

.Fields(0) = "adapted"

End With

8.2 linked Record

When you link to a file, as in 6.1 through 6.6, you will need to make sure the user has the rights to adapt the records.
By default you do not have the permission to do so.
The property .CursorType regulates that authorization.
By default, the property .CursorType is set to Read only: adOpenForwardOnly (0).
To have the user change Records, change the property to adOpenDynamic (2).
You can do that in the second argument of the method .Open.
After you have changed a field in an active record, the method .Update stores the changes permanently in the linked file.

With CreateObject("ADODB.Recordset")

.Open "Select * FROM `" & thisworkbook.sheets(1).name & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml""", , 2

.Fields(0)="new value"
.Update

End With

9 Sorting Records

The sorting options in the ADODB.Recordset make this library unique:
- you can sort on several fields
- you can sort ascending or descending each field independently
- the record structure will not be affected by the sorting
- the order in which records are added to the recordset won't be affected.
- the sorting is a 'stable sorting': 'ties' in de sorting result will be presented by their record index
- the field type (string or numbers/dates) determines the sorting method (alphanumeric or numeric).
- in VBA it is the simplest sorting method on several fields in a 2-dimensional array.
- the sorting is very fast.

NB. The .Sort property can't sort records in a linked file.

The sorting property is .Sort
The name of the sorting field has to be assigned to the property .Sort.
The default sorting order is ascending.

9.1 Sorting 1 field

In the ADODB Recordset sorting is a property.
Assign the field name to the property .Sort in order to sort the recordset by that field.
You can 'hard code' the field name or the Field property .Name.

With CreateObject("ADODB.Recordset")

End With

9.2 Sorting 1 field ascending/descending

The default sorting order is ascending.
Without any specification after the field name the recordset will be sorted by that field in ascending order.
You can specify the sorting order explicitly using ASC or DESC.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "second field field")
.AddNew Array(0,1), Array("second record","second field")
.Update

.Sort = "text_002"
.Sort = "text_002 ASC"
.Sort = "text_002 DESC"
.Sort = .Fields(1).Name
.Sort = .Fields(1).Name & " ASC"
.Sort = .Fields(1).Name & " DESC"

End With

9.3 Sorting by several fields

Sorting by several fields can be accomplished by adding field names, separated by a comma.
The order of the sorting matches the order of the field names in the property .Sort.
Records with identical values in a field keep their order in the original Recordset: 'stable sort'.

With CreateObject("ADODB.Recordset")

End With

9.4 Sorting fields ascending or descending

Per field you can (in the case of ascending sorting) or must (in the case of descending sorting) indicate the sorting order.
Each field name will be followed by ASC or DESC.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "second field")
.AddNew Array(0,1), Array("second record","second field")
.Update

.Sort = "text_002,text_001"
.Sort = "text_002 ASC,text_001 ASC"
.Sort = .fields(1).name & " ASC," & .fields(0).name & " ASC"

.Sort = "text_002 DESC,text_001 DESC"
.Sort = "text_002 DESC,text_001"
.Sort = .fields(1).name & " DESC," & .fields(0).name & " DESC"
.Sort = .fields(1).name & " DESC," & .fields(0).name"

End With

9.5 Undo Sorting

You can undo the sorting of the ADODB Recordset by assigning an empty string to the property .Sort.
The original order in which records were added to the ADODB Recordset will be restored.

With CreateObject("ADODB.Recordset")

End With

10 Searching Records

The ADODB.Recordset has no 'record' object.
So it isn't possible to read a record using a code like

x0 = .records(4).fields(1)

The ADODB.Recordset has a recordpointer.
The recordpointer determines which record is the 'active' record.

The ADODB recordset has a method to search for a record: .Find
This method is restricted to a search in the values in 1 field.
If you want to search in several fields use the property .Filter.

The first argument of the method .Find requires the name of the field and the value to be found.
The value should be surrounded by single quotes.

The method .Find can do a forward search: from the actual position to the last record.
The method .Find can do a backward search: from the actual position to the first record.
The third argument of the method .Find contains this direction parameter: forward (1) or backward (-1).

A forward search without a match results in recordpointer .EOF (End of File)
A backward search without a match results in recordpointer .BOF (Begin of File)
If the search method has a match the recordpointer is equal to the matching record.

To include all records in a forward search you need to move the recordpointer to the first record: .MoveFirst.
To include all records in a backward search you need to move the recordpointer to the last record: .MoveLast.

10.1 Forward searching

To include all records in the search move the recordpointer to the first record: .MoveFirst
The third argument in the method .Find is the direction parameter.
The forward direction parameter is the constant adSearchForward or the value 1.
The purpose of the msgbox is to illustrate the position of the recordpointer by showing the values in the found record.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "second field")
.AddNew Array(0,1), Array("second record","second field")
.Update

.MoveFirst

.Find "text_001='second record'",,1

If Not .EOF then MsgBox Join(Array(.Fields(0),.Fields(1)),vbTab)

End With

10.2 Backward searching

To include all records in the search move the recordpointer to the last record: .MoveLast
The third argument in the method .Find is the direction parameter.
The backward direction parameter is the constant adSearchBackward or the value -1.
The purpose of the msgbox is to illustrate the position of the recordpointer by showing the values in the record found.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "second field")
.AddNew Array(0,1), Array("second record","second field")
.Update

.MoveLast

.Find "text_001='second record'",,-1

If Not .BOF Then MsgBox Join(Array(.Fields(0),.Fields(1)),vbTab)

End With

10.3 Skip records in the search

The second argument in the method .Find can be used to indicate how many records from the actual recordpointer position onward have to be skipped in the search.
In this example the 4th record is the first record found.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open
.AddNew Array(0,1), Array("first field", "first record")
.AddNew Array(0,1), Array("first field", "second record")
.AddNew Array(0,1), Array("first field", "third record")
.AddNew Array(0,1), Array("first field", "fourth record")
.Update

.MoveFirst

.Find "text_001='second record'",3,1

If Not .BOF Then MsgBox Join(Array(.Fields(0),.Fields(1)),vbTab)

End With

10.4 Search criteria

The search criterion in the method .Find can have all kinds of values.

.Find "text_001='Norway'"
.Find "text_001<>'Norway'"
.Find "[text_002] LIKE N*"
.Find "text_002 LIKE *n"
.Find "date_001>#7/22/2018#"

10.5 Check the existence of a record

The ADODB Recordset has no method to check a record's existence.
So Records might be added twice.
The method .Find checks whether at least one record matches a certain value.
If the method result is .EOF (forward) or .BOF (backward) no such record exists.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "first record")
.AddNew Array(0,1), Array("first field", "second record")
.Update
.MoveFirst

.Find "text_001='second record'",,1

MsgBox "record " & iif(.EOF, "does not ","") & "exist"

11 Filter Records

The ADODB Recordset has a built in filter property: .Filter.
This filter can filter in 1 or more fields at the same time.
Concatenate filter criteria with the operators OR and AND.

Filter criteria can use several operators:

- equal to	=
- not equal to	<>
- greater than	>
- smaller than	<
- begins with	Like x*
- ends with	Like *x
- contains	Like x

After the filtering the property .RecordCount shows the number of filtered records.
Only when the filtering has been undone .Recordcount shows the total number of records in the ADODB Recordset.

The filter criterion is a string.
To improve readability wrap a field name by brackets: [text_001]
If the criterion values is a string, it requires to be surrounded by single quotes: ='criterion'
A date must be wrapped by hash tags: =#12/13/2018#

11.1 Filter by 1 field

In the ADODB Recordset .filter is a property.
Enter the filter criteria as string in the property .Filter.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "first record")
.AddNew Array(0,1), Array("first field", "second record")
.Update

.Filter= "text_001='first field'"

MsgBox .Recordcount
.Filter = "text_0011 LIKE '* field'"
.Filter = "[text_002]<>'first field'"

End With

11.2 Filter by 2 fields

In the ADODB Recordset .filter is a property.
Enter the filter criteria as string in the property .Filter. Concatenate the filter criteria for each field with the operators AND and OR.

With CreateObject("ADODB.Recordset")

End With

11.3 Remove filter

The actual filter can be removed by assigning the value 0 to the property .Filter.
The property .RecordCount will now show the number of all records in the ADODB Recordset.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("first field", "first record")
.AddNew Array(0,1), Array("first field", "second record")
.Update

.Filter= "text_001='first field' AND text_002<>'second record'"

MsgBox .RecordCount

.Filter = 0

MsgBox .RecordCount

End With

12 Save the Recordset

There are 4 methods to save the contents of an ADODB RecordSet.

In VBA the data can be retrieved as a string
- c00 = .GetString

In VBA data can be retrieved as an Array
- sn = .GetRows

In Excel data can be written into a worksheet
- Range.CopyFromRecordset

Data can be saved as XML-file or as ADTG-file
- .Save

12.1 .GetString

You can read the complete recordset and put it into a string using the method .Getstring.
That string can be assigned to a variable.
The default record separator is vbCrLf: Chr(13) & Chr(10).
The default field separator is a TAB: vbTab: Chr(9).
In the 3rd argument of the method .Getstring you can specify the field separator.
In the 4th argument you can specify which record separator should be used.

With CreateObject("ADODB.Recordset")

c00 =.GetString

End With

To put all data into the clipboard in order to use them elsewhere

With CreateObject("ADODB.Recordset")

c00 =.String

End With

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' clipboard

.SetText c00
.PutInClipboard

End With

To save the data in a CSV-file or TXT-file:

With CreateObject("ADODB.Recordset")

Open "G:\OF\example.csv" For Output As #1

Print .GetString, #1

End With

To apply the standard Windows-options in your system you can use the third argument.
In Excel this Windows-option can be retrieved by Application.International(xlListSeparator) or Application.International(5). If necessary you can adapt the record separator in the fourth argument.

With CreateObject("ADODB.Recordset")

Open "G:\OF\example.csv" For Output As #1

Print .GetString(,,Application.International(5),vbCrLf), #1

End With

12.2 .GetRows

The method .GetRows stores the values in the Recordset into a 2-dimensional Array.

With CreateObject("ADODB.Recordset")

sn = .GetRows

End With

Although its name suggests otherwise every record will be represented by a column.
Each row in the Array is a field.
In order to create a more practical Array representation we'll have to transpose it.

12.2.0 Transpose .GetRows

12.2.0.1 Application.Transpose

If the Excel VBA library is in memory we can use the Excel function Transpose.
Caveat 1 : the data shouldn't contain any NULL values. Transpose can't handle those.
Caveat 2 : the upper limit to the data is approx. 5200. An Array greater than that can't be transposed by this Excel function.

With CreateObject("ADODB.Recordset")

sn = Application.Transpose(.GetRows)

End With

12.2.0.2 ActiveX Listbox

The Excel function Transpose is restricted to approx. 5200 records.
The ActiveX Listbox has no such constraints.
A Listbox can be populated by its property .List or .Column.
Those properties can be read too.
The .List property results in an Array that is the transposed property .Column Array and vice versa.
To transpose an Array you assign it to the property .List en read it by its property .Column, or the other way around.

With CreateObject("ADODB.Recordset")

sn = .GetRows

End With

With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}") ' - Microsoft Forms 2.0 ListBox

.Column = sn
sn = .List

End With

12.2.0.3 VBA-Array

Based on the resulting Array of .GetRows we can create a new empty Array, switching rows and columns.
Then the corresponding values can be entered into the new Array.

With CreateObject("ADODB.Recordset")

sn = .GetRows
ReDim sq(UBound(sn, 2), UBound(sn))

For j = 0 To UBound(sn, 2)

For jj = 0 To UBound(sn)

sq(j, jj) = sn(jj, j)

End With

12.2.1 Selection of records

The method .Getrows has the option to specify the number of records that should be retrieved.
If you leave the fist argument empty, all records in the Recordset will be read.
If you specify the number of records to be retrieved in the first argument, the position from which these records should be read is required too.
The second argument serves that purpose.
In the second argument the actual recordpointer is represented by 0
The first record is indicated by 1
The last record is indicated by 2

Read 3 records from the recordset, starting at the first record

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("field 1.1", "first record")
.AddNew Array(0,1), Array("field 2.1", "second record")
.AddNew Array(0,1), Array("field 3.1", "third record")
.AddNew Array(0,1), Array("field 4.1", "fourth record")
.AddNew Array(0,1), Array("field 5.1", "fifth record")
.Update

sn = .GetRows(3,1)

End With

12.2.2 Selection of fields

The method .Getrows reads all fields by default.
You can use the third argument to return only a selection of fields.
In the third argument of the method .Getrows you can enter the field name of the field that should be returned.
That can be 'hard coded' of by referring to the property .Name of a field.

With CreateObject("ADODB.Recordset")

End With

You need an Array containing field names to select several fields.

With CreateObject("ADODB.Recordset")

End With

12.2.3 Order of fields

If you want to restrict the fields to be read you need an Array containing field names.
In that Array you can also determine the order in which the fields and their data will be presented.
This offers an opportunity to control the resulting Array by row and by column completely.

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Fields.Append "text_003", 129, 90
.Open

.AddNew Array(0,1,2), Array("field 1.1", "first record","old")
.AddNew Array(0,1,2), Array("field 2.1", "second record","new")
.Update

sn = .GetRows(,,Array(.Fields(2).Name,.Fields(0).Name))

End With

12.3 Excel method .CopyFromRecordset

Excel has its own method to retrieve data form a recordset.
This method is bound to a cell in a worksheet.
The data in all records starting from the 'actual' record ( = recordpointer) will be copied into the worksheet.
If you need all records in the recordset you will have to move the recordpointer to the first record in the recordset.
In this method the recordset will be referred to by its property .DataSource.
In the second argument of the method .CopyFromRecordset you can specify the maximum number of records to be retrieved.
In the third argument the maximum number of fields.

With CreateObject("ADODB.Recordset")

End with

12.4 .Save

The method .Save in the ADODB Recordset library saves the recordset as a file.
In the first argument of this method: the fullname (path and file name) of the file.
In the second argument which kind of file you want to save.
By default the method saves the file as .ADTG (Advanced Data Table Gram) file.
The ADODB.recordset library can open an ADTG file very simply.
The method .Save can't overwrite or replace a file.
If a file already exists .Save will generate an error.
So check the file's existence before saving it.

12.4.1 Save as .ADTG file

With CreateObject("ADODB.Recordset")

End with

Open an .ADTG file:

With CreateObject("ADODB.Recordset")

.Open "G:\OF\example.adtg", "Provider=MSPersist", , , 256

End with

12.4.2 Save as XML file

Enter the constant 'adPersistXML' or the value 1 in the 2nd argument of the method .Save to save the Recordset als an XML file.

With CreateObject("ADODB.Recordset")

End with

12.4.3 Convert an Excel file to an XML file

The contents of an Excel worksheet can be transformed into an ADODB recordset.
The method .Save can save this Recordset as an XML file.
So you can convert an Excel worksheet into an XML file in 2 lines of VBA Code.

With CreateObject("ADODB.recordset")

.Open "SELECT * FROM `" & ThisWorkbook.Sheets(1).Name & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml"""

.Save "G:\OF\conversion.xml", 1

End With

13 Delete record

We have to distinguish between records that are part of an independent Recordset and records that are part of a linked file.

13.1 Delete a non-linked record

The method .delete deletes a record.
By default, if the argument of the method is empty, the method deletes the 'active' record.
If the argument is 1, the method performs its default deletion.
The method deletes created records before they have been added by the method .Update or when they are already part of the recordset (after .Update).

With CreateObject("ADODB.Recordset")

.Fields.Append "text_001", 129, 120
.Fields.Append "text_002", 129, 80
.Open

.AddNew Array(0,1), Array("field 1.1", "first record")
.AddNew Array(0,1), Array("field 2.1", "second record")

.Delete ' deletes the second record
.Update

.Delete ' deletes the active (first) record

End with

13.2 Delete several records

To delete several records at the same time the argument of the method .Delete has the value 2.
It looks as if this is only possible in combination with the .Filter property.

To delete records that have not yet been added to the recordset by the method .Update.
In this case we use the .Filter property to filter all records that have not yet been added to the database.
The .Filter value to filter these is adFilterPendingRecords (1)

With CreateObject("ADODB.Recordset")

End with

13.3 Delete all records in the recordset

First we have to filter all records in the recordset.
In this case the only way to do so is to set the value of .Filter to 1.
Then we can apply the method .Delete with its argument value = 2.

With CreateObject("ADODB.Recordset")

End with

13.4 Delete all records in the recordset in a loop

If you use a loop you can delete each 'actual' record with the default .delete method, without any specified argument.
You don't need a filter either.

With CreateObject("ADODB.Recordset")

.Delete
.Movefirst

Loop

msgbox .Recordcount

End with

13.5 Delete a linked record

In order to be able to delete/adapt a record you must be authorised.
The property .LockType sets the authorisation.
The default value of .Locktype is ReadOnly adLockReadOnly (1).
As soon as you open a file you can set the user's authorisation by .Locktype in the 4th argument of the .Open method.
If the property .Locktype is set to 2 (adLockPessimistic) the user is authorised to adapt/delete a linked record.

13.5.1 Delete linked Access record < 2007

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\OF\example.mdb", , 2
.Delete

End With

13.5.2 Delete linked Access record > 2003

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\example.accdb", , 2
.Delete

End With

13.5.3 Delete linked XML record

In a linked Access file records will be deleted immediately in the Access file.
If the linked file is an XML file you will have to save the file using .Save.

With CreateObject("ADODB.Recordset")

.Open "G:\OF\example.XML", "Provider=MSPersist", , 2 , 256
.Delete
.Save

End With

13.5.4 Delete linked ADTG record

In a linked Access file records will be deleted immediately in the Access file.
In the case of an ADTG file the recordset must be saved first using .Save.

With CreateObject("ADODB.Recordset")

.Open "G:\OF\example.adtg", "Provider=MSPersist", , 2 , 256
.Delete
.Save

End With

13.5.5 Delete linked CSV-record

I wasn't able to find a method to delete a record in a linked CSV-file.
The following code errors out in the line that contains .Delete.

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `example.csv`","Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\", , 2
.Delete
.Save

End With

There is a workaround to delete records in a CSV file.
Use a filter in the SQL string or the property .Filter in the ADODB.Recordset.
Then the non filterd records will be 'deleted'.
You can't write the result to the linked file directly.
Store the filtered result in a variable, close the recordset and replace the linked file by the values in the variable.
Or you can save the filtered result in a new CSV file.

In the SQL command

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `example.csv` Where [field1]='Haaglanden'", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\"
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\example_001.csv").write .GetString(, , ";", vbCrLf)

End With

The property .Filter

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `example.csv`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\" .filter= "[field1]='Haaglanden'"
c00=.GetString(, , ";", vbCrLf)
.close

End With

CreateObject("scripting.filesystemobject").createtextfile("G:\OF\example.csv").write c00

13.5.6 Delete linked Excel record

I wasn't able to find a way to delete a record in a linked Excel file.
The following code errors out in the line that contains .Delete.

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Sheet1$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\Example.xlsb;Extended Properties=""Excel 12.0 Xml""",,2
.Delete

End With

There is a workaround to delete records in an Excel file.
Use a filter in the SQL string or the property .Filter in the ADODB.Recordset.
Since it's not possible to write the result to the linked file directly you need a workaround.
Store the filtered result in a variable, close the recordset and replace the linked file by the values in the variable.
Or you can save the filtered result in a new Excel file.

In the SQL command

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Sheet1$` Where [field1]='Haaglanden'", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\Example.xlsb;Extended Properties=""Excel 12.0 Xml"""
WorkBooks.Add.Sheets(1).Cells(1).CopyFromRecordset .DataSource

End With

The property .Filter

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Sheet1$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\Example.xlsb;Extended Properties=""Excel 12.0 Xml"""
.Filter="[field1]='Haaglanden'"
st =.getrows .Close

End with

WorkBooks.Add.Sheets(1).Cells(1).resize(Ubound(st,2),Ubound(st))= application.transpose(st)