dcsimg
December 5, 2016
Hot Topics:

Switch from DAO to ADO

  • November 19, 2002
  • By Sam Huggill
  • Send Email »
  • More Articles »

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel