What do you do when something just stops working for no apparent reason?
I’ve been developing an application for a client for the past few weeks, one that records answers to a set of questions. A couple of weeks ago I was asked to add a process to automatically set the answers to a default value if the question was not asked. It’s not standard practice, but sometimes the rule that the customer is always right has to be followed.
It was working fine. Tested okay. I moved on.
Today that process stopped working.
Just stopped. Dead. No error messages, it appeared to work okay as far as the code was concerned, but no answers were written to the database.
I should say I’m developing this using Microsoft Access and Excel. My favourite rapid application development suite.
Here’s an example of the original SQL code (dynamically generated when the process runs):
INSERT INTO tbl_Test_Answers ( FK_Test, FK_Answer, Test_Run, FK_EnteredBy, Date_Entered )
SELECT 4, tbl_Answers.PK_Answer, 1, 1, #25/05/2017 14:32:56#
FROM tbl_Answers INNER JOIN qry_Questions_Live ON tbl_Answers.FK_Question = qry_Questions_Live.PK_Questions
WHERE tbl_Answers.Answer='Test did not ask me this question' AND qry_Questions_Live.Question_Order>5
Now, I have a confession to make… The code didn’t just stop dead. No, as usually happens in situations like this, I’d made a change. Here’s the SQL code after I made those changes:
INSERT INTO tbl_Test_Answers ( FK_Test, FK_Answer, Test_Run, FK_EnteredBy, Date_Entered ) SELECT 10, tbl_Answers.PK_Answer, 1, 1, #29/05/2017 20:44:30# FROM tbl_Answers INNER JOIN qry_Questions_Live ON tbl_Answers.FK_Question = qry_Questions_Live.PK_Questions WHERE tbl_Answers.Answer Like '*Test did not ask me this question' AND qry_Questions_Live.Question_Order>5
Can you spot the difference?
Hopefully fairly obvious even if you’re not a SQL developer. Instead of =’Test did not ask me this question’ I’d changed the SQL to Like ‘*Test did not ask me this question’
This allows the code to look for variations like ‘1. Test did not ask me this question’ or ‘5. Test did not ask me this question’
Originally I’d not been told the answers could be numbered. Then I was, but it was inconsistent numbering so had to cope with variations. Fair enough. The Like command solves that and the * wildcard allows for variable length of text beforehand.
At least when running SQL directly on the database.
Not, apparently, when running via an ADODB connection from VBA…
Too many technical terms? Sorry… This isn’t supposed to be a technical blog.
Anyway. There are two wildcards you can use in SQL: * and %, but my understanding is that only * works in Access. I tried the % wildcard and it worked fine over VBA.
There’s a very useful explanation on Stackoverflow:
https://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign/720896#720896. Thanks to David-W-Fenton for pointing me at that.
Lessons learned?
When something was working and then breaks, it’s usually because something changed.
Just because something works in one environment doesn’t mean it will work in another. Always better to test.
