Home>

Create the following class to connect to Access with ExcelVBA.

Const strFilePath = "C: hoge.accdb"
Dim adoCn As Object
Dim adoRs As Object
Private Sub Class_Initialize ()
    Set adoCn = CreateObject ("ADODB.Connection"): adoCn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source ="&strFilePath&";"
    Set adoRs = CreateObject ("ADODB.Recordset")
End Sub
Public Function strTarget (Syain_ID As String, System_Name As String)
    Dim strSQL As String
    strSQL = "SELECT Target FROM table 1 WHERE Syain_ID = '"&Syain_ID&"' AND System_Name = '"&System_Name&"'"
    adoRs.Open strSQL, adoCn
    If Not adoRs.EOF Then
        strTarget = adoRs! Target
    Else
        Err.Raise 1234,, "employee number:"&Syain_ID&vbCrLf&"system name:"&System_Name&vbCrLf&"No records satisfying above" "← If this happens
    End If
End Function
Private Sub Class_Terminate ()
    adoRs.Close: Set adoRs = Nothing
    adoCn.Close: Set adoCn = Nothing
End Sub

After that, write the following code in the standard module.

Sub main ()
    With New Class1
        MsgBox .strTarget ("1234567", "System")
    End With
End Sub

Throw SQL to Access and if it is 0, I want to Err.Raise at that point. In this case, will the Close process written in Class_Terminate be performed?
Also, please tell us about the adverse effects if the macro ends before the Close process is performed.

  • Answer # 1

    Check before answering
    strPassword = adoRs! Target
    Is
    strTarget = adoRs! Target
    Isn't that wrong?


    In the case of

    Class, it is automatically released when the reference is finished. At that time the Terminate event is called. It is the same if an error is generated with Err.Raise on the way. (It shouldn't be terminated or disciplined on the error screen)

    In the question code, the class is generated with 'With New Class1', so the reference ends withEnd With, so Terminate is called and the Close process is also executed. IfOn Error GoTo ...jumps to the error handler and exits the procedure, Class is released at that point and Terminate is executed.
    How about embedding debug code as shown below to check the operation?

    Private Sub Class_Terminate ()
        adoRs.Close: Set adoRs = Nothing
        adoCn.Close: Set adoCn = Nothing
        Debug.Print "Close processing complete"
    End Sub

    Below, it is not an answer to the question, but it is not an error to process withErr.Raisebecause 0 results are not errors.
    I think the return value of strTarget should be Null or "".
    For example, the standard DLookup function of Access returns Null when there is no corresponding data.