Switch from DAO to ADO
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