Sometimes in Excel (for example) you'll need to retrieve data from a SQL Server table, an Access query or even a table in Oracle, Ingres or the like. This tutorial explains how to do this!
This topic is stuffed full of theory about things like cursors and recordsets, but the strange thing is that the resulting code can be copied and pasted whenever you need to use it, without always needing to understand exactly what's going on behind the scenes!
To link to data from within VBA, you will usually use the ActiveX Data Objects object library (ADO to its friends).
Referencing the ADO Object Library
You can create a reference to the ActiveX Data Objects library as follows:
From the menu within VBA select: Tools -> References...
Check the box next to the latest version of Microsoft ActiveX Data Objects that you can see, and select OK.
Here's what the dialog box looks like:
Make sure you tick the box next to the latest version of the library, rather than just selecting it in the list.
There are 7 different versions of ADO in the dialog box shown above - which one should you use? The short answer is: the highest version number. For more information on different versions of ADO, see this Microsoft ADO version history page.
Connection Strings
Any application can talk to a database, provided that you set up a connection (think of it as a thin pipe down which data can pass, and you won't be far off). Typically this involves specifying:
Which database you're using (eg Access, SQL Server).
Where the database is, and its name.
Any security requirements.
Getting connections working can be a pain, but to get the connection string you need I'd recommend ConnectionStrings.com - the ultimate resource for the required syntax!
To create a connection, you could use code like this:
Dim cn As New ADODB.Connection
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2022;Database=Movies;Trusted_Connection=True;"
cn.Open
Note that this is for connection to a SQL Server called SERVER_NAME, using a named instance called SQL2022. The connection links to a database called Movies, using Windows security. It is likely that your connection string will be different!
Recordsets
The other thing you'll need to create is a recordset. This is like a table of records held in memory. You can go forwards or backwards a record at a time, or go to the first or last record:
You can go back one record beyond the first to make the BOF (Beginning Of File) property become true, or go forward one record beyond the last to make the EOF (End Of File) property become true. However, you can not go into the illegal zone!
When you open a recordset, you will automatically be on the first record. From here you can go in the following directions:
Direction | What it means |
|---|
MoveFirst | Go to the first record |
MoveLast | Go to the last record |
MoveNext | Go to the next record (or the EOF zone if there isn't one) |
MovePrevious | Go to the previous record (or the BOF zone if there isn't one) |
Here's how you can open a recordset once you have a connection set up as above:
Dim rs As New ADODB.Recordset
rs.Open "tblFilm", cn
You can specify the name of a table, a view/query or a SQL statement, so the following 3 alternative lines of code would all work:
rs.Open "tblFilm", cn
rs.Open "SELECT FilmName FROM tblFilm ORDER BY FilmName", cn
rs.Open "vwFilmsInFilmNameOrder", cn
Referring to Fields in a Recordset
When you are have created a reference to a recordset, you'll usually want to refer to fields within it. It helps to have a picture in your mind of what the recordset looks like:
A recordset consists of a number of records, each having one or more fields. Here there are three fields:
Suppose that you want to refer to the film name King Kong (shown selected above), and you have created a recordset called rs to refer to the table above and are already pointing to the required record (number 3). There are several ways to refer to the field value shown.
One way is by referring to the value of the field called FilmName in the recordset:
Debug.Print rs.Fields("FilmName").Value
However, you can omit the Value property (whether it's good practice to do so is a moot point), and VBA will assume that this is what you mean:
Debug.Print rs.Fields("FilmName")
For that matter, you can also omit the Fields collection. Because a recordset can only be viewed as a collection of fields, VBA will fill in the missing bits:
Debug.Print rs("FilmName")
Fourthly, you could refer to the field by its number with the collection, rather than its name (fields are numbered from 0):
Access programmers can also use the horrible exclamation mark syntax:
Debug.Print rs![FilmName]
The first syntax is the clearest, but most code that you download off the Internet will use the third syntax - rs("FilmName").
Cursors
When you open a recordset in VBA, you can optionally specify a third argument stating what type of cursor you will use:
The cursor argument, showing that the default option is adOpenUnspecified
There are four possible values that you can specify, as follows:
The four possible values for the cursor - see the table below for what each means.
Here's what the four possible values mean:
Cursor | What it means |
|---|
adOpenDynamic | VBA loads the primary key of the underlying table, and will load other field values as and when needed (this cursor also allows you to write changes back to the database). |
adOpenForwardOnly | You are positioned at the start of the recordset, and (as the name suggests) can only move in a forward direction. |
adOpenKeyset | VBA loads the primary key of the underlying table (the film id in our case), and loads the other field values as and when needed. |
adOpenStatic | This will create a fixed copy of the underlying recordsets in memory. |
The reason this matters is that if you use the default value, you won't be able to use the recordset to add, edit or delete data; just view it.
Record Locking
The final bit of theory you might like to know about when using recordsets is that of record locking. Here are the possible values you can use for a recordset:
The possible values you can use for the fourth argument of the Open method for a recordset.
Again, here are what the different values mean:
Locking value | What it means |
|---|
adLockBatchOptimistic | Only used for batch updating of records. |
adLockOptimistic | When you try to change any data through VBA, the database will only lock the record that you are attempting to update when you commit your changes. |
adLockPessimistic | When you say that you're going to change a record, the database will lock it from updates for other users until your code releases it. |
adLockReadOnly | You can't make any changes to the underlying data. |
And that is the end of all the theory we need! Time to continue and do something more interesting - either list, add, edit or delete some records!
There are basically only 4 things you can do with records: view them, add to them, edit them and delete them. Sample procedures for each are shown below (the final part of this tutorial also shows how to find records quickly).
Viewing Records
You can loop forward through recordsets (displaying the values of each) as follows:
Sub ShowFilms()
Dim cn As New Connection
Dim rs As New Recordset
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2022;Database=Movies;" & _
"Trusted_Connection=True;"
cn.Open
rs.Open "tblFilm", cn
Do Until rs.EOF
If rs("FilmRunTimeMinutes") > 190 Then
Debug.Print rs("FilmName")
End If
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
Here's the output this might produce in the Immediate window:
Only 3 films in this database lasted longer than 190 minutes
We've used the default cursor and record locking methods because we don't want to change data; just view it.
Editing Records
You can edit records in almost the same way: by looping over the records in a table until you find the one you want (the final part of this tutorial shows a quicker way to locate the record you want).
The only difference is that you need to open the recordset with a sensible cursor and record locking combination, otherwise you won't be able to make changes to data.
The following code would shave 20 minutes off the running time of Titanic (surely a good thing?):
Sub ShortenTitanic()
Dim cn As New Connection
Dim rs As New Recordset
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2022;Database=Movies;" & _
"Trusted_Connection=True;"
cn.Open
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
If LCase(rs("FilmName")) = "titanic" Then
rs("FilmRunTimeMinutes") = rs("FilmRunTimeMinutes") - 20
rs.Update
End If
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
In the above code, we convert the film name to lower case before comparing it with titanic, just in case the film name wasn't entered in a sensible case (case comparisons are always case-sensitive in VBA).
Note that if you forget to open the recordset with a suitable cursor and record locking choice, you'll see the following message:
The main aim of this tutorial is to prevent other people experiencing the same pain that I have so often experienced!
Don't forget the Update statement! If you do, your code will run perfectly, but won't actually change any data.
Adding Records
You can use the AddNew method to add new records, but you must set values for all of the required fields in the underlying table.
Sub AddFilm()
Dim cn As New Connection
Dim rs As New Recordset
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2022;Database=Movies;Trusted_Connection=True;"
cn.Open
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs("FilmId") = 999
rs("FilmName") = "The Iron Lady"
rs("FilmRunTimeMinutes") = 107
rs.Update
rs.Close
cn.Close
End Sub
The code above would add The Iron Lady to our films table:
Because we haven't set values for any other fields, they contain NULL
Had the FilmId column been an identity or autonumber field in the above example, the database would have generated it automatically and we would have had to omit the line rs("FilmId") = 999.
Deleting Rows
After deleting a row in a recordset, the recordset pointer does not move onto the next record, but stays pointing to the (now deleted) row. This is shown in the following code to delete all films where the director id is not filled in:
Sub DeleteDirectorlessFilms()
Dim cn As New Connection
Dim rs As New Recordset
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2022;Database=Movies;Trusted_Connection=True;"
cn.Open
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
If IsNull(rs("FilmDirectorId")) Then
rs.Delete
End If
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
Deleting a record in this way should be done cautiously, since you may not know what triggers and cascade deletion will be set in the underlying database.
Listing out Fields
Because a recordset contains a collection of fields, you can also display the value of every field by looping over the collection:
For Each f In rs.Fields
Debug.Print f.Value
Next f
However, this is not usually that useful. 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()
Dim cn As New Connection
Dim rs As New Recordset
Dim FilmCriterion As String
cn.Open "driver={SQL Server};" & _
"Server=ANDYB\SQL2022;Database=Movies;Trusted_Connection=True;"
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
FilmCriterion = "FilmName like '*Shrek*'"
rs.MoveFirst
rs.Find FilmCriterion
Debug.Print "Part of the Shrek series ==> ", rs("FilmName")
FilmCriterion = "FilmReleaseDate > #31 Jan 2007#"
rs.MoveFirst
rs.Find FilmCriterion
Debug.Print "Released since 31 Jan 07 ==> ", rs("FilmName")
FilmCriterion = "FilmOscarWins >= 11"
rs.MoveFirst
rs.Find FilmCriterion
Debug.Print "Winning at least 11 Oscars ==> ", rs("FilmName")
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()
Dim cn As New Connection
Dim rs As New Recordset
Dim FilmCriterion As String
cn.Open "driver={SQL Server};" & _
"Server=ANDYB\SQL2022;Database=Movies;Trusted_Connection=True;"
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
FilmCriterion = "FilmName like '*Shrek*'"
rs.MoveFirst
rs.Find FilmCriterion
Do While Not rs.EOF
Debug.Print rs("FilmName")
rs.Find criteria:=FilmCriterion, skiprecords:=1
Loop
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 tutoiials 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.
