Linking to ADO databases with VBA
Part three 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 (this blog)
  4. Working with Records
  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.

Of Connection Strings, Recordsets, Cursors and Locking

To connect to a database, you'll need (at minimum) to know about connection strings and recordsets - you may also need to know about cursors and locking too.

You can fairly safely miss out this page and go straight onto the meaty one about how to add, edit, view and delete records.  All that you really need are the code excerpts to copy - understanding what they do is a bonus!

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:

  1. Which database you're using (eg Access, SQL Server).
  2. Where the database is, and its name.
  3. 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:

'create a new connection string

Dim cn As New ADODB.Connection

'say where the connection string is pointing to, and open connection

cn.ConnectionString = "driver={SQL Server};" & _

"Server=SERVER_NAME\SQL2008R2;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 SQL2008R2.  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:

Recordset diagram

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:

'create a new recordset

Dim rs As New ADODB.Recordset

'open a recordset of table of films

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:

'3 ways to open a recordset of table of films

'by specifying the table

rs.Open "tblFilm", cn

'by specifying a SELECT statement

rs.Open "SELECT FilmName FROM tblFilm ORDER BY FilmName", cn

'by specifying a view name

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:

Recordset picture

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:

'show the value of the film name

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:

'show the value of the film name (VBA

'will assume which property 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:

'show the value of the film name in the default Fields collection

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):

'show the value of the second field

Debug.Print rs(1)

Access programmers can also use the horrible exclamation mark syntax:

'use Access horrible notation

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:

Choosing a cursor

The cursor argument, showing that the default option is adOpenUnspecified

There are four possible values that you can specify, as follows:

Four cursor values

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:

Record locking values

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!

 

  1. Linking to Data using VBA
  2. The ADO Object Library
  3. Of Connection Strings, Recordsets, Cursors and Locking (this blog)
  4. Working with Records
  5. Finding Records Easily
This blog has 0 threads Add post