This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Linking to Data using VBA The ADO Object Library Of Connection Strings, Recordsets, Cursors and Locking Working with Records Finding Records Easily (this blog) This blog is part of both a complete Excel VBA tutorial and a complete SQL tutorial.

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.

Linking to Data using VBA
The ADO Object Library
Of Connection Strings, Recordsets, Cursors and Locking
Working with Records
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

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:

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.

Parts of this blog
Linking to Data using VBA The ADO Object Library Of Connection Strings, Recordsets, Cursors and Locking Working with Records Finding Records Easily (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Finding Records Easily

Finding a Record Meeting a Criterion

Finding Multiple Records

Head office

London

Manchester