[AccessD] Dynamic SQL statement

[John Colby]

This sounds like a good application for a class.  Place all of the code 
for selecting when to do the specific SQL then code to select and 
execute the correct SQL statement.

Classes are about grouping code and data together.



On 12/7/2015 9:53 AM, FW Salato Center wrote:
> I ran into an interesting SQL problem a few months ago and I'm about to repeat the process with another set of data. Basically, I'm updating animals by group - herd, flock...
>
> Most of our animals are included as individuals, but forcing the vet and animal care guys to update all of them separately for the same thing was tedious and unnecessarily inefficient. Many procedures are "herd-applied" - very few records will apply to a single individual.
>
> I created a form that populates with all of the individuals of a specific species selected as a group and displayed in a list box. They can then unselect individuals if they want. The input values allow them to choose medical procedures, dates, and include a comment. When saved, the SQL behind the code creates a new medical record with the input values for each individual in the selected group.
>
> What complicated things was that not all of the fields in the input form are required so I had to come up with five different INSERT INTO statements and a list of conditions to meet to know which one to execute. Writing it all was easy enough - just basic stuff, but it occurs to me on the other side of all this that with the right dynamic statement, I could cover all possibilities with one statement. Maybe not.
>
> What I've got works fine so I'm not in any hurry to "fix" it - it aint broke. This week, I'll be putting in the same solution for updating vaccines for the herd/flock so I thought now might be a good time to consider something more... dynamic.
>
> The code for the first set - the medical procedures -- is below - as you can see, it's simple routine stuff. The first snippet checks for the required fields. The second creates the variables I drop into the SQL clauses because empty delimiters generate an error when executing a generic "all-inclusive" statement. The third sets up the SQL statement and executes it.
>
> I'm really interested in how you guys might omit that SELECT CASE and create a dynamic "string" that considers it all and just evaluates one SQL to execute - I think it's possible but I know that on my end, coming up with it isn't worth the time it would take me. Might not be for you guys, but some of you do this stuff in your sleep. :) I don't think you really need to closely examine the code, I just included in case.
>
> I'm interested in the discussion, even if I don't apply it - might be something to tuck into my toolkit for another project. Or, I might come back later next year and redo it. :) Nobody but me is ever going to see this code, so I don't need any "oooo... this is cool... she's so smart..." snippets. But I'm interested in learning something new just the same. - Thanks! Susan H.
>
>      'Check for required fields.
>      boo = lstIndividuals.ItemsSelected.Count = 0 Or IsNull(cboExam.Value) Or IsNull(txtObservation.Value)
>      If boo Then
>          MsgBox "Please choose an animal, exam type, and enter an explanation in the observation and procedure control.", _
>              vbOKOnly, "Required value is missing"
>          Exit Sub
>      End If
>
>
> '------------Nov 4
> 'Catch null values for non-required fields to eliminate delimiters that cause an error.
> '------------
>
>
>      'Accommodate null values for non-required fields.
>      'Above boo snipped above catches the three (table-level) required fields.
>      'The Ifs below catch the other two, otherwise, the empty delimiters make it to the
>      'SQL statement and cause an error.
>
>      strExamType = ", " & cboExam.Value
>
>      If IsNull(txtDate.Value) Then
>          strExamDate = ""
>      Else
>          strExamDate = ", #" & txtDate.Value & "#"
>      End If
>
>      strObservation = ", '" & txtObservation.Value & "'"
>
>      If IsNull(txtFollowup.Value) Then
>          strFollowupDate = ""
>      Else
>          strFollowupDate = ", #" & txtFollowup.Value & "#"
>      End If
>
> '--------------Nov 6
> 'This could be more efficient.
> 'Write new string that sets everything but the two possible missing values and run the if before the For.
> '--------------
>
>      'Appends each record to exam table.
>      For Each varItem In ctl.ItemsSelected
>      '        strIndividual = ctl.Column(2, varItem)
>       'Determine SQL string to run.
>      If IsNull(txtDate.Value) And IsNull(txtFollowup.Value) Then
>      'Basic SQL statement with only the three required fields.
>          strSQL = "INSERT INTO Exam " _
>              & "(IndividualIDFK, ExamDescriptionIDFK, Results) VALUES " _
>              & "(" _
>              & ctl.Column(2, varItem) & strExamType & strObservation _
>              & ")"
>      ElseIf IsNull(txtDate.Value) And Not (IsNull(txtFollowup.Value)) Then
>      'If only txtDate is null.
>          strSQL = "INSERT INTO Exam " _
>              & "(IndividualIDFK, ExamDescriptionIDFK, Results, NextExamDue) VALUES " _
>              & "(" _
>              & ctl.Column(2, varItem) & strExamType & strObservation & strFollowupDate _
>              & ")"
>      ElseIf Not (IsNull(txtDate.Value)) And IsNull(txtFollowup.Value) Then
>      'If only follow up date is null.
>          strSQL = "INSERT INTO Exam " _
>              & "(IndividualIDFK, ExamDescriptionIDFK, ExamDate, Results) VALUES " _
>              & "(" _
>              & ctl.Column(2, varItem) & strExamType & strExamDate & strObservation _
>              & ")"
>      ElseIf Not (IsNull(txtDate.Value)) And Not (IsNull(txtFollowup.Value)) Then
>      'If nothing's null.
>          strSQL = "INSERT INTO Exam " _
>              & "(IndividualIDFK, ExamDescriptionIDFK, ExamDate, Results, NextExamDue) VALUES " _
>              & "(" _
>              & ctl.Column(2, varItem) & strExamType & strExamDate & strObservation & strFollowupDate _
>              & ")"
>      Else
>          MsgBox "There is an error with a selected item. Please try again.", vbOKOnly, "Error"
>          Exit Sub
>      End If
>      Debug.Print strSQL
>      conn.Execute strSQL
>      strSQL = ""
>      Next varItem
>      conn.Close
>      Exit Sub
>

-- 
John W. Colby