How to write a very long SQL statement in VBA

[rocklee]

I'm having some great difficulty with an SQL statement which is very long, I'm not sure if I'm using the right characters to continue the SQL statement to the next line :

strsql = "INSERT INTO [Activity Details]" _
& "(Activity_ID, Activity_Name, Club_ID, term, [session teacher], [Number of support staff], [number of sessions], [running cost], [period/duration], [date of first session], [start time of session], [end time of session] , [jp contribution], [school contribution], [notes - preview], [notes - review], [notes - revision], [target gender], [traveller status], refugee, gifted, [ethnicity code], [sen status], [learning area]) VALUES " _
& " ('" & Activity_ID.Value & "','" & Activity_Name.Value & "','" & Club_ID.Value & "','" & Term & "','" & Session_Teacher & "','" & [Forms]![manage - club]![create_activity_subform]![Number_of_support_staff] & "','" & [Forms]![manage - club]![create_activity_subform]![Number_of_Sessions] & "','" & [Forms]![manage - club]![create_activity_subform]![Running_Cost] & "','" & [Forms]![manage - club]![create_activity_subform]![Duration] & "','" _
& first_session & " ','" & start_time & "','" & end_time & "','" & [Forms]![manage - club]![create_activity_subform].JP_Contribution.Value & "','" & [Forms]![manage - club]![create_activity_subform].School_Contribution.Value & "','" & [Forms]![manage - club]![create_activity_subform].Notes_Preview.Value & "','" & [Forms]![manage - club]![create_activity_subform].Notes_Review.Value & "','" & [Forms]![manage - club]![create_activity_subform].Notes_Revision.Value & "','" _
& [Forms]![manage - club]![create_activity_subform].Target_Gender.Value & " ','" & [Forms]![manage - club]![create_activity_subform].Traveller_Status.Value & "','" & [Forms]![manage - club]![create_activity_subform].Refugee.Value & "','" & [Forms]![manage - club]![create_activity_subform].Gifted.Value & "','" & [Forms]![manage - club]![create_activity_subform].Ethnicity_Code.Value & "','" & [Forms]![manage - club]![create_activity_subform].SEN_Status.Value & "','" & [Forms]![manage - club]![create_activity_subform].Learning_Area.Value & "')"

At the end of each line should be :

" _

And the beginning of the next line should be :

& "

is that correct? Did I miss something in my code? Sorry, its 3am in the morning and I can't seem to think straight.

 

[wiklendt]

what's not working...? be as specific as you can.

 

[shadow9449]

Try using:
...." & _

at the end of the line and then:
" at the beginning of the line.

Make sure there's a space between & and _

My personal preference in adding rows to a table is to use a DAO statement
such as

Dim db As DAO.Database
Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("XXX")
    With rs
      .Addnew
      .Fields("AAA") = Forms!myform!myfield
      .Fields("BBB") = Forms!myform!myfield1
      .Fields("CCC") = Forms!myform!myfield2
      .Update
      .Close
    End With

  set db = Nothing
  set rs = Nothing

Just for the ability to read it, this makes more sense. In addition, DAO code is able to handle things like Null values and quotation marks in strings whereas you can have a nightmare if you try doing that using the SQL statement you are using. There are work-arounds but I find that this solution makes life much easier.

SHADOW

 

[Atomic Shrimp]

I don't like using the line continuation thing, where I can avoid it. This is just a personal preference of course.

If I'm building SQL in code (actually, regardless of its length) I tend to do it like this:

strSQL =          "SELECT [MyTable1].*, MyTable2.[foo] "
strSQL = strSQL & "FROM MyTable2 "
strSQL = strSQL & "RIGHT JOIN [MyTable1] ON (MyTable2.bar = [MyTable1].bar) "
strSQL = strSQL & "AND (MyTable2.[foo] = [MyTable1].[foo]) "
strSQL = strSQL & "WHERE (((MyTable2.[foo]) Is Null));"

