Switch from DAO to ADO

• facebook
• twitter
• google+
• in
• Email

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.

Page 3 of 5