June 10, 2000

Faster client-side ADO 2.1 Recordset with the OPTMIZE dynamic property

ADO 2.1 added a new, important dynamic property to the Field object, the OPTIMIZE property. If you have a client-side Recordset and you set this property to True, ADO will create an Index for the specified field, and will then automaticaly use that index for any Find, Sort, and Filter

Replicate a string of any length

The String$ function can replicate only 1-char strings, so it seems that you need a loop to duplicate strings that contain 2 or more characters. However, this is a one-liner that does the trick: Function ReplicateString(Source As String, Times As Long) As String ‘ build a string of spaces whose

Export to a text file with the GetString method

The GetString method of the Recordset object returns a formatted string that contains all the values in each field and each row in the recordset, with your choice of column and row delimited. You can therefore use it to quickly and easily export the recordset to a comma-delimited text file,

Simple variables are always faster than array elements

Reading and writing an item of an array is always slower than accessing a simple variable. Therefore, if you need to repeatedly use the same array item in a loop, you should assign it to a temporary variable and use that variable instead. I’ve included an example of this technique

Faster string appending with Mid$ command

As you probably know, the “&” operator is rather slow, especially with long strings. When you have to repeatedly append chucks of characters to the same variable, you can speed up your code using a simple trick based on the Mid$ command. The idea is that you pre-allocate a buffer

FilterString – Remove invalid characters from a string

‘ Filter out all invalid characters in a string.Function FilterString(text As String, ValidChars As String) As String Dim i As Long, result As String For i = 1 To Len(text) If InStr(ValidChars, Mid$(text, i, 1)) Then result = result & Mid$(text, i, 1) End If Next FilterString = resultEnd Function

Export to a text file with quoted fields

As explained in another item in this Tip Bank, you can use the Recordset’s GetString method to export a Recordset to a comma-delimited or tab-delimited text file. However, if you are exporting string fields that might include commas or tabs, you should include the fields’ value within quotes. At a

Clone a Font object

When you want to assign a control’s Font to another control, the first obvious way is to assign the Font property directly, as in: Set Text2.Font = Text1.Font but in most cases this approach doesn’t really work, because it assigns a reference to the same font to both controls. In

Scan all the items in a multi-dimensional array with only one loop

It seems that you need two nested For loops to iterate over all the elements of a 2-dimensional array, and three loops for a 3-dimensional array, and so on. However, the For Each loop offers you a neat and concise solution, as this code proves: ‘ a 2-dimensional array of