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

Dragon Lake Books

The home of great stories

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

SQL

Avoiding Transaction

June 12, 2017 by frozbie Leave a Comment

There are times in my job that I wished I had photographic memory. I primarily code in Microsoft Visual Basic for Applications (VBA) and in MS Access SQL (or other flavours depending on the client.)

As with all programming langauges, there are a lot of words to memorize. I suspect that I only use a small fraction on a daily basis.

For comparison, did you know that according to Oxford Dictionary there are 171,476 English words in current use.

Robert Charles Lee writes on Quora that the average adult native speaker of English knows 20,000 to 35,000 words. Also, that “3,000 words will cover 95% of everyday writing.”

Of course, some of those words we use are ones that are not always appropriate in certain situations…

There I was, trying to run a Stored Procedure (a SQL command I’d saved in MS Access) from VBA and all I got was this error:

“The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.”

Okay, what does that mean, apart from the obvious? The SQL was working fine when I ran it directly within the Access database. I only received the error when running through VBA.

I went and poured myself a coffee. Decaf…

Reserved word, misspelt or missing argument name, incorrect punctuation… One of those must be true, but why was the SQL query running on the database?

I took a closer look and finally realised what I’d done wrong. The query was returning a list of Transactions and so I’d called the name of the Transactions: Transaction. I searched online and found this handy list of Reserved Words.

Transaction, of course, is reserved. If you know more than basic SQL that is probably pretty obvious. A feature of SQL is that it allows you to combine commands within a “Transaction” which can then be rolled back if something goes wrong.

So, using Transaction as a column name is not allowed. What I’m left wondering is why MS Access allowed it… Maybe one for another day.

Filed Under: 100 Words 100 Days, SQL, VBA Tagged With: language, programming, Reserved Words, SQL, Transaction, VBA, vocabulary

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

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

Recent Blog Posts

  • Goal 31

    Goal 31

    February 22, 2025
    I keep returning in my thoughts to Goal 31: To be and do all that God wants of me. Not every minute or every day, yet this phrase follows me around. I used to have a website: www.goal31.co.uk which I used for my contracting/consulting business. I made a poor decision to take it down and […]Read More »
  • 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 »

Footer

My 100 Goals Blog

  • Goal 31

    Goal 31

    February 22, 2025
  • 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

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