DAO Syntax???

[AlexB123]

Hi all,

I am updating code in a database created by someone else, and I came across the use of a recordset that seems problematic. The author did not declare whether they wanted to use ADO or DAO, and there seemed to be a few other minor issues. I made a few small updates, but am uncertain whether my code is correct. Could someone review these code blocks and let me know if I did things correctly? Thanks!

Dim dbs As Database, rcd As Recordset
Set dbs = CurrentDb
Set rcd = dbs.OpenRecordset("tblClients")
rcd.AddNew
rcd("ClientID") = inClientID
rcd("ClientName") = inClientName
rcd("CAM") = inCAM
rcd("ClientStatusID") = inClientStatusID
rcd("BOBID") = inBOBID
rcd("RKSystemID") = inRKSystemID
rcd("Server") = inServer
If Not IsNull(inCallCenter) And inCallCenter <> "" Then rcd("CallCenter") = inCallCenter
If Not IsNull(inClosedDate) And inClosedDate <> "" Then rcd("ClosedDate") = inClosedDate
If Not IsNull(inMergedTo) And inMergedTo <> "" Then rcd("MergedTo") = inMergedTo
If Not IsNull(inSisterPlan) And inSisterPlan <> "" Then rcd("SisterPlan") = inSisterPlan
If Not IsNull(inClientComments) And inClientComments <> "" Then rcd("ClientComments") = inClientComments
If Not IsNull(inPlanLedgerNum) And inPlanLedgerNum <> "" Then rcd("PlanLedgerNum") = inPlanLedgerNum
If Not IsNull(inUCAcctName) And inUCAcctName <> "" Then rcd("UCAcctName") = inUCAcctName
If Not IsNull(UCAcctSSNPID) And inUCAcctSSNPID <> "" Then rcd("UCAcctSSNPID") = inUCAcctSSNPID
If Not IsNull(inUCMT) And inUCMT <> "" Then rcd("UCMT") = inUCMT
rcd.Update
rcd.Close

Here is what I updated it to:

Dim dbs As DAO.Database
Dim rcd As DAO.Recordset
Set dbs = CurrentDb
Set rcd = dbs.OpenRecordset("tblClients")
rcd.AddNew
rcd("ClientID") = inClientID
rcd("ClientName") = inClientName
rcd("CAM") = inCAM
rcd("ClientStatusID") = inClientStatusID
rcd("BOBID") = inBOBID
rcd("RKSystemID") = inRKSystemID
rcd("Server") = inServer
If Not IsNull(inCallCenter) And inCallCenter <> "" Then rcd("CallCenter") = inCallCenter
If Not IsNull(inClosedDate) And inClosedDate <> "" Then rcd("ClosedDate") = inClosedDate
If Not IsNull(inMergedTo) And inMergedTo <> "" Then rcd("MergedTo") = inMergedTo
If Not IsNull(inSisterPlan) And inSisterPlan <> "" Then rcd("SisterPlan") = inSisterPlan
If Not IsNull(inClientComments) And inClientComments <> "" Then rcd("ClientComments") = inClientComments
If Not IsNull(inPlanLedgerNum) And inPlanLedgerNum <> "" Then rcd("PlanLedgerNum") = inPlanLedgerNum
If Not IsNull(inUCAcctName) And inUCAcctName <> "" Then rcd("UCAcctName") = inUCAcctName
If Not IsNull(UCAcctSSNPID) And inUCAcctSSNPID <> "" Then rcd("UCAcctSSNPID") = inUCAcctSSNPID
If Not IsNull(inUCMT) And inUCMT <> "" Then rcd("UCMT") = inUCMT
rcd.Update
rcd.Close

Set rcd = Nothing
Set dbs = Nothing<strike></strike>

 

[xenou]

Hi, I don't see any changes that would break anything - or to put it positively, this should work just the same as before.

 

[AlexB123]

LOL. Definitely not any significant change!

I was under the impression that the full DAO reference had to be included ... isn't there overlap between ADO and DAO? Or was there enough information in the former block for the computer to recognize which syntax was being used?

 

[xenou]

My understanding (I haven't really looked at this for a few years though) is that if you goto the references in your Visual Basic editor where you would check or uncheck (add or remove) references, then basically the references are resolved in the order that you see them in there.

So with a reference like "set rcd = recordset" then if DAO is above/before ADO, then it would resolve first, and take priority.

Also of course, if there were no reference to ADO at all then DAO would of course be the default. There was an attempted coup by ADO to become the de facto standard for MSAccess but it failed. I think DAO is still the default (or so to speak "native") library for Access.

As far as syntax goes, if you tried to use your same code but with the recordsets declared as ADO recordsets I think you'd quickly discover that it just wouldn't work at all.

 

[Micron]

here is some info regarding which Access versions are which (look at the far right side) http://allenbrowne.com/tips.html

With respect to a new db with version 2016, Microsoft 16.0 Access Database Engine Object library is already selected. This is ACEDAO.dll, and if you deselect it, Dim rs as recordset will fail, as will Dim rs as DAO.recordset or Dim rs as ADO.recordset. So it is the default in the sense that the library is already chosen for a new db, but not in the sense that it defaults to anything with absolutely no reference.

You can also deselect that reference and add DAO3.6, which will then work. Not sure why, but there seems to be some overlap between the 2 references.