Wanted to put this out to save someone else from the time I spent figuring this out: This happened in VB.Net under VS2005, using DAO3.6 and Access2003. The upgraded application was originally VB6, DAO3.51 and Access97.
In VB6 had a module level var named mLastUpdate As Date. Var was set to "Now" (current system date and time) when Name property was set.
Public Property Name() As String
Get
Name = mName
End Get
Set(ByVal Value As String)
mName = Value
mLastUpdate = Now
mDirtyData = True
End Set
End Property
Data was saved to a recordset with the name and lastupdate fields set:
myRS.Fields("Name").Value = mName
myRS.Fields("LastUpdate").Value = mLastUpdate
myRS.Update()
Then retrieved the row just inserted in order to get the Autogenerated ID number from the database table.
myRS = myDB.OpenRecordset("SELECT ID FROM Company WHERE LastUpdate=#" & mLastUpdate & "#;", dao.RecordsetTypeEnum.dbOpenSnapshot)
Code worked fine in VB6 - last added/updated row was returned; however, in .Net the row was not returned. When running the SQL inside Access using the date/time from the table it still would not return the row. Tried formatting mLastUpdate as it was being assigned to the recordset value, but still no luck.
Within the property SET, I then tried formatting:
mLastUpdate = FormatDateTime(Now, vbGeneralDate).
Now it works perfectly like it did before. I could have used @@Identity (which also works) but wanted to keep the code as close to the original as possible to avoid introducing more problems.
I'm not sure if the change that effects "Now()" is happening under Access or .Net, but wanted to make people aware that it is evidentally out there.