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.

  1. Linking to Data using VBA
  2. The ADO Object Library
  3. Of Connection Strings, Recordsets, Cursors and Locking
  4. Working with Records (this blog)
  5. 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:

Films lasting long time

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:

Record locking error

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:

Added film - the iron lady

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. 

 

This blog has 0 threads Add post