That way, if I have to inject some variables (as is nearly always the case, if I've chosen to put the query in the VBA), it's really easy to see where they need to go and if there's a problem with part of the query, it's still reasonably easy to parse the whole thing by eye.

It does make it a bit harder to just copy and paste the SQL back out into the query designer to test it, but I just put up with that.

 

[rocklee]

Thanks guys for showing me the alternatives.

Many thanks to shadow9449 for this easy code, so much easier than what I had!

 

[rocklee]

BTW, just thought I'd add 2 samples to demonstrate how easy the new method is for me :

To insert a new record :

    With rs
      .AddNew
      .Fields("Activity_ID") = Activity_ID.Value
      .Fields("Activity_Name") = Activity_Name.Value
      .Fields("Club_ID") = Club_ID.Value
      .Fields("term") = Term.Value
      .Fields("session teacher") = Session_Teacher.Value
      .Fields("Number of support staff") = [Forms]![manage - club]![create_activity_subform]![Number_of_support_staff]
      .Fields("number of sessions") = [Forms]![manage - club]![create_activity_subform]![Number_of_Sessions]
      .Fields("running cost") = [Forms]![manage - club]![create_activity_subform]![Running Cost]
      .Fields("duration") = [Forms]![manage - club]![create_activity_subform]![Duration]
      .Fields("date of first session") = first_session
      .Fields("start time of session") = start_time
      .Fields("end time of session") = end_time
      .Fields("jp contribution") = [Forms]![manage - club]![create_activity_subform]![JP Contribution]
      .Fields("school contribution") = [Forms]![manage - club]![create_activity_subform]![School Contribution]
      .Fields("notes - preview") = [Forms]![manage - club]![create_activity_subform]![Notes_Preview]
      .Fields("notes - review") = [Forms]![manage - club]![create_activity_subform]![Notes_Review]
      .Fields("notes - revision") = [Forms]![manage - club]![create_activity_subform]![Notes_Revision]
      .Fields("target gender") = [Forms]![manage - club]![create_activity_subform]![Target Gender]
      .Fields("traveller status") = [Forms]![manage - club]![create_activity_subform]![Traveller Status]
      .Fields("refugee") = [Forms]![manage - club]![create_activity_subform]![Refugee]
      .Fields("gifted") = [Forms]![manage - club]![create_activity_subform]![Gifted]
      .Fields("ethnicity code") = [Forms]![manage - club]![create_activity_subform]![Ethnicity Code]
      .Fields("sen status") = [Forms]![manage - club]![create_activity_subform]![SEN Status]
      .Fields("learning area") = [Forms]![manage - club]![create_activity_subform]![Learning Area]
      .Update
      .Close
    End With

To update the current record :

    With rs
      .Edit
      .Fields("Activity_Name") = Activity_Name.Value
      .Fields("term") = Term.Value
      .Fields("session teacher") = Session_Teacher.Value
      .Fields("Number of support staff") = [Forms]![manage - club]![create_activity_subform]![Number_of_support_staff]
      .Fields("number of sessions") = [Forms]![manage - club]![create_activity_subform]![Number_of_Sessions]
      .Fields("running cost") = [Forms]![manage - club]![create_activity_subform]![Running Cost]
      .Fields("duration") = [Forms]![manage - club]![create_activity_subform]![Duration]
      .Fields("date of first session") = first_session
      .Fields("start time of session") = start_time
      .Fields("end time of session") = end_time
      .Fields("jp contribution") = [Forms]![manage - club]![create_activity_subform]![JP Contribution]
      .Fields("school contribution") = [Forms]![manage - club]![create_activity_subform]![School Contribution]
      .Fields("notes - preview") = [Forms]![manage - club]![create_activity_subform]![Notes_Preview]
      .Fields("notes - review") = [Forms]![manage - club]![create_activity_subform]![Notes_Review]
      .Fields("notes - revision") = [Forms]![manage - club]![create_activity_subform]![Notes_Revision]
      .Fields("target gender") = [Forms]![manage - club]![create_activity_subform]![Target Gender]
      .Fields("traveller status") = [Forms]![manage - club]![create_activity_subform]![Traveller Status]
      .Fields("refugee") = [Forms]![manage - club]![create_activity_subform]![Refugee]
      .Fields("gifted") = [Forms]![manage - club]![create_activity_subform]![Gifted]
      .Fields("ethnicity code") = [Forms]![manage - club]![create_activity_subform]![Ethnicity Code]
      .Fields("sen status") = [Forms]![manage - club]![create_activity_subform]![SEN Status]
      .Fields("learning area") = [Forms]![manage - club]![create_activity_subform]![Learning Area]
      .Update
      .Close
    End With

 

[shadow9449]

...Many thanks to shadow9449 for this easy code, so much easier than what I had!

Most welcome. Good luck

SHADOW

 

[rocklee]

I just realised something during testing, update seems to update the first record in the table and not the record that needs to be updated!!

So the way for me to overcome this is to use :

.MoveLast

Since I'm only using this code to add new records and edit only these records when they are created.