Implementing Excel's "fill-down" on MS Access table

Yesterday I came across a posting in MS Access VBA Developers groups and the top post caught my attention, as it is something that I may have done in the past. I responded that I would use a self-join UPDATE query to complete this task.

Since LinkedIn has limitations on message length and since I wrote some sample code to illustrate the solution I decided to share it.

One can view the original problem in the above hyperlink. I created a table T in MS Access with the exact structure and values. The code I wrote creates a table called TEMP for the results while the original table T stays in tact so one can see the before and after. The code can then be further enhanced and extended to handle as many fields as necessary and the assumption is knowledge of looping through fields in a recordset.

Here is the code:

Option Compare Database

Public Sub ExecuteProcess()

    On Error GoTo ERROR_PROC
    
    Dim oDB As DAO.Database, sSQL As String
    
    'Name the sample table name and sample field name to populate in "fill-down" Excel-like manner
    'Use a recordset to loop through all fields of interest and use logic below accordingly
    'This just illustrates the concept using a single field
       
    Const cTableName As String = "T": Const cFieldName As String = "Process"
    Set oDB = Application.CurrentDb
    'BEGIN quick way to create new table so original states in tact for debugging/illustration purpses
    On Error Resume Next
        sSQL = "DROP TABLE [TEMP];"
        Call oDB.Execute(sSQL, dbFailOnError)
        sSQL = "SELECT * INTO [TEMP] FROM [" & cTableName & "];"
        Call oDB.Execute(sSQL, dbFailOnError)
        sSQL = "DROP TABLE [REF];"
        Call oDB.Execute(sSQL, dbFailOnError)
        'Set up IDs for joins and store in table [REF]
        sSQL = _
            "SELECT" & VBA.vbNewLine & _
            "[A].[ID], CLNG([A].[ID]) AS C_ID, MAX([B].[ID]) AS E_ID" & VBA.vbNewLine & _
            "INTO [REF]" & VBA.vbNewLine & _
            "FROM" & VBA.vbNewLine & _
            "[TEMP] AS A" & VBA.vbNewLine & _
            "INNER JOIN [TEMP] AS B" & VBA.vbNewLine & _
            "ON [A].[ID] > [B].[ID]" & VBA.vbNewLine & _
            "WHERE [A].[" & cFieldName & "] IS NULL" & VBA.vbNewLine & _
            "AND [B].[" & cFieldName & "] IS NOT NULL" & VBA.vbNewLine & _
            "GROUP BY" & VBA.vbNewLine & _
            "[A].[ID];"
            Debug.Print sSQL
        Call oDB.Execute(sSQL, dbFailOnError)
    On Error GoTo 0
    'END quick way to create new table so original states in tact for debugging/illustration purposes
    
    'Essence of "fill-down" table update
    sSQL = _
        "UPDATE" & VBA.vbNewLine & _
        "[TEMP] INNER JOIN" & VBA.vbNewLine & _
        "(" & VBA.vbNewLine & _
        "[TEMP] AS [E] INNER JOIN (" & VBA.vbNewLine & _
        "[TEMP] AS D INNER JOIN" & VBA.vbNewLine & _
        "[REF] AS C" & VBA.vbNewLine & _
        "ON [D].[ID] = [C].[C_ID]" & VBA.vbNewLine & _
        ")" & VBA.vbNewLine & _
        "ON [E].ID = [C].[E_ID])" & VBA.vbNewLine & _
        "ON [TEMP].[ID] = [C].[ID]" & VBA.vbNewLine & _
        "SET" & VBA.vbNewLine & _
        "[TEMP].[" & cFieldName & "] = [E].[" & cFieldName & "];"
        Debug.Print sSQL
    Call oDB.Execute(sSQL, dbFailOnError)

EXIT_SUB:

    'Cleanup
    'Type SQL statements here to DROP TABLE {REF] and [TEMP] here
    If Not oDB Is Nothing Then Call oDB.Close: Set oDB = Nothing
    Exit Sub
    
    'See original table T and new table TEMP
    
ERROR_PROC:
    Call VBA.MsgBox(VBA.Err.Description & " [" & VBA.Err.Number, VBA.vbCritical, "Error_ExecuteProcess")
    Resume EXIT_SUB:
    Resume
    
End Sub

I welcome any comments. Thank you for reading.

P.S. Adding the code is a bit tricky so I used StackOverflow's functionality to post code.