BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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 (this blog)
- Finding Records Easily
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.
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()
'create a new connection, to link to the database
Dim cn As New Connection
'create a new recordset (but as yet it doesn't refer
'to anything)
Dim rs As New Recordset
'say where the connection string is pointing to (this
'will be different for every company and database - see
'www.connectionstrings.com for examples
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2008R2;Database=Movies;" & _
"Trusted_Connection=True;"
'open this connection to the database (always do this
'as late as possible, for speed reasons)
cn.Open
'open the recordset of films (or any other table, view or SQL
'SELECT statement), using the connection we've created
rs.Open "tblFilm", cn
'we'll automatically be on the first record - keep going
'until we reach the last one
Do Until rs.EOF
'for each film, print out its name (well, we had to do
'something!) if it lasted longer then 3 hours 10 minutes
If rs("FilmRunTimeMinutes") > 190 Then
Debug.Print rs("FilmName")
End If
'vitally, go on to the next record (otherwise the
'routine will loop endlessly on the first record)
rs.MoveNext
Loop
'close the recordset and (importantly) the connection
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()
'create a new connection, to link to the database
Dim cn As New Connection
'create a new recordset (but as yet it doesn't refer
'to anything)
Dim rs As New Recordset
'say where the connection string is pointing to (this
'will be different for every company and database - see
'www.connectionstrings.com for examples
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2008R2;Database=Movies;" & _
"Trusted_Connection=True;"
'open this connection to the database (always do this
'as late as possible, for speed reasons)
cn.Open
'open the recordset of films (or any other table, view or SQL
'SELECT statement), making sure we'll be able to change data
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
'we'll automatically be on the first record - keep going
'until we reach the last one
Do Until rs.EOF
'for each film, if it's Titanic ...
If LCase(rs("FilmName")) = "titanic" Then
'... reduce the running time by 20 minutes ...
rs("FilmRunTimeMinutes") = rs("FilmRunTimeMinutes") - 20
'... and write the change made back to database
rs.Update
End If
'vitally, go on to the next record (otherwise the
'routine will loop endlessly on the first record)
rs.MoveNext
Loop
'close the recordset and (importantly) the connection
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()
'create a new connection, to link to the database
Dim cn As New Connection
'create a new recordset (but as yet it doesn't refer
'to anything)
Dim rs As New Recordset
'say where the connection string is pointing to (this
'will be different for every company and database - see
'www.connectionstrings.com for examples)
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2008R2;Database=Movies;Trusted_Connection=True;"
'open this connection to the database (always do this
'as late as possible, for speed reasons)
cn.Open
'open the recordset of films (or any other table, view or SQL
'SELECT statement), making sure we'll be able to change data
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
'add a new film, filling in details
rs.AddNew
rs("FilmId") = 999
rs("FilmName") = "The Iron Lady"
rs("FilmRunTimeMinutes") = 107
'make sure we save these changes
rs.Update
'close the recordset and (importantly) the connection
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()
'create a new connection, to link to the database
Dim cn As New Connection
'create a new recordset (but as yet it doesn't refer
'to anything)
Dim rs As New Recordset
'say where the connection string is pointing to (this
'will be different for every company and database - see
'www.connectionstrings.com for examples)
cn.ConnectionString = "driver={SQL Server};" & _
"Server=SERVER_NAME\SQL2008R2;Database=Movies;Trusted_Connection=True;"
'open this connection to the database (always do this
'as late as possible, for speed reasons)
cn.Open
'open the recordset of films (or any other table, view or SQL
'SELECT statement), making sure we'll be able to change data
rs.Open "tblFilm", cn, adOpenDynamic, adLockOptimistic
'keep looping through records ...
Do Until rs.EOF
If IsNull(rs("FilmDirectorId")) Then
'... deleting all those whose director is null
rs.Delete
End If
'go onto the next record (WHETHER WE'VE JUST DELETED
'A FILM OR NOT)
rs.MoveNext
Loop
'close the recordset and (importantly) the connection
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:
'list out all fields
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.
- Linking to Data using VBA
- The ADO Object Library
- Of Connection Strings, Recordsets, Cursors and Locking
- Working with Records (this blog)
- Finding Records Easily