Dragon Lake Books

The home of great stories

SQL

Avoiding Transaction

by 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.

When the SQL twists and squirms

by 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.