A few days ago, I started a
new project that handles a large database containing HTML code for a
complete web site. The project has to allow the webmasters of the web
site view all updates made to the site, when they were made and by whom.
They can also edit the pages on the site, and automatically upload them.
This project requires the use
of a fairly large database that needs to be accessed by many people from
different PCs. I decided to use SQL Server as the back end to the
project, but this meant that I couldn’t use DAO to connect to it! What a
So, I decided it was about
time I started to learn ADO. I took a quick glance around on the net at
my usual VB sites, but found little or no help for me on ADO.
Well, as we pride ourselves
here at VB Square on adding original content, I decided I would write an
article on using ADO.
This article is only really to
get you started on ADO, and only discusses the connection and recordset
objects. There are many more features of ADO that you will need to look
into before you take on a project using ADO.
With ADO, you can build all
your code around a local database and then, very easily change one line
of code that will allow you to access a database on a SQL Server.
The thing that took me a while
to figure out, was how to connect to a database. With DAO, you use the
OpenDatabase command passing the path of the database as one of the
arguements. But with ADO, you need to build a connection string. To
connect to a local database, use the following connection string:
ConnectionString = "Provider=Microsoft.JET.OLEDB.3.51; _ Data Source=c:mydb.mdb"
That may seem a bit
cumbersome, but this flexibility provides you with the means to connect
to almost any database in any format anywhere. The following connection
string is used to connect to a SQL Sever database named ‘people’:
ConnectionString = "driver=[SQLServer]; _ uid=admin;server=myserver;database=people"
The Connection object is the
base from which almost all ADO functions derive from. You can use this
object to carry out most of the actions performed in the sample code,
using SQL statements. E.g.
mCN.Execute "DELETE FROM People WHERE ID = 1"
I won’t go into any detail
about using SQL statements, but the MSDN
has some info on them.
The connection object returns
a recordset object if you use the Execute mehtod. You can use this to
create a DLL and use COM to get the contents of a recordset. e.g.
Public Sub GetRecordSet() As ADODB.Recordset GetRecordSet = mCN.Execute("SELECT * FROM People") End Sub
This means that you can
centralize all you database code into one component, preferably a DLL.
Using the Recordset Object
In ADO, the Recordset object
is very similar to the DAO Recordset object. This makes things a lot
easier when porting your code, although you will need to devise a few
workarounds to overcome a few missing features.
For example, when you insert a
record, but need to store its ID (AutoNumber) value in the same action,
you would normally use this code in DAO:
With rs .AddNew .Fields("Name").value = sNewValue .Update .Bookmark = .Lastmodified m_intRcdID = .Fields("ID").value .Close End With
The ADO Recordset object does
not expose a LastModified or LastUpdated property, so we need to use the
With rs .AddNew .Fields("Name").value = sNewValue .Update .Requery .MoveLast m_intRcdID = .Fields("ID").value .Close End With
After updating the recordset
(which you don’t need to do if you are moving to another record, as ADO
automatically updates changes made when you move records) you need to
refresh the recordset using the Requery method. Then you need to move to
the last record, which is the one you have just added. Now, just extract
the ID value and store it in a member variable.
To help you move from DAO to
ADO, I have made a similar sample application as I did for the Beginning
Databases article. The sample offers these features:
- Adding new records
- Deleting records
- Updating records
- Getting record data
It is a very simple demo, but
should help you to understand the basics. It use the latest version of
ADO, version 2.1. See the section at the bottom for downloading the ADO
Libraries and the sample applcation.
To get the sample application
to work, start a new Standard EXE Project and add a reference to the
Microsoft ActiveX Data Objects 2.1 Library (Project, References). Add
four command buttons (cmdAdd, cmdDelete, cmdGet, cmdSave) and three text
boxes (txtNotes, txtURL, txtName). Copy/paste the following code into
Option Explicit ' Private references to the ADO 2.1 Object Library Private mCN As Connection Private mRS As New Recordset ' Internal reference to the current records ID value Private mintRcdID As Integer Private Sub cmdAbout_Click() frmAbout.Show vbModal End Sub Private Sub cmdAdd_Click() AddRecord End Sub Private Sub cmdClose_Click() Unload Me End Sub Private Sub OpenConnection(strPath As String) ' Close an open connection If Not (mCN Is Nothing) Then mCN.Close Set mCN = Nothing End If ' Create a new connection Set mCN = New Connection With mCN ' To connect to a SQL Server, use the following line: ' .ConnectionString="driver=[SQL Server];uid=admin;server=mysrv;database=site" ' For this example, we will be connecting to a local database .ConnectionString = "Provider=Microsoft.JET.OLEDB.3.51;Data Source=" & strPath .CursorLocation = adUseClient .Open End With End Sub Private Sub AddRecord() ' Add a new record using the recordset object ' Could be done using the connection object mRS.Open "SELECT * FROM People", mCN, adOpenKeyset, adLockOptimistic With mRS .AddNew .Fields("Name").Value = txtName.Text .Fields("URL").Value = txtURL.Text .Fields("Notes").Value = txtNotes.Text ' After updating the recordset, we need to refresh it, and then move to the ' end to get the newest record. We can then retrieve the new record's id .Update .Requery .MoveLast mintRcdID = .Fields("ID").Value .Close End With End Sub Private Sub DeleteRecord() ' Delete a record and clear the textboxes mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic mRS.Delete mRS.Close txtName.Text = "" txtURL.Text = "" txtNotes.Text = "" End Sub Private Sub GetInfo() ' Get the data for a record based on its ID value mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic With mRS txtName.Text = .Fields("Name").Value txtURL.Text = .Fields("URL").Value txtNotes.Text = .Fields("Notes").Value .Close End With End Sub Private Sub UpdateRecord() ' Update a record's values mRS.Open "SELECT * FROM People WHERE ID =" & mintRcdID, mCN, adOpenKeyset, adLockOptimistic With mRS .Fields("Name").Value = txtName.Text .Fields("URL").Value = txtURL.Text .Fields("Notes").Value = txtNotes.Text .Update .Close End With End Sub Private Sub cmdDelete_Click() DeleteRecord End Sub Private Sub cmdGet_Click() ' Ask the user which record should be retrieved and get the data ' for that record mintRcdID = Val(InputBox$("Enter ID of record:", App.Title, "1")) GetInfo End Sub Private Sub cmdSave_Click() UpdateRecord End Sub Private Sub Form_Load() OpenConnection App.Path & "people.mdb" End Sub Private Sub Form_Unload(Cancel As Integer) If Not (mRS Is Nothing) Then Set mRS = Nothing End If If Not (mCN Is Nothing) Then mCN.Close Set mCN = Nothing End If End Sub