Forum Discussion
Update record via VBA fails
Since the windows 11 and office updates 11/11/2021 my VBA application which extracts data from an external XML file has simply stopped working.
I have debugged the code and traced the variables and SQL creation and all is as was previously, data collected correctly, Update statement correct and when run no error is shown however the updates do not reach the table.
Manual updates are working OK
Out of ideas.
Incidentally I know the SQL is valid, and the update code has been working for months prior to the latest update from Microsoft.
I may well roll back the Office updates after perhaps a reinstall.
UPDATE: I have removed any relevant updates - no change
now rolled back (x2) to 3 days ago and still not working. Also took old access app from backup and not working.
UPDATE 2 SOLVED:
Somewhere along the line the Date function has changed and extraction of the date from the NOW() timestamp seems to include the time element even after extracting via DateValue command as worked perviously.
I now used "datefrom = DateValue(DateAdd("d", -1, Now()))" to modify the date so the date relevant update can now proceed as previous.
5 Replies
Just to be sure I understand, can you post both the "before" and "after" versions of this expression?
DateValue() has ALWAYS extracted just the date from any date/time value.
Now() has ALWAYS returned the date and time.
If something changed, it's likely to be in your newest data sets, not in the VBA. You are subtracting one day from the current date to get a result, and that makes me think one possibility is that the "date" field in your data is now including times when they didn't before.
That's why I'd be curious to see exactly what the code was and exactly what it is currently. Also how it is used in context.Also, it would be helpful to examine those fields for the presence or absence of times with the dates.
I agree that your description of the date manipulators is what i would expect, which is how it was programmed initially. The confusion was why it suddenly stopped processing as it had done successfully for many months.
The input data was always a full timestamp generated internally, and my update statement was designed to only update records added that day. Just flummoxed as to why it stopped processing.
That solution of mine was false as the same problem occurred today although the 'fix' had been done.
i found a further article that stated that the data comparisons are done using the american format, mm/dd/yy. The 11/11/21 format worked by coincidence as the american and the international formats are identical.
I have now converted both date fields into ' Double' (Cdbl(Now()) to eliminate formatting issues and the comparisons are now valid, all works again.
For reference here is the code that failed after the last Windows 11 update:Dim datefrom As Date
datefrom = DateValue(Now())
'DoCmd.SetWarnings True
Me.COL_CALL.SetFocusaddr2value = StrConv(addr2value, 3)
fnamevalue = StrConv(fnamevalue, 3)sqlquery = "Update TABLE_HRD_CONTACTS_V01" & _
" set COL_DXCC = '" & dxccvalue & "',COL_NAME= '" & fnamevalue & _
"',COL_QTH = '" & addr2value & "',COL_STATE = '" & statevalue & _
"',COL_GRIDSQUARE= '" & gridvalue & "',COL_QSL_VIA = '" & qslmgrvalue & _
"' where COL_CALL = '" & Me.COL_CALL.text & "' AND COL_TIME_ON > #" & datefrom & "#"
COL_TIME_ON is a full timestamp held in a local database in a 3rd party tool which loads the data initially. I am expanding this entry with extra data extracted from an XML file sourced from a web based database.
Removing the date comparison at the end allowed the update to take place, but it also updated other records for the same callsign which may not be valid. (I am a radio ham by the way)
Thanks for your input.- As I suggested, and as I always go to as a first check, when a process has worked properly for an extended period of time, and then stops working, look FIRST at the data. The answer is almost always in the data.
Here it's the fact that one day's data reveals a problem with a basic assumption. Date formats differ, as we know, and it's a problematic thing that Office defaults to US format when one is not anticipating that.
One solution is to explicitly format your dates into an unambiguous structure, e.g. "YYYY/MM/DD". That's going to be interpreted correctly in all cases (assuming of course that it is actually the correct date passed into it).
It's one manifestation of what I've termed "The Iron Law of Ambiguity" (irony intended).
Ambiguity is required in poetry, humor and politics. Ambiguity is anathema is relational database applications.
Where a "date" can be interpreted in two different ways, it is ambiguous and using it is risky.
In the US format "7/3/2021" is July 3rd, 2021, but in other formats it is March 7th, 2021. Ambiguity not resolved leads to bad data and bad decisions based on that bad data. Resolve it in an unambiguous way before relying on it.
