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:
- Which database you're using (eg Access, SQL Server).
- Where the database is, and its name.
- 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:
Dim cn As New ADODB.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:
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:
Dim rs As New ADODB.Recordset
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:
rs.Open "tblFilm", cn
rs.Open "SELECT FilmName FROM tblFilm ORDER BY FilmName", cn
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:
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:
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:
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:
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):
Access programmers can also use the horrible exclamation mark syntax:
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:
The cursor argument, showing that the default option is
adOpenUnspecified
There are four
possible values that you can specify, as follows:
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:
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!
