• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Dragon Lake Books

The home of great stories

  • About
    • Privacy policy
  • Where To Buy
  • Sample Chapters
  • Events
  • My 100 Goals Blog
  • Contact Form
  • Mailing List Sign-up

ADODB

When the SQL twists and squirms

June 1, 2017 by frozbie Leave a Comment

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.

Filed Under: 100 Words 100 Days, SQL, VBA Tagged With: ADODB, Microsoft, MS Access, MS Excel, SQL, VBA, wildcards

Primary Sidebar

Follow

Follow me on:

Recent Posts

  • Q2 Halfway point
  • The 12 Week Year – a first quarter review
  • Double Your Salary …Without losing your soul!
  • 40K
  • Progress

Recent Blog Posts

  • Q2 Halfway point

    Q2 Halfway point

    May 12, 2021
    Back in December 2020/January 2021 I wondered if the contracting and job markets in the UK would continue to be dead through till the summer. It seemed possible that I might have to go six months without working and so to put a plan in place to make constructive use of the time seemed sensible. […]Read More »
  • The 12 Week Year – a first quarter review

    The 12 Week Year – a first quarter review

    April 21, 2021
    I recently finished my first 12 Week Year and wanted to review how it went to see if I could learn some lessons from the experience. If you’ve not read it, The 12 Week Year by Brian P. Moran and Michael Lennington proposes by working to a 12 week plan, you can get more done […]Read More »
  • Double Your Salary …Without losing your soul!

    Double Your Salary …Without losing your soul!

    June 25, 2019
    I’m excited to share that the hardback of my next book: Double Your Salary …Without losing your soul! will be released for sale on 1st August 2019. I believe that doubling your salary is possible when you understand who you are and what you are capable of. Sharing my story of how I doubled my […]Read More »
  • 40K

    40K

    June 15, 2019
    It might be that weekly posts are all I can manage until the first draft of Fallen Warriors Season Two is complete. I met my target of 10,000 words today and did manage to hit 2,000 words each day this week. That feels good. An accomplishment. I haven’t managed much else, publishing wise. I kept […]Read More »
  • Progress

    Progress

    June 8, 2019
    I wrote last week that I was setting a new target to write 5,000 words a day. Why do I do this to myself? The results are in. I think I managed a thousand words one day that first half week, then I’ve managed 4,790 this week. That’s not even a fifth of what I’d […]Read More »

Footer

My 100 Goals Blog

  • Q2 Halfway point

    Q2 Halfway point

    May 12, 2021
  • The 12 Week Year – a first quarter review

    The 12 Week Year – a first quarter review

    April 21, 2021
  • Double Your Salary …Without losing your soul!

    Double Your Salary …Without losing your soul!

    June 25, 2019
  • 40K

    40K

    June 15, 2019
  • Progress

    Progress

    June 8, 2019

Copyright © 2022 · Author Pro on Genesis Framework · WordPress · Log in