MS Access VBA Code Audit
Dear All;
Happy new year 2020!
I just want to be corrected on the Ms Access/VBA code below if I have gotten it right or there is an error somewhere , the quality may not be as per your expectation , but due to the impending scheduled quality check by the taxman on their tax gadget (Rs 232 serial port) coming on Monday next week , I have decided to use this platform to audit the code below:
Happy new year 2020!
I just want to be corrected on the Ms Access/VBA code below if I have gotten it right or there is an error somewhere , the quality may not be as per your expectation , but due to the impending scheduled quality check by the taxman on their tax gadget (Rs 232 serial port) coming on Monday next week , I have decided to use this platform to audit the code below:
Dim json As String
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError As String
Dim strData As String
Dim lngSize As Long
intPortID = 2
' Initialize Communications
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
"baud=115200 parity=N data=8 stop=1")
If lngStatus <> 0 Then
' Handle error.
lngStatus = CommGetError(strError)
MsgBox "COM Error: " & strError
End If
' Set modem control lines.
lngStatus = CommSetLine(intPortID, LINE_RTS, True)
lngStatus = CommSetLine(intPortID, LINE_DTR, True)
' Write data to serial port.
strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3) [b]‘the intention here is to put ‘the Json data into the data string called strData[/b]
lngSize = Len(strData)
lngStatus = CommWrite(intPortID, strData)
If lngStatus <> lngSize Then
' Handle error.
End If
Exit_CmdConertJson_Click:
Exit Sub
Err_Handler:
Resume Exit_CmdConertJson_Click
' Read maximum of 14400 bytes from serial port.
Dim JSONS As Object
lngStatus = CommRead(intPortID, strData, 14400)
Set rs = db.OpenRecordset("tblEfdReceipts") [b]‘here I’m opening the table as a record source so that I ‘put the data received as per below deserialization[/b]
If lngStatus > 0 Then
ElseIf lngStatus < 0 Then
' Handle error.
On Error Resume Next
End If
' Process data.
Set JSONS = JsonConverter.ParseJson(strData)[b]’ Here I have assigned the Json to put data into object ‘called JSONS[/b]
Z = 2
For Each item In JSONS [b]‘Here Im now unpacking received json data into the table called ‘("tblEfdReceipts")[/b]
With rs
.AddNew
rs![TPIN] = item("TPIN")
rs![TaxpayerName] = item("TaxpayerName")
rs![Address] = item("Address")
rs![ESDTime] = item("ESDTime")
rs![TerminalID] = item("TerminalID")
rs![InvoiceCode] = item("InvoiceCode")
rs![InvoiceNumber] = item("InvoiceCode")
rs![FiscalCode] = item("FiscalCode")
rs![TalkTime] = item("TalkTime")
rs![Operator] = item("Operator")
rs![Taxlabel] = item("TaxItems")("TaxLabel")
rs![CategoryName] = item("TaxItems")("CategoryName")
rs![Rate] = item("TaxItems")("Rate")
rs![TaxAmount] = item("TaxItems")("TaxAmount")
rs![VerificationUrl] = item("TaxItems")("VerificationUrl")
rs![INVID] = Me.InvoiceID
rs.Update
End With
Z = Z + 1
Next
rs.Close
Set rs = Nothing
Set db = Nothing
Set JSONS = Nothing
' Reset modem control lines.
lngStatus = CommSetLine(intPortID, LINE_RTS, False)
lngStatus = CommSetLine(intPortID, LINE_DTR, False)
' Close communications.
Call CommClose(intPortID)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Very correct I'm using the commwrite
I was just concerned on the assignments of the following whether they are correctly done:
For Each item In JSONS ‘Here Im now unpacking received json data into the table called ‘("tblEfdReceipts")
I was just concerned on the assignments of the following whether they are correctly done:
strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3) [b]‘the intention here is to put ‘the Json data into the data string called strData[/b]Set rs = db.OpenRecordset("tblEfdReceipts") [b]‘here I’m opening the table as a record source so that I ‘put the data received as per below deserialization[/b]For Each item In JSONS ‘Here Im now unpacking received json data into the table called ‘("tblEfdReceipts")
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
- It uses hard coded values, which are clearly configuration.
The COM port configuration settings (parity, speed, stop bit) should be stored in configuration table. Any other setting requires a new audit, cause the code must be changed.
- No data validation.
The data read from the device must be validated before parsing and storing it.
- [..] Error handling and logging is not as granular as it could and (imho) should be.
There is no error handling and logging.