Working with 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 blog 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\SQL2008R2;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 blog 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\SQL2008R2;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 blog 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\SQL2008R2;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\SQL2008R2;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.
Now that you have excerpts of code to add, edit, view and delete records, the
only improvement we could make would be to speed up searching - which is the
subject of the last part of this blog.
