Switch from DAO to ADO

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

Page 4 of 5



This article was originally published on November 20, 2002