Linking to ADO databases with VBA
Part five of a five-part series of blogs

Learn how to write VBA macros to add, edit and delete data in Access, SQL Server and other databases, using something called ADO.

  1. Linking to Data using VBA
  2. The ADO Object Library
  3. Of Connection Strings, Recordsets, Cursors and Locking
  4. Working with Records
  5. Finding Records Easily (this blog)

This blog is part of both a complete Excel VBA tutorial and a complete SQL tutorial.

Posted by Andy Brown on 06 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Finding Records Easily

Although you can loop over all of the records in a recordset to find the one you want, it's usually better to set and apply a find criteria.

Although you may well be finding records in a SQL Server database, you're using VBA to do it, so the criteria syntax is in the style of VBA, and not SQL.  So it's asterisks (*), not percentage signs (%) for wildcards!

Having said this, my colleague Andrew has just pointed out that if you're connecting to a SQL Server database either wildcard works ...

Finding a Record Meeting a Criterion

The code below shows how you could find the first record against a text, date and numeric criterion.  The output of the code for our database would be:

Output of Find method

The 3 films found by the 3 separate criteria

Our procedure to find the 3 films might look like this:

Sub FindCriterionExamples()

'create and open a connection and recordset as per previous blog

Dim cn As New Connection

Dim rs As New Recordset

'variable to hold criterion

Dim FilmCriterion As String

cn.Open "driver={SQL Server};" & _

"Server=ANDYB\SQL2008R2;Database=Movies;Trusted_Connection=True;"

rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic

'find record meeting string criterion

FilmCriterion = "FilmName like '*Shrek*'"

rs.MoveFirst

rs.Find FilmCriterion

Debug.Print "Part of the Shrek series ==> ", rs("FilmName")

'find record meeting date criterion (could also use single

'quotes for the date

FilmCriterion = "FilmReleaseDate > #31 Jan 2007#"

rs.MoveFirst

rs.Find FilmCriterion

Debug.Print "Released since 31 Jan 07 ==> ", rs("FilmName")

'find record meeting integer criterion

FilmCriterion = "FilmOscarWins >= 11"

rs.MoveFirst

rs.Find FilmCriterion

Debug.Print "Winning at least 11 Oscars ==> ", rs("FilmName")

'close the recordset and the connection

rs.Close

cn.Close

End Sub

Note that in each case we return to the first record before finding the one we want using a criterion.

Finding Multiple Records

The above method only finds the first occurrence of a record matching a criterion, but you can repeat the process using code like this (this example would list out all of the Shrek films):

Sub FindShrekFilms()

'create and open a connection and recordset as per previous blog

Dim cn As New Connection

Dim rs As New Recordset

'variable to hold criterion

Dim FilmCriterion As String

cn.Open "driver={SQL Server};" & _

"Server=ANDYB\SQL2008R2;Database=Movies;Trusted_Connection=True;"

rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic

'find record meeting string criterion

FilmCriterion = "FilmName like '*Shrek*'"

'find all films matching this criterion

rs.MoveFirst

rs.Find FilmCriterion

Do While Not rs.EOF

'if we're not yet at the end of the recordset, it means

'that we found another SHREK film

Debug.Print rs("FilmName")

'now try and find another

rs.Find criteria:=FilmCriterion, skiprecords:=1

Loop

'close the recordset and the connection

rs.Close

cn.Close

End Sub

If you're finding that repeated uses of the Find method is running slowly, you might try using the Seek method instead (not shown here, and - I think - not that useful).

As I've just rediscovered, it's vital that you skip a record before trying to find more occurrences of the string in question - otherwise your code will loop indefinitely, continually displaying the name of the first film found!

And that completes one of the longer blogs in this series!  It's not exhaustive, but it covers pretty much everything most VBA programmers will need to know.  If you think I've missed anything, I'd welcome your comments below.

 

This blog has 0 threads Add post