[AccessD] Dynamic SQL statement

[Rocky Smolin]

How about two separate strings - one for the INSERT clause and one for the
SELECT clause.  Assemble them in parallel adding to both the fields you want
and then append the SELECT to the INSERT at the end?

R
  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of FW
Salato Center
Sent: Monday, December 07, 2015 6:54 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Dynamic SQL statement

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus