SQL query in code over 100 lines long

[qwertyjjj]

Hi
I have a complex SQL query, which I need to put into my DB in some VBA code so that it can be dynamically changed.
As far as I know, Access does not have anything like stored procedures, which would have made this easier.

I'm planning to use this SQL query as the record source of a form but to get it neatly into the code I want to leave it in it's exisiting structure with line breaks, etc. for easy reading.

The only way I can think of is to do something like

strSQL = "SELECT Field1, " & _
" Field2, " & _
etc. but this is going to become very tiresome adding the & _ at the end of each line.

Isn't there an easier way ?

 

[Pat Hartman]

If all you want to do is provide a parameter at runtime, put the SQL in a querydef. Access will take away your nice formatting (we've asked but nothing is happening with the next release) but in exchange, you can work with the QBE and not have to mess with SQL in code.

If you want to make dynamic changes to the select clause or where clause, you will need to work with the string in VBA.

 

[qwertyjjj]

By querydef do you mean a query that I can add a parameter value to and then through VBA code?

 

[Moonshine]

By querydef do you mean a query that I can add a parameter value to and then through VBA code?

Yeah, thats exactly what he means. It will have to be a SQL Pass-Thru query i think, you then just create a Query Def in the Code and then you can amend it via the code.

    Dim qdfCarer As QueryDef
    'Assign the value of the form's PARIS ID to a string variable

    'Assign the query definition variable
    Set qdfCarer = CurrentDb.QueryDefs![qry_Carer]
   
    'Redefine the query's SQL to incorporate the selected PARIS ID
    qdfCarer.SQL = "select REL_TARGET_PER_ID " & _
    " from PARIS.MPI_RELATIONSHIP" & _
    " where REL_ASSOCIATION = 'CA'" & _
    " and (REL_FROM <SYSDATE or REL_FROM Is Null)" & _
    " and (REL_TO >SYSDATE or REL_TO Is Null)" & _
    " and REL_ORIGIN_PER_ID = " & Me.Client & ""

Sample bit of code of many we use, simply has the same SQL as the query, and it adds the Me.Client into it at the end for whicever client is choosen from the form.

 

[qwertyjjj]

Bit stuck here as to how to pass a parameter to the query:

So far, I've got:
Private Sub combo_DivLevel_Change()

If combo_DivLevel.SelText = "All Divisional Levels" Then
Call Populate_DropDowns("", "")
ElseIf Len(combo_DivLevel.Text) = 5 Then 'might need a 9 character one as well
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("DivisionalLevelSpecific")
qdfParmQry("DivisionalLevel?") = combo_DivLevel.SelText
Set rs = qdfParmQry.OpenRecordset()
Form.RecordSource = rs
End If

End Sub

I need to change the form's recordsource to the query with a passed parameter and reload the data anytime the combo box is changed...