Parameterized Query in Access SQL

Question

I am a beginner in Access SQL. I am in need of writing a parameter query for Access SQL and found a few tutorials and links for that but that wouldn't satisfy my requirement.

DB: Access DB

This updates a 'PD' column based on the values of 'OG', 'CPTY-BA' in an INPUT table. I need to calculate PD values from other reference tables 'PD', 'PD_MASTER_CPTY' and 'PD_MASTER_BG'.

Below is the VBA code with Access SQL with provided parameters. Is there any other way to write it in a single SELECT statement?

ElseIf (outputColumn = "PD") Then

strSQL = "SELECT [OG], CIF,[CPTY-BA], CIF_2 FROM INPUT   WHERE RUN_NUMBER='" + runNumber + "' ORDER BY CIF"
Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF And rs.BOF) Then

     Do Until rs.EOF

        strOrigGroup = rs![Original Group]
        strCIF = rs![CIF]
        strCIF2 = rs![CIF_2]
        strBaselAsset = rs![CPTY-BA]


        If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then

                strSQL1 = " SELECT DISTINCT  [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
                          "ON [PD].CIF=[INPUT].CIF_2  WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL1
                Set rs1 = CurrentDb.OpenRecordset(strSQL1)

                If Not (rs1.EOF And rs1.BOF) Then
                    strPD = CDbl(rs1![MID_PD])
                Else
                    strPD = "0.0229"
                End If

                strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL
                CurrentDb.Execute (strSQL)                    


        ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then

                strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER,  [INPUT].CIF, [PD_MASTER_CPTY].[PD] FROM PD_MASTER_CPTY INNER JOIN INPUT ON [PD_MASTER_CPTY].COUNTERPARTY=INPUT.[CPTY-BA] WHERE [PD_MASTER_CPTY].[COUNTERPARTY]='" + strBaselAsset + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL1
                Set rs1 = CurrentDb.OpenRecordset(strSQL1)

                If Not (rs1.EOF And rs1.BOF) Then
                    strPD = CDbl(rs1![PD])
                Else
                    strPD = "0.0229"
                End If


                strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL
                CurrentDb.Execute (strSQL)

        Else
                strSQL1 = "SELECT [INPUT].RUN_NUMBER, [INPUT].CIF,[PD_MASTER_BG].[PD] FROM [PD_MASTER_BG] INNER JOIN INPUT ON [PD_MASTER_BG].[BUSINESS_GROUP]=INPUT.[ORIGINAL GROUP] WHERE [PD_MASTER_BG].[BUSINESS_GROUP]='" + strOrigGroup + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL1
                Set rs1 = CurrentDb.OpenRecordset(strSQL1)

                If Not (rs1.EOF And rs1.BOF) Then
                    strPD = CDbl(rs1![PD])
                Else
                    strPD = "0.0229"
                End If

                strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
                Debug.Print strSQL
                CurrentDb.Execute (strSQL)


        End If

        rs.MoveNext
        Loop

End If

Answer 1

Before we can do anything here really, we need to clean this up so we can understand exactly what's going on.

To start with, let's remove some duplication. This code is copy/pasted all over the place, but no matter what happens, it executes.

If Not (rs1.EOF And rs1.BOF) Then
    strPD = CDbl(rs1![PD])
Else
    strPD = "0.0229"
End If

strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

---

    If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then

            strSQL1 = " SELECT DISTINCT  [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
                      "ON [PD].CIF=[INPUT].CIF_2  WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
            Debug.Print strSQL1
            Set rs1 = CurrentDb.OpenRecordset(strSQL1)

    ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then

         strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER,  [INPUT].CIF, [PD_MASTER_CPTY].[PD] FROM PD_MASTER_CPTY INNER JOIN INPUT ON [PD_MASTER_CPTY].COUNTERPARTY=INPUT.[CPTY-BA] WHERE [PD_MASTER_CPTY].[COUNTERPARTY]='" + strBaselAsset + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
         Debug.Print strSQL1
         Set rs1 = CurrentDb.OpenRecordset(strSQL1)
    Else
        strSQL1 = "SELECT [INPUT].RUN_NUMBER, [INPUT].CIF,[PD_MASTER_BG].[PD] FROM [PD_MASTER_BG] INNER JOIN INPUT ON [PD_MASTER_BG].[BUSINESS_GROUP]=INPUT.[ORIGINAL GROUP] WHERE [PD_MASTER_BG].[BUSINESS_GROUP]='" + strOrigGroup + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
        Debug.Print strSQL1
        Set rs1 = CurrentDb.OpenRecordset(strSQL1)
    End If

    If Not (rs1.EOF And rs1.BOF) Then
        strPD = CDbl(rs1![PD])
    Else
        strPD = "0.0229"
    End If

    strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
    Debug.Print strSQL
    CurrentDb.Execute (strSQL)

    rs.MoveNext
Loop

Next, give your SQL statement variables some meaningful names. I shouldn't have to keep reminding myself that strSQL1 is the select statement and strSQL is the update. Try selectSQL and updateSQL respectively.

Next, I would separate the messy business of creating the string to be executed from the business of actually executing.

Private Function BuildSelectStatement(byval cif as string, byval runNumber as integer, Optional byval baselAsset as String) As String
    ...
End Function

....

selectSQL = BuildSelectStatement(strCIF2, runNumber)

Which brings me to recommending that you use ADODB for this. The only time it's really important to stick with DAO is when you intend on binding a form to the resulting Recordset. Instead of concatenating all of these parameters into strings, you could instead be passing parameter into parameterized queries. It looks and reads much better than this.