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
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 with
End With, so Terminate is called and the Close process is also executed. If
On 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 with
Err.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.
- excelvba hyperlinkfollow error "438" i want to resolve
- i want to replace all the fill colors after the word vba cursor position
- in excelvba, i want to disable the checkbox
- vba - how to receive return value after executing blueprism excel macro
- how to find the last column in that row after using find in vba
- excelvba i want to solve macro execution error of inactive book
- vba error occurs after the second time
- i want to save a csv file without opening it after saving excel vba
- i want to decompose the values with vba and combine them after executing the formula
- how to overwrite a specific cell after processing a vba macro
- i want to put a space after the variable name when creating a vba program
- delete after vba specific characters
- how to select a folder and get its path after pressing a button with a vba macro
- after reading a vba file, i want to read data by condition judgment
- cannot insert cells after inheriting vba
- vba - after importing csv into a table, i want to make the data with some items deleted as a new table
- i want to create an excel-vba macro that acquires the ip address of a pc on a remote desktop
- i want to use excelvba to send emails to multiple contacts at once while changing the text content and destination
- want to import excelvba sheet into list box
- access&excelvba how to handle blank data
- vba - no error occurs in docmdtransferspreadsheet in the form of operating access from excel
- excel vba end property cannot be used
- vba - how to search in an access form like an excel autofilter
- vba - i want to import the xlsx file in which the positional relationship between the item name and the contents of the data she
- i want to use the export function (docmdtransferspreadsheet) of access from excel vba
- vba - how to launch an access file with a password from task scheduler
- vba - [database design] about table design when referencing/updating the access database using excel as an input screen
- vba - please teach me how to centrally manage customer lists
- excel vba displays "&h8000ffff (-2147418113) this is a fatal error"