564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Linking to ADO databases with VBA Part four 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.
This blog is part of both a complete Excel VBA tutorial and a complete SQL tutorial. |
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).
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.
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.
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.
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.