Switch from DAO to ADO

Tuesday Nov 19th 2002 by Sam Huggill
Share:

Switch from DAO to ADO

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 pain!

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 following workaround:

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 the form:

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

Download the ADO 2.1 Object Library

Download the sample project

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved