Nov 12 2021 05:20 AM - edited Nov 12 2021 07:18 AM
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.
Nov 12 2021 07:27 AM - edited Nov 12 2021 07:31 AM
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.
Nov 13 2021 04:27 AM - edited Nov 13 2021 04:50 AM
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.SetFocus
addr2value = 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.
Nov 13 2021 06:15 AM
Nov 13 2021 06:37 AM
Nov 13 2021 07:13 AM
"I checked the data previously when it was working and the mm/dd format was still valid in the comparison although the date 'created' was earlier than now thus allowing later rows to update."
This is the problem I was anticipating. You wouldn't necessarily even know that something had gone wrong because there are no error messages and you might not (probably wouldn't) be looking at those other rows and therefore not notice the update.
After 25+ years of working with Access and participating in forums like this, I've come to the conclusion that ambiguity is the biggest single factor in "anomalous" results.
The good news is two-fold. Problem solved. Deeper insight into the inner workings of Access